Thread: PL/pgSQL bug?

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();

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

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
fornow,  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

> 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

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 usingFOR rec IN EXECUTE text_expression LOOP ...
        regards, tom lane