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 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
