pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedCreating and updating table using function parameter reference
5+ messages / 2 participants
[nested] [flat]
* Creating and updating table using function parameter reference
@ 2008-02-13 14:25 Linux Guru <linux.binary@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Linux Guru @ 2008-02-13 14:25 UTC (permalink / raw)
To: pgsql-performance
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?
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Creating and updating table using function parameter reference
@ 2008-02-13 15:23 Albert Cervera Areny <albert@sedifa.com>
parent: Linux Guru <linux.binary@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Albert Cervera Areny @ 2008-02-13 15:23 UTC (permalink / raw)
To: pgsql-performance; +Cc: Linux Guru <linux.binary@gmail.com>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Creating and updating table using function parameter reference
@ 2008-02-14 12:35 Linux Guru <linux.binary@gmail.com>
parent: Albert Cervera Areny <albert@sedifa.com>
0 siblings, 1 reply; 5+ messages in thread
From: Linux Guru @ 2008-02-14 12:35 UTC (permalink / raw)
To: Albert Cervera Areny <albert@sedifa.com>; +Cc: pgsql-performance
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
>
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Creating and updating table using function parameter reference
@ 2008-02-14 12:54 Albert Cervera Areny <albert@sedifa.com>
parent: Linux Guru <linux.binary@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Albert Cervera Areny @ 2008-02-14 12:54 UTC (permalink / raw)
To: Linux Guru <linux.binary@gmail.com>; +Cc: pgsql-performance
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@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
--
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.
====================================================================
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Creating and updating table using function parameter reference
@ 2008-02-15 14:14 Linux Guru <linux.binary@gmail.com>
parent: Albert Cervera Areny <albert@sedifa.com>
0 siblings, 0 replies; 5+ messages in thread
From: Linux Guru @ 2008-02-15 14:14 UTC (permalink / raw)
To: Albert Cervera Areny <albert@sedifa.com>; +Cc: pgsql-performance
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@sedifa.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@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
>
> --
> 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.
> ====================================================================
>
>
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2008-02-15 14:14 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-02-13 14:25 Creating and updating table using function parameter reference Linux Guru <linux.binary@gmail.com>
2008-02-13 15:23 ` Albert Cervera Areny <albert@sedifa.com>
2008-02-14 12:35 ` Linux Guru <linux.binary@gmail.com>
2008-02-14 12:54 ` Albert Cervera Areny <albert@sedifa.com>
2008-02-15 14:14 ` Linux Guru <linux.binary@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox