See our FAQ about temporary tables:
<H3 id="item4.19">4.19) Why do I get "relation with OID #####
does not exist" errors when accessing temporary tables in PL/PgSQL
functions?</H3>
---------------------------------------------------------------------------
andrew_kazachkov@mail.ru wrote:
>
> The following bug has been logged online:
>
> Bug reference: 2124
> Logged by:
> Email address: andrew_kazachkov@mail.ru
> PostgreSQL version: 8.1.1-1
> Operating system: Windows
> Description: Error "relation with OID ... does not exist" when using
> temporary table in function.
> Details:
>
> After running function proc_3 (described below) more than once error
> "relation with OID ... does not exist" occures.
>
> First run of function proc_3() is OK but the second run always fails until
> we recreate function proc_2().
>
> Script to reproduce.
>
> --------------------------------------------------------
>
> --DROP FUNCTION proc_1();
>
> CREATE OR REPLACE FUNCTION proc_1() RETURNS int AS
> $BODY$
> DECLARE
> __nCount int;
> BEGIN
> SELECT INTO __nCount COUNT(*) FROM __tmp_xx;
> return __nCount;
> END;
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE;
>
>
> --DROP FUNCTION proc_2();
>
> CREATE OR REPLACE FUNCTION proc_2() RETURNS int AS
> $BODY$
> DECLARE
> __nCount int;
> BEGIN
> CREATE TEMPORARY TABLE __tmp_xx(
> nId int PRIMARY KEY,
> wstrName varchar(256) NOT NULL
> );
> INSERT INTO __tmp_xx (nId, wstrName) VALUES (1, 'xx');
> __nCount := proc_1();
> DROP TABLE __tmp_xx;
> return __nCount;
> END
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE;
>
>
> --DROP FUNCTION proc_3();
>
> CREATE OR REPLACE FUNCTION proc_3() RETURNS int AS
> $BODY$
> DECLARE
> __nCount int;
> BEGIN
> __nCount = proc_2();
> --DELETE FROM t_res;
> --INSERT INTO t_res(nId, nValue) VALUES(1, __nCount);
> return __nCount;
> END
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE;
>
>
> SELECT * FROM proc_3();
>
> SELECT * FROM proc_3();
>
> --------------------------------------------------------
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
