Re: 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 <albert(at)sedifa(dot)com>
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 <albert(at)sedifa(dot)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
>
> --
> 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 asegurar ni la confidencialidad de los mensajes
> ni su correcta recepción. En el 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
> 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.
> ====================================================================
>
>
>
>