Thread: pl/pgsql Function Problem

I have written a pl/pgsql function as follows:

create function aufloesung(varchar,varchar) returns integer as '
declare
   tabnam alias for $1;
   proj alias for $2;
begin
drop table temptab;
create table $1 as select sum(menge) as menge, bauteilcode, version  from
fertigungsprojektliste where fertigungsprojekt=''proj'' group by
bauteilcode,version;
return 1;
end;'
language 'plpgsql';

When I call this function with:
select aufloesung('xyz','0307W2901');
an Error Message is issued: ERROR: 'parser:parse error at or near "$1" at
character 15'

When I change "create table $1" to "create table xyz" the function works
fine. What did I do wrong?

Thanks in advance for your hints.
R.Hnat

On Wed, 28 Jan 2004, Reinhard Hnat wrote:

> I have written a pl/pgsql function as follows:
>
> create function aufloesung(varchar,varchar) returns integer as '
> declare
>    tabnam alias for $1;
>    proj alias for $2;
> begin
> drop table temptab;
> create table $1 as select sum(menge) as menge, bauteilcode, version  from
> fertigungsprojektliste where fertigungsprojekt=''proj'' group by
> bauteilcode,version;
> return 1;
> end;'
> language 'plpgsql';
>
> When I call this function with:
> select aufloesung('xyz','0307W2901');
> an Error Message is issued: ERROR: 'parser:parse error at or near "$1" at
> character 15'
>
> When I change "create table $1" to "create table xyz" the function works
> fine. What did I do wrong?

Commands like create table do not currently accept variables as arguments
in plpgsql.  You'll need to use EXECUTE to do them, like:
EXECUTE ''create table '' || $1 || '' as ...