Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 7E5512E00F3 for ; Thu, 14 Feb 2008 08:54:39 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 02708-03 for ; Thu, 14 Feb 2008 08:54:28 -0400 (AST) Received: from correo1.acens.net (correo1.acens.net [217.116.0.33]) by postgresql.org (Postfix) with ESMTP id 00A8C2E00E0 for ; Thu, 14 Feb 2008 08:54:34 -0400 (AST) Received: (qmail 454 invoked from network); 14 Feb 2008 12:54:32 -0000 Received: from unknown (HELO sardina) ([83.175.220.10]) (envelope-sender ) by correo1.acens.net (qmail-ldap-1.03) with SMTP for ; 14 Feb 2008 12:54:31 -0000 From: Albert Cervera Areny Organization: Sedifa, S.L. To: "Linux Guru" Subject: Re: Creating and updating table using function parameter reference Date: Thu, 14 Feb 2008 13:54:28 +0100 User-Agent: KMail/1.9.5 Cc: pgsql-performance@postgresql.org References: <3caa866c0802130625k728a4a5dl23bffb56c1ded6e9@mail.gmail.com> <200802131623.57343.albert@sedifa.com> <3caa866c0802140435v4af4f2a1y183883db977eb151@mail.gmail.com> In-Reply-To: <3caa866c0802140435v4af4f2a1y183883db977eb151@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-15" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Message-Id: <200802141354.28669.albert@sedifa.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/163 X-Sequence-Number: 28705 You need the string concatenation operator ||. Take a look at=20 http://www.postgresql.org/docs/8.3/static/functions-string.html By the way, this is off-topic in this list please, post general=20 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=3Dt1; > temp2=3Dt2; > cmd=3D'select product, (case when sum(pd) <> 0 then sum(gd)/sum(pd)*1= 00 > 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 > > 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, b= ut > > > 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#PLPGS= QL > >-STATEMENTS-EXECUTING-DYN > > > > > CREATE OR REPLACE FUNCTION test ( t1 text,t2 text ) RETURNS numeric > > > AS > > > > $$ > > > > > declare temp1 text; > > > declare temp2 text; > > > begin > > > temp1=3Dt1; > > > temp2=3Dt2; > > > 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=3D(select gppp from temp2 as dummy where > > > dummy.product=3Dt.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 =2D-=20 Albert Cervera Areny Dept. Inform=E0tica Sedifa, S.L. Av. Can Bordoll, 149 08202 - Sabadell (Barcelona) Tel. 93 715 51 11 =46ax. 93 715 51 12 =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =2E....................... AVISO LEGAL ............................ La presente comunicaci=F3n 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=FAn fin. Su contenido puede tener informaci=F3n confidencial o protegida legalmente y =FAnicamente expresa la opini=F3n del remitente. El uso del correo electr=F3nico v=EDa Internet no permite asegurar ni la confidencialidad de los mensajes ni su correcta recepci=F3n. En el caso de que el destinatario no consintiera la utilizaci=F3n del correo electr=F3nico, deber=E1 ponerlo en nuestro conocimiento inmediatamente. =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =2E.......................... 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 individual sender. Internet e-mail guarantees neither the confidentiality nor the proper receipt of the message sent. If the addressee of this message does not consent to the use of internet e-mail, please inform us inmmediately. =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =20