Re: Creating and updating table using function parameter reference

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.
====================================================================