Re: Executing a user created function twice give an error - Mailing list pgsql-general

am  Thu, dem 04.12.2008, um  9:23:31 +0000 mailte Wajid Khattak folgendes:
> Did try by encapsulating the create and drop queries within EXECUTE, but it seems to be giving the same error. The
changedfunction is as follows: 

You need to execute the insert-statement also:

test=# create or replace function tmp_table() returns int as $$begin
execute 'create temporary table tmp_foo(i int)'; insert into tmp_foo
values(1);drop table tmp_foo; return 1;end;$$language plpgsql;
CREATE FUNCTION
test=*#
test=*#
test=*# select * from tmp_table();
 tmp_table
-----------
         1
(1 row)

test=*# select * from tmp_table();
ERROR:  relation with OID 187431854 does not exist
CONTEXT:  SQL statement "insert into tmp_foo values(1)"
PL/pgSQL function "tmp_table" line 1 at SQL statement
test=!# rollback;
ROLLBACK
test=# create or replace function tmp_table() returns int as $$begin
execute 'create temporary table tmp_foo(i int)'; execute 'insert into
tmp_foo values(1)';drop table tmp_foo; return 1;end;$$language plpgsql;
CREATE FUNCTION
test=*# select * from tmp_table();
 tmp_table
-----------
         1
(1 row)

test=*# select * from tmp_table();
 tmp_table
-----------
         1
(1 row)

test=*# select * from tmp_table();
 tmp_table
-----------
         1
(1 row)

test=*#


Peculiar, the drop table works without execute...


Regards, Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net