Re: [BUGS] Solution for bug #899
--- Bruce Momjian <[EMAIL PROTECTED]> a écrit : > > Your patch has been added to the PostgreSQL > unapplied patches list at: > > http://momjian.postgresql.org/cgi-bin/pgpatches > > I will try to apply it within the next 48 hours. Ok, thanks a lot. Have a nice day. Laurent = The misspelling master is on the Web. _100 % Dictionnary Free ! //( / Dico / / Pleins d'autres fautes sur // /http://go.to/destroyedlolo (###( / http://destroyedlolo.homeunix.org:8080 Quoi, des fautes d'orthographe! Pas possible ;-D. ___ Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français ! Yahoo! Mail : http://fr.mail.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[BUGS] Bug #913: plpgsql function fails on second call
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
Re: [BUGS] Bug #913: plpgsql function fails on second call
On Tue, 18 Mar 2003 [EMAIL PROTECTED] 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). ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] 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. --- [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
