In the attached script, second call to test1() causes error:

select test1();
 test1 
-------
     0
(1 row)

select test1();
psql:/home/t-ishii/tmp/aaa.sql:13: NOTICE:  Error occurred while executing PL/pgSQL function test1
psql:/home/t-ishii/tmp/aaa.sql:13: NOTICE:  line 5 at select into variables
psql:/home/t-ishii/tmp/aaa.sql:13: ERROR:  Relation 422543 does not exist

Maybe PL/pgSQL cache problem?
--
Tatsuo Ishii
----------------------------------------------------------------------
drop function test1();
create function test1() returns int as '
declare
rec RECORD;
begin
create temp table temp_aaa (i int);
select into rec * from temp_aaa;
drop table temp_aaa;
return 0;
end;
'language 'plpgsql';
select test1();
select test1();

Sun, 30 Sep 2001 21:13:02 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
PL/pgSQL bug?
Tatsuo Ishii <t-ishii@sra.co.jp> writes:
> Maybe PL/pgSQL cache problem?

This is a well-known problem: plpgsql caches a query plan that refers
to the first version of the temp table, and it doesn't know it needs
to rebuild the plan.  AFAIK the only workaround at present is to use
EXECUTE for queries referencing the temp table.

			regards, tom lane

Sun, 30 Sep 2001 11:35:23 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: PL/pgSQL bug? 
Tatsuo Ishii wrote:
> In the attached script, second call to test1() causes error:

    Well known error.

    PL/pgSQL  creates  saved  execution  plans  for  almost every
    expression and query using SPI_prepare(), SPI_saveplan().  If
    any  of  the  objects,  referenced  from  such  a  plan get's
    dropped, they become invalid and for now,  only  reconnecting
    to the database can heal.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com


Sun, 30 Sep 2001 12:18:08 -0400 (EDT)
Jan Wieck <JanWieck@Yahoo.com>
Re: PL/pgSQL bug?
> This is a well-known problem: plpgsql caches a query plan that refers
> to the first version of the temp table, and it doesn't know it needs
> to rebuild the plan.  AFAIK the only workaround at present is to use
> EXECUTE for queries referencing the temp table.

But EXECUTE does not support select into, does it?
--
Tatsuo Ishii

Mon, 01 Oct 2001 09:55:15 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: PL/pgSQL bug? 
Tatsuo Ishii <t-ishii@sra.co.jp> writes:
>> AFAIK the only workaround at present is to use
>> EXECUTE for queries referencing the temp table.

> But EXECUTE does not support select into, does it?

You could probably get the result you want using
	FOR rec IN EXECUTE text_expression LOOP ...

			regards, tom lane

Mon, 01 Oct 2001 12:58:26 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: PL/pgSQL bug? 