Thread: About SP's and parameters

Hi all:

    Is there a way to pass a tablename as a parameter to a SP?

    I'll try to explain myself. I want to create a SP that can, for
instance, do a delete from ...... so that any particular application all

it has to do is call the SP sending the table to be deleted.

    Right now I have:

CREATE FUNCTION deleteanytable (bpchar)
returns integer
as '
    begin
        delete from $1;

        return 1;
    end;'
language 'plpgsql';

If I call it:
    select deleteanytable ( mytable );

I get a message saying

ERROR:  Attribute 'mytable' not found

Thanks for your help.

P.S. It would be very interesting if a columnname could also be sent as
a parameter to the Stored Procedure.

in 7.1, there's "EXECUTE" command in plsql, so you can do:

execute 'delete from ' || $1;

-alex

On Thu, 8 Feb 2001, Alfonso Peniche wrote:

> Hi all:
>
>     Is there a way to pass a tablename as a parameter to a SP?
>
>     I'll try to explain myself. I want to create a SP that can, for
> instance, do a delete from ...... so that any particular application all
>
> it has to do is call the SP sending the table to be deleted.
>
>     Right now I have:
>
> CREATE FUNCTION deleteanytable (bpchar)
> returns integer
> as '
>     begin
>         delete from $1;
>
>         return 1;
>     end;'
> language 'plpgsql';
>
> If I call it:
>     select deleteanytable ( mytable );
>
> I get a message saying
>
> ERROR:  Attribute 'mytable' not found
>
> Thanks for your help.
>
> P.S. It would be very interesting if a columnname could also be sent as
> a parameter to the Stored Procedure.
>
>
>
>

And where can I find information about EXECUTE and other new
features/instructions (I haven't found anything in the online manual)?

Alex Pilosov wrote:

> in 7.1, there's "EXECUTE" command in plsql, so you can do:
>
> execute 'delete from ' || $1;
>
> -alex
>
> On Thu, 8 Feb 2001, Alfonso Peniche wrote:
>
> > Hi all:
> >
> >     Is there a way to pass a tablename as a parameter to a SP?
> >
> >     I'll try to explain myself. I want to create a SP that can, for
> > instance, do a delete from ...... so that any particular application all
> >
> > it has to do is call the SP sending the table to be deleted.
> >
> >     Right now I have:
> >
> > CREATE FUNCTION deleteanytable (bpchar)
> > returns integer
> > as '
> >     begin
> >         delete from $1;
> >
> >         return 1;
> >     end;'
> > language 'plpgsql';
> >
> > If I call it:
> >     select deleteanytable ( mytable );
> >
> > I get a message saying
> >
> > ERROR:  Attribute 'mytable' not found
> >
> > Thanks for your help.
> >
> > P.S. It would be very interesting if a columnname could also be sent as
> > a parameter to the Stored Procedure.
> >
> >
> >
> >