hello people, my name is Poyo, I'm from a little town near from Buenos Aire=
s -Argentina- and
i'm having problems with the creation/drop of tables from inside of functio=
n's.=20
in other words: if you execute the below points in order (1,2,3,4,5) you 'l=
l see this:
ERROR: relation with OID 17687 does not exist
CONTEXT: PL/pgSQL function "myfunc" line 5 at for over select rows
the oid 17687 is corresponding with the table created inside the function (=
SELECT INTO newtable) (see point 3) but later, in the same function, this t=
able is dropped.
when the function (point 5) is executed again, this search for the oid's ta=
ble but never found it.
note: after the error, if you REPLACE the function and execute it again, th=
ere are no problem... and of corse, if you exectute it again without drop t=
he function, the error message will appear
i've tried this with posgresql 8.0 beta3 and beta4 running on linux (debian=
6.2.6 and debian 6.2.9) and postgresql 8.0 beta 5 and RC1 running on windo=
ws 2000 Professional with the same results.
pd: i tried a lot of way's to do this whitout other results: temporary tabl=
es and not, creating tables (with CREATE TABLE) with and with out "ON COMMI=
T DROP" explicit.
if you wan't more information about this, don't doubt and mail me.
thank you.
1)
CREATE TABLE test
(a int4, b int4);
2)
insert into test values (1,1);
insert into test values (2,2);
3)
CREATE OR REPLACE FUNCTION myfunc()
RETURNS SETOF test AS
$BODY$
DECLARE result test;
BEGIN
SELECT * INTO newtable FROM test;
FOR result in
SELECT * FROM newtable
loop
RETURN NEXT result;
END loop;
DROP TABLE newtable;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
4)
select * from myfunc();
5)
select * from myfunc();=