Thread: Problem with temporary table -- Urgent

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

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