Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 8012D2E00B8 for ; Wed, 13 Feb 2008 11:24:13 -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 99709-01-3 for ; Wed, 13 Feb 2008 11:23:58 -0400 (AST) Received: from correo9.acens.net (correo9.acens.net [217.116.0.27]) by postgresql.org (Postfix) with ESMTP id 535BF2E0155 for ; Wed, 13 Feb 2008 11:24:04 -0400 (AST) Received: (qmail 15899 invoked from network); 13 Feb 2008 15:24:01 -0000 Received: from unknown (HELO sardina) ([83.175.220.10]) (envelope-sender ) by correo9.acens.net (qmail-ldap-1.03) with SMTP for ; 13 Feb 2008 15:24:01 -0000 From: Albert Cervera Areny Organization: Sedifa, S.L. To: pgsql-performance@postgresql.org Subject: Re: Creating and updating table using function parameter reference Date: Wed, 13 Feb 2008 16:23:56 +0100 User-Agent: KMail/1.9.5 Cc: "Linux Guru" References: <3caa866c0802130625k728a4a5dl23bffb56c1ded6e9@mail.gmail.com> In-Reply-To: <3caa866c0802130625k728a4a5dl23bffb56c1ded6e9@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-15" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200802131623.57343.albert@sedifa.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/137 X-Sequence-Number: 28679 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