Bug #913: plpgsql function fails on second call

John Duffy (jbduffy(at)ntlworld(dot)com) reports a bug with a severity of 1
The lower the number the more severe it is.

Short Description
plpgsql function fails on second call

Long Description
A function containing a left outer join declared to return a set works correctly when first called. A second call to the function terminates with an error. If the function is recreated the function then works correctly for the first call and then fails on the second.

Sample Code
createdb test

create table a (id integer, value integer)
create table b (id integer, value integer)

insert into a (id, value) values (1,1)
insert into a (id, value) values (2,2)
insert into a (id, value) values (3,3)
insert into b (id, value) values (1,1)
insert into b (id, value) values (2,2)
insert into b (id, value) values (3,3)

create type ab as (a integer, b integer)

createlang plpgsql test

create or replace function test() returns setof ab as '
declare
row ab%ROWTYPE;
begin
create temp table ab as
select a.value as a_value, b.value as b_value
from a left outer join b
on a.id = b.id;

for row in select * from ab loop
return next row;
end loop;

drop table ab;

return;
end;
' language 'plpgsql';

test=# \i test-func.sql
CREATE FUNCTION

test=# select * from test();
a | b
---+---
1 | 1
2 | 2
3 | 3
(3 rows)

test=# select * from test();
WARNING: Error occurred while executing PL/pgSQL function test
WARNING: line 9 at for over select rows
ERROR: pg_class_aclcheck: relation 3759490 not found

No file was uploaded with this report

You have to use EXECUTE when creating a temp table in a function --- see
FAQ on it.

---------------------------------------------------------------------------

pgsql-bugs(at)postgresql(dot)org wrote:
> John Duffy (jbduffy(at)ntlworld(dot)com) reports a bug with a severity of 1
> The lower the number the more severe it is.
>
> Short Description
> plpgsql function fails on second call
>
> Long Description
> A function containing a left outer join declared to return a set works correctly when first called. A second call to the function terminates with an error. If the function is recreated the function then works correctly for the first call and then fails on the second.
>
> Sample Code
> createdb test
>
> create table a (id integer, value integer)
> create table b (id integer, value integer)
>
> insert into a (id, value) values (1,1)
> insert into a (id, value) values (2,2)
> insert into a (id, value) values (3,3)
> insert into b (id, value) values (1,1)
> insert into b (id, value) values (2,2)
> insert into b (id, value) values (3,3)
>
> create type ab as (a integer, b integer)
>
> createlang plpgsql test
>
> create or replace function test() returns setof ab as '
> declare
> row ab%ROWTYPE;
> begin
> create temp table ab as
> select a.value as a_value, b.value as b_value
> from a left outer join b
> on a.id = b.id;
>
> for row in select * from ab loop
> return next row;
> end loop;
>
> drop table ab;
>
> return;
> end;
> ' language 'plpgsql';
>
> test=# \i test-func.sql
> CREATE FUNCTION
>
> test=# select * from test();
> a | b
> ---+---
> 1 | 1
> 2 | 2
> 3 | 3
> (3 rows)
>
> test=# select * from test();
> WARNING: Error occurred while executing PL/pgSQL function test
> WARNING: line 9 at for over select rows
> ERROR: pg_class_aclcheck: relation 3759490 not found
>
>
> No file was uploaded with this report
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Tue, 18 Mar 2003 pgsql-bugs(at)postgresql(dot)org wrote:

> create temp table ab as
> select a.value as a_value, b.value as b_value
> from a left outer join b
> on a.id = b.id;
>
> for row in select * from ab loop
> return next row;
> end loop;
>
> drop table ab;

If you're going to be doing create/drop table, you need to use
EXECUTE on the queries relating to the table, otherwise it'll cache
the plan which is invalid on the second pass since the table being
referenced is gone (replaced by a new ab table).