Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 94C552E01A9 for ; Wed, 13 Feb 2008 10:25:18 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 12930-08 for ; Wed, 13 Feb 2008 10:24:56 -0400 (AST) Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.168]) by postgresql.org (Postfix) with ESMTP id 2851B2E0078 for ; Wed, 13 Feb 2008 10:25:03 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id j3so728202ugf.49 for ; Wed, 13 Feb 2008 06:25:02 -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:mime-version:content-type; bh=s07uqj2hTyfzNFUWzCaLP2ihkZdUqd8ePAMhMGekfYw=; b=pkLScZVT2tojOT7kOVrUsSPhjqJ8WrrkJR6+X9zi4oZ/WPOwFKjMmZMtwMWqMUkUcmJVBOOq6hSdkVDz08UB8J/s1RoqGKp4wmKls6FQtu2kEXfjlP8/QK2k/+7LsSQXm6bHjPf5cTZ9m1SG/q3y5TDBUpWEEo7y6Iz77UdFGrc= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:mime-version:content-type; b=rEaF/+rl8LJcFbHfvYZR0BqMTLqFJmcbRvoLTq3ggqeqH9jVDXeZR/KDvQbfbmhOYOfVWi3dxdArqhVAlBrSjq4wjMMeOS0jtrKwad1YwZoVhalllu5zIdDY0BR+DP/n3J4fEOanMqAJI9oALlmHP5I3MkF7oWJbkVEn2HaRj18= Received: by 10.78.199.8 with SMTP id w8mr1044859huf.60.1202912702200; Wed, 13 Feb 2008 06:25:02 -0800 (PST) Received: by 10.78.146.1 with HTTP; Wed, 13 Feb 2008 06:25:02 -0800 (PST) Message-ID: <3caa866c0802130625k728a4a5dl23bffb56c1ded6e9@mail.gmail.com> Date: Wed, 13 Feb 2008 19:25:02 +0500 From: "Linux Guru" To: pgsql-performance@postgresql.org Subject: Creating and updating table using function parameter reference MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_3350_17898892.1202912702204" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/134 X-Sequence-Number: 28676 ------=_Part_3350_17898892.1202912702204 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 ------=_Part_3350_17898892.1202912702204 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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


------=_Part_3350_17898892.1202912702204--