Re: Bug #913: plpgsql function fails on second call

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