Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id AE0802E0039 for ; Fri, 15 Feb 2008 10:14:54 -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 75681-05 for ; Fri, 15 Feb 2008 10:14:50 -0400 (AST) Received: from fk-out-0910.google.com (fk-out-0910.google.com [209.85.128.190]) by postgresql.org (Postfix) with ESMTP id 2021C2E002C for ; Fri, 15 Feb 2008 10:14:49 -0400 (AST) Received: by fk-out-0910.google.com with SMTP id b27so850635fka.3 for ; Fri, 15 Feb 2008 06:14:48 -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=Q7ZdGi1caNXzUGMZnSA/SFeR+xqoCcq3Ie8sgus6Umg=; b=R8Bl8hY1av0UZnFuItZftKUG5aY2YSUVDNvyLD7VBi+L/BUBb2gAhB8oUTg5jvX3SFuPRArsOETDr67ZoFTP+3DA9QLGcACvg/a2eYn+en8Pw9uQDaoAznPw8Q7p8A87hgoBl8pXb2StPLsKGVXILXG3Xqweixr3MKEwEmmW+Bs= 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=O/WCmg3xBxf9gyat2+F9rQLV4Z2MavmDJ1ZRE2gCZ6OUY7WRXbOjdkn9uM5O9yBbIloq8xU965xWr8pLSLZj/erkpZMs8Aq4JUTbXByf/qEpQsCDS3h43eQ4KYatJlsUM3WDe/UXm9F7vFz4nEyCKAiqLNdlMXrMXWz0g98naS0= Received: by 10.78.187.17 with SMTP id k17mr3383018huf.38.1203084888363; Fri, 15 Feb 2008 06:14:48 -0800 (PST) Received: by 10.78.146.1 with HTTP; Fri, 15 Feb 2008 06:14:48 -0800 (PST) Message-ID: <3caa866c0802150614u29aab1ebl9a26574ad8e66e2b@mail.gmail.com> Date: Fri, 15 Feb 2008 19:14:48 +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: <200802141354.28669.albert@sedifa.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_7588_13405796.1203084888346" References: <3caa866c0802130625k728a4a5dl23bffb56c1ded6e9@mail.gmail.com> <200802131623.57343.albert@sedifa.com> <3caa866c0802140435v4af4f2a1y183883db977eb151@mail.gmail.com> <200802141354.28669.albert@sedifa.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/179 X-Sequence-Number: 28721 ------=_Part_7588_13405796.1203084888346 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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=3D' 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 =3D 'update ' || t1 || ' as t set GPPP=3D' ||t2row.gppp||' where product=3D'||quote_literal(t2row.product); execute cmd2; cmd2 =3D 'update ' || t1 || ' as t set PPD=3D' ||t2row.ppd||' where product=3D'||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 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 A= S > $$ > > 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)*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=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 b= y > > > > 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=E0tica Sedifa, S.L. > > Av. Can Bordoll, 149 > 08202 - Sabadell (Barcelona) > Tel. 93 715 51 11 > Fax. 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 > ........................ 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 > ........................... 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 > > > > ------=_Part_7588_13405796.1203084888346 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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=3D' select
product, (case when sum(pd) <&g= t; 0 then sum(gd)/sum(pd)*100 else 0 end  ) as gppp, (case when sum(tl= d) <> 0 then sum(pd)/sum(tld) else 0 end  ) as ppd  from &#= 39;|| t1 || ' as dummy group by dummy.product' ;

for t2row in execute cmd1 loop

    cmd2 =3D '= update ' || t1 || ' as t  set  GPPP=3D' ||t2row.gppp|= |' where product=3D'||quote_literal(t2row.product);
  =   execute cmd2;

    cmd2 =3D 'update ' |= | t1 || ' as t  set  PPD=3D' ||t2row.ppd||' where pro= duct=3D'||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 <albert@sedifa.com> wrote:
You need the stri= ng concatenation operator ||. Take a look at
http://www.postgresql.org/docs/8.3/static/functions-stri= ng.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, w= hat is wrong?
> Is this the right way?
>
>
> CREATE OR REPLACE FUNCTION test ( t1  text,t2 text  ) RETURN= S 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)*100
> else 0 end  ) as gppp
> from ' temp1 ' as dummy group by dummy.product,dummy.totalclai= msgroup,
> 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.produc= t,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" nea= r 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 er= ror. Is
> >
> > there
> >
> > > any way I could achieve this?
> >
> > You're looking for EXECUTE:
> >
> > http://www.postgresql.org/docs/8.3/s= tatic/plpgsql-statements.html#PLPGSQL
> >-STATEMENTS-EXECUTING-DYN
> >
> > > CREATE OR REPLACE FUNCTION test ( t1  text,t2 text &nbs= p;) 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.mon= ths
> >
> > ;
> >
> > > update temp1 as t  set
> > >  GPPP=3D(select gppp  from temp2  as dummy wh= ere
> > > 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...
> >
> > >                 &nbs= p;                     &n= bsp;                     =  ^
> > > QUERY:  select product, (case when sum(pd) <> 0 t= hen
> > > sum(gd)/sum(pd)*100 else 0 end ) as gppp from  $1  = ;as dummy group by
> > > dummy.product, dummy.totalclaimsgroup,dummy.avgmems,dummy.mo= nths
> > > 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=E0tica Sedifa, S.L.

Av. Can Bordoll, 149
08202 - Sabadell (Barcelona)
Tel. 93 715 51 11
Fax. 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
........................  AVISO LEGAL  ..........................= ..
La   presente  comunicaci=F3n  y sus anexos tiene como desti= natario la
persona a  la  que  va  dirigida, por  lo  qu= e  si  usted lo  recibe
por error  debe  notificarlo  al  remitente  y &nb= sp; eliminarlo   de  su
sistema,  no  pudiendo  utilizarlo,  total  o &nbs= p; parcialmente,   para
ning=FAn  fin.  Su  contenido  puede  tener inform= aci=F3n confidencial o
protegida legalmente   y   =FAnicamente   expresa  la &= nbsp;opini=F3n     del
remitente.  El   uso   del   correo   electr=F3nic= o   v=EDa Internet  no
permite   asegurar    ni  la   confidencialidad &n= bsp; 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
........................... DISCLAIMER .............................
This message and its  attachments are  intended  exclusively= for the
named addressee. If you  receive  this  message  in &nb= sp; error,  please
immediately delete it from  your  system  and notify the sen= der. You
may  not  use  this message  or  any  part &n= bsp;of it  for any  purpose.
The   message   may  contain  information  that &n= bsp;is  confidential or
protected  by  law,  and  any  opinions  expr= essed  are those of the
individual    sender.  Internet  e-mail   guarante= es   neither   the
confidentiality   nor  the  proper  receipt  of &n= bsp;the  message  sent.
If  the  addressee  of  this  message  does &= nbsp;not  consent to the use
of   internet    e-mail,    please    in= form     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




------=_Part_7588_13405796.1203084888346--