Re: Creating and updating table using function parameter reference

A Dimecres 13 Febrer 2008 15:25, Linux Guru va escriure:
> I want to create and update two tables in a function such as below, but
> using parameters as tablename is not allowed and gives an error. Is there
> any way I could achieve this?

You're looking for EXECUTE:
http://www.postgresql.org/docs/8.3/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

>
> CREATE OR REPLACE FUNCTION test ( t1 text,t2 text ) RETURNS numeric AS $$
> declare temp1 text;
> declare temp2 text;
> begin
> temp1=t1;
> temp2=t2;
> select
> product,
> (case when sum(pd) <> 0 then sum(gd)/sum(pd)*100 else 0 end ) as gppp
> into temp2 from temp1 as dummy
> group by dummy.product,dummy.totalclaimsgroup,dummy.avgmems,dummy.months;
>
> update temp1 as t set
> GPPP=(select gppp from temp2 as dummy where dummy.product=t.product),
>
> end
> $$ LANGUAGE plpgsql
>
>
> ----------------------
> ERROR: syntax error at or near "$1"
> LINE 1: ...en sum(gd)/sum(pd)*100 else 0 end ) as gppp from $1 as dum...
> ^
> QUERY: select product, (case when sum(pd) <> 0 then sum(gd)/sum(pd)*100
> else 0 end ) as gppp from $1 as dummy group by dummy.product,
> dummy.totalclaimsgroup,dummy.avgmems,dummy.months
> CONTEXT: SQL statement in PL/PgSQL function "test" near line 10
>
> ********** Error **********
>
> ERROR: syntax error at or near "$1"
> SQL state: 42601
> Context: SQL statement in PL/PgSQL function "test" near line 10