pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedProblem with temporary table -- Urgent
3+ messages / 3 participants
[nested] [flat]
* Problem with temporary table -- Urgent
@ 2003-07-10 08:39 Vijay Kumar <vijay@kinera.com>
0 siblings, 2 replies; 3+ messages in thread
From: Vijay Kumar @ 2003-07-10 08:39 UTC (permalink / raw)
To: pgsql-sql
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;
open refc for select * from temp_Table;
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
Kindly guide as to solve this problem....
Any help will be highly appreciated........
Thanks & Regards
Vijay
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Problem with temporary table -- Urgent
@ 2003-07-12 04:03 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Vijay Kumar <vijay@kinera.com>
1 sibling, 0 replies; 3+ messages in thread
From: Stephan Szabo @ 2003-07-12 04:03 UTC (permalink / raw)
To: Vijay Kumar <vijay@kinera.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Problem with temporary table -- Urgent
@ 2003-07-12 08:45 Richard Huxton <dev@archonet.com>
parent: Vijay Kumar <vijay@kinera.com>
1 sibling, 0 replies; 3+ messages in thread
From: Richard Huxton @ 2003-07-12 08:45 UTC (permalink / raw)
To: Vijay Kumar <vijay@kinera.com>; pgsql-sql
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 := lString || '' create temporary table temp_Table( Name
> 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"
refers to a specific entry in the system-tables. When the temp-table gets
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
try to keep my function code in the same file as the table(s) they rely on,
that way I recreate both when I make changes.
--
Richard Huxton
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-07-12 08:45 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-10 08:39 Problem with temporary table -- Urgent Vijay Kumar <vijay@kinera.com>
2003-07-12 04:03 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-07-12 08:45 ` Richard Huxton <dev@archonet.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