You have to use EXECUTE when creating a temp table in a function --- see FAQ on it.
--------------------------------------------------------------------------- [EMAIL PROTECTED] wrote: > John Duffy ([EMAIL PROTECTED]) 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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073 ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
