From: | Albert Cervera Areny <albert(at)sedifa(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Cc: | "Linux Guru" <linux(dot)binary(at)gmail(dot)com> |
Subject: | Re: Creating and updating table using function parameter reference |
Date: | 2008-02-13 15:23:56 |
Message-ID: | 200802131623.57343.albert@sedifa.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2008-02-13 15:48:42 | Re: Join Query Perfomance Issue |
Previous Message | Matthew | 2008-02-13 14:35:27 | Re: Small DB Server Advice |