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@postgresql.org wrote:
> John Duffy (jbduffy@ntlworld.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
thefunction terminates with an error. If the function is recreated the function then works correctly for the first call
andthen 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@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