Re: Executing a user created function twice give an error

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 changed function 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