Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 342692E0294 for ; Thu, 14 Feb 2008 08:35:36 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 87520-07 for ; Thu, 14 Feb 2008 08:35:29 -0400 (AST) Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.174]) by postgresql.org (Postfix) with ESMTP id 2D1AB2E00E0 for ; Thu, 14 Feb 2008 08:35:28 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id j3so987808ugf.49 for ; Thu, 14 Feb 2008 04:35:27 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; bh=bxwLUP2zzZsoZW7/B2OxFmWLYIoqO2JUDbCx/0MTxR0=; b=r9kpt3ACj88o8kAXiVoZ4RLiLlkASVBLXPayaqdPj/h1YzWWbDb3CO9W0S9hSA167rcunQ9gr+cBuUk/wgtj3SoEj1obHmRTIyOj8TukvHeNa4HcdEwhLWWIl1E2iTK0zshqKBEE1rBDuNpwjtnlP3iodghmUATNUQmGUJpbOsQ= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=Rb2ar1CUQ3mY3OKIX+S1gwTiwcYeD920dlS5HMDL8AmAR9HIwejhnu38I3oTP3QwpOnIOWOzYMd1CbF/z7UcMirRNn08Vis7XC3tfdjNhPr8G2mgXpdHdqGGBL2RzUXc70t9Rbs6uSOcmlVjNlEVgR3bmlgA92vtE3mSotxhURY= Received: by 10.78.171.20 with SMTP id t20mr698677hue.20.1202992527375; Thu, 14 Feb 2008 04:35:27 -0800 (PST) Received: by 10.78.146.1 with HTTP; Thu, 14 Feb 2008 04:35:27 -0800 (PST) Message-ID: <3caa866c0802140435v4af4f2a1y183883db977eb151@mail.gmail.com> Date: Thu, 14 Feb 2008 17:35:27 +0500 From: "Linux Guru" To: "Albert Cervera Areny" Subject: Re: Creating and updating table using function parameter reference Cc: pgsql-performance@postgresql.org In-Reply-To: <200802131623.57343.albert@sedifa.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_969_20452932.1202992527367" References: <3caa866c0802130625k728a4a5dl23bffb56c1ded6e9@mail.gmail.com> <200802131623.57343.albert@sedifa.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/162 X-Sequence-Number: 28704 ------=_Part_969_20452932.1202992527367 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 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 > > > ------=_Part_969_20452932.1202992527367 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 <albert@sedifa.com> 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



------=_Part_969_20452932.1202992527367--