[PERFORM] Creating and updating table using function parameter reference
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? 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
Re: [PERFORM] 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Creating and updating table using function parameter reference
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 > > >
Re: [PERFORM] Creating and updating table using function parameter reference
You need the string concatenation operator ||. Take a look at http://www.postgresql.org/docs/8.3/static/functions-string.html By the way, this is off-topic in this list please, post general non-performance questions to pgsql-general. A Dijous 14 Febrer 2008 13:35, Linux Guru va escriure: > 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 -- Albert Cervera Areny Dept. Informàtica Sedifa, S.L. Av. Can Bordoll, 149 08202 - Sabadell (Barcelona) Tel. 93 715 51 11 Fax. 93 715 51 12 AVISO LEGAL La presente comunicación y sus anexos tiene como destinatario la persona a la que va dirigida, por lo que si usted lo recibe por error debe notificarlo al remitente y eliminarlo de su sistema, no pudiendo utilizarlo, total o parcialmente, para ningún fin. Su contenido puede tener información confidencial o protegida legalmente y únicamente expresa la opinión del remitente. El uso del correo electrónico vía Internet no permite asegurarni la confidencialidad de los mensajes nisucorrecta recepción. Enel caso de que el destinatario no consintiera la utilización del correo electrónico, deberá ponerlo en nuestro conocimiento inmediatamente. ... DISCLAIMER . This message and its attachments are intended exclusively for the named addressee. If you receive this message in error, please immediately delete it from your system and notify the sender. You may not use this message or any part of it for any purpose. The message may contain information that is confidential or protected by law, and any opinions expressed are those of the individualsender. Internet e-mail guarantees neither the confidentiality nor the proper receipt of the
Re: [PERFORM] Creating and updating table using function parameter reference
thanks, i posted in this listed because it was related to my previous query. Anyway, I am able to achieve, with the help in this mailing list, what I wanted but is there any way to further optimize this. Thanks CREATE OR REPLACE FUNCTION test ( t1 text ) RETURNS numeric AS $$ declare cmd1 text; declare cmd2 text; declare t2row RECORD; begin cmd1=' select product, (case when sum(pd) <> 0 then sum(gd)/sum(pd)*100 else 0 end ) as gppp, (case when sum(tld) <> 0 then sum(pd)/sum(tld) else 0 end ) as ppd from '|| t1 || ' as dummy group by dummy.product' ; for t2row in execute cmd1 loop cmd2 = 'update ' || t1 || ' as t set GPPP=' ||t2row.gppp||' where product='||quote_literal(t2row.product); execute cmd2; cmd2 = 'update ' || t1 || ' as t set PPD=' ||t2row.ppd||' where product='||quote_literal(t2row.product); execute cmd2; END LOOP; RETURN NULL; end; $$ LANGUAGE plpgsql VOLATILE On Thu, Feb 14, 2008 at 5:54 PM, Albert Cervera Areny <[EMAIL PROTECTED]> wrote: > You need the string concatenation operator ||. Take a look at > http://www.postgresql.org/docs/8.3/static/functions-string.html > > By the way, this is off-topic in this list please, post general > non-performance questions to pgsql-general. > > A Dijous 14 Febrer 2008 13:35, Linux Guru va escriure: > > 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 > > -- > Albert Cervera Areny > Dept. Informàtica Sedifa, S.L. > > Av. Can Bordoll, 149 > 08202 - Sabadell (Barcelona) > Tel. 93 715 51 11 > Fax. 93 715 51 12 > > > AVISO LEGAL > La presente comunicación y sus anexos tiene como destinatario la > persona a la que va dirigida, por lo que si usted lo recibe > por error debe notificarlo
