create or replace function= =20 TestTemp_refcur(refcursor) returns refcursor As '
declare
refc = alias=20 for $1;
lString=20 Varchar(4000);
begin
lStri= ng :=3D=20 '' CREATE OR REPLACE FUNCTION TESTTEMP() RETURNS varchar as ''''

BEGIN=20 '';
lString :=3D lStrin= g=20 || '' create temporary table temp_Table( Name Numeric);'';

lString :=3D lString ||= ''=20 insert into temp_Table values (1); '';

lString :=3D lString ||= ''=20 insert into temp_Table values (2);'';

lString :=3D lString ||= ''=20 return null; end;'''' language ''''plpgsql'''';'';

raise notice '' Notice = is %=20 '', lString;
execute=20 lString;
open refc for select * from temp_Table;
return= =20 refc;
end;
' language 'plpgsql';

begin;
select=20 TestTemp_refcur('funcursor');
fetch all in funcursor;
commit;<= /DIV>

psql:test18.sql:= 25:=20 WARNING: Error occurred while executing PL/pgSQL function=20 t
esttemp_refcur
psql:test18.sql:25: WARNING: line 20 at=20 open
psql:test18.sql:25: ERROR: Relation "temp_table" does not=20 exist
psql:test18.sql:26: ERROR: current transaction is aborted,= =20 queries ignored unti
l end of transaction block

Kindly guide as to solve this=20 problem....

Any help will be highly=20 appreciated........

Thanks & Regards

Vijay

------=_NextPart_000_0005_01C346EC.E8BE07E0-- From sszabo@megazone.bigpanda.com Sat Jul 12 04:03:46 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.225.181]) by svr1.postgresql.org (Postfix) with ESMTP id 80E33CB52E for ; Sat, 12 Jul 2003 04:03:46 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr5.postgresql.org [64.117.225.181]) (amavisd-new, port 10024) with ESMTP id 26762-09 for ; Sat, 12 Jul 2003 04:03:33 +0000 (GMT) Received: from megazone.bigpanda.com (unknown [66.180.233.26]) by svr1.postgresql.org (Postfix) with ESMTP id 656B4CB5E3 for ; Sat, 12 Jul 2003 01:03:13 -0300 (ADT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 17D51D736; Fri, 11 Jul 2003 21:03:32 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 0D5535C0F; Fri, 11 Jul 2003 21:03:32 -0700 (PDT) Date: Fri, 11 Jul 2003 21:03:31 -0700 (PDT) From: Stephan Szabo To: Vijay Kumar Cc: Subject: Re: Problem with temporary table -- Urgent In-Reply-To: <000801c346be$cf166db0$cf01a8c0@vijay> Message-ID: <20030711205654.U3777-100000@megazone.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/278 X-Sequence-Number: 14392 On Thu, 10 Jul 2003, Vijay Kumar wrote: > Hi, > We are using postgresql 7.3.3, we are encountering the following problems when we used temporary tables. > > Here with i'm sending my Sample function. > > create or replace function TestTemp_refcur(refcursor) returns refcursor As ' > declare > refc alias for $1; > lString Varchar(4000); > begin > lString := '' CREATE OR REPLACE FUNCTION TESTTEMP() RETURNS varchar as '''' > > BEGIN ''; > lString := lString || '' create temporary table temp_Table( Name Numeric);''; > > lString := lString || '' insert into temp_Table values (1); ''; > > lString := lString || '' insert into temp_Table values (2);''; > > lString := lString || '' return null; end;'''' language ''''plpgsql'''';''; > > raise notice '' Notice is % '', lString; > execute lString; Okay, this create a function which would create the table and insert some stuff. But you don't actually appear to call it afaics. > open refc for select * from temp_Table; I think you may need to use open for execute here as well to make it work with temporary tables in any case. > return refc; > end; > ' language 'plpgsql'; > > begin; > select TestTemp_refcur('funcursor'); > fetch all in funcursor; > commit; > > > psql:test18.sql:25: WARNING: Error occurred while executing PL/pgSQL function t > esttemp_refcur > psql:test18.sql:25: WARNING: line 20 at open > psql:test18.sql:25: ERROR: Relation "temp_table" does not exist > psql:test18.sql:26: ERROR: current transaction is aborted, queries ignored unti > l end of transaction block From dev@archonet.com Sat Jul 12 08:45:45 2003 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.192]) by svr1.postgresql.org (Postfix) with ESMTP id 3D702CB5FA for ; Sat, 12 Jul 2003 08:45:45 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr4.postgresql.org [64.117.224.192]) (amavisd-new, port 10024) with ESMTP id 30853-09 for ; Sat, 12 Jul 2003 08:45:29 +0000 (GMT) Received: from anchor-post-33.mail.demon.net (anchor-post-33.mail.demon.net [194.217.242.91]) by svr1.postgresql.org (Postfix) with ESMTP id 9E78ACB5C7 for ; Sat, 12 Jul 2003 05:45:11 -0300 (ADT) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-33.mail.demon.net with esmtp (Exim 3.35 #1) id 19bG0Y-000Mmh-0X; Sat, 12 Jul 2003 09:45:30 +0100 Received: from localhost (localhost.localdomain [127.0.0.1]) by mainbox.archonet.com (Postfix) with ESMTP id A179E178D5; Sat, 12 Jul 2003 09:45:29 +0100 (BST) Received: from client.archonet.com (client.archonet.com [192.168.1.16]) by mainbox.archonet.com (Postfix) with ESMTP id F0DE0163BE; Sat, 12 Jul 2003 09:45:28 +0100 (BST) Content-Type: text/plain; charset="iso-8859-1" From: Richard Huxton Organization: Archonet Ltd To: "Vijay Kumar" , Subject: Re: Problem with temporary table -- Urgent Date: Sat, 12 Jul 2003 09:45:35 +0100 User-Agent: KMail/1.4.3 References: <000801c346be$cf166db0$cf01a8c0@vijay> In-Reply-To: <000801c346be$cf166db0$cf01a8c0@vijay> MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200307120945.36058.dev@archonet.com> X-Virus-Scanned: by AMaViS snapshot-20020531 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/147 X-Sequence-Number: 14261 On Thursday 10 Jul 2003 9:39 am, Vijay Kumar wrote: > Hi, > We are using postgresql 7.3.3, we are encountering the following problems > when we used temporary tables. > > Here with i'm sending my Sample function. > > create or replace function TestTemp_refcur(refcursor) returns refcursor As [snip] > lString :=3D lString || '' create temporary table temp_Table( N= ame > Numeric);''; [snip] > open refc for select * from temp_Table; [snip] > psql:test18.sql:25: ERROR: Relation "temp_table" does not exist Sorry about the delay. The issue is that plpgsql is compiled, so the "open refc ... temp_Table"=20 refers to a specific entry in the system-tables. When the temp-table gets= =20 recreated it gets a new OID and so your function can't find it. Solution: 1. Don't use temp-tables 2. Use EXECUTE "..." which gets parsed when the function is run. Note that this problem applies to any table that gets dropped and restored.= I=20 try to keep my function code in the same file as the table(s) they rely on,= =20 that way I recreate both when I make changes. --=20 Richard Huxton