I still cannot pass tablename, what is wrong? Is this the right way?
CREATE OR REPLACE FUNCTION test ( t1 text,t2 text ) RETURNS numeric AS $$ declare temp1 text; declare temp2 text; declare cmd text; declare t2row RECORD; begin temp1=t1; temp2=t2; cmd='select product, (case when sum(pd) <> 0 then sum(gd)/sum(pd)*100 else 0 end ) as gppp from ' temp1 ' as dummy group by dummy.product,dummy.totalclaimsgroup, dummy.avgmems,dummy.months'; execute cmd into t2row --After executing above, I need here to update table t1 end; $$ LANGUAGE plpgsql ---------------- ERROR: syntax error at or near "$1" LINE 2: from ' $1 ' as dummy group by dummy.product,dummy.totalcla... ^ QUERY: SELECT '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 9 ********** Error ********** ERROR: syntax error at or near "$1" SQL state: 42601 Context: SQL statement in PL/PgSQL function "test" near line 9 On Wed, Feb 13, 2008 at 8:23 PM, Albert Cervera Areny <[EMAIL PROTECTED]> wrote: > 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 > > >