The following bug has been logged online:

Bug reference: 1059
Logged by: Wilhelm

Email address: wilhelm.pakulla (AT) gmx (DOT) de

PostgreSQL version: 7.4

Operating system: Linux

Description: Second Call of a PGSQL-function fails

Details:

-- The Source:

-- Init Stuff
DROP FUNCTION plpgsql_call_handler () CASCADE;
CREATE FUNCTION plpgsql_call_handler () RETURNS LANGUAGE_HANDLER AS
'$libdir/plpgsql' LANGUAGE C;

CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql
HANDLER plpgsql_call_handler;

-- The function

CREATE FUNCTION f (INTEGER) RETURNS INTEGER
AS '
BEGIN
CREATE TABLE test ( x INTEGER );

-- Without this insert, everything works well...
INSERT INTO test VALUES (1);

DROP TABLE test CASCADE;

RETURN 0;
END;
' LANGUAGE 'plpgsql';

-- That works.
SELECT f(1);

-- Second Call fails.
SELECT f(1);

-- Thanks in advance, Wilhelm



---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

On Wednesday 21 January 2004 21:24, PostgreSQL Bugs List wrote:
Wilhelm - this is a known behaviour (I'm not sure it can be called a bug).

Because plpgsql is a compiled language, it converts references to tables and
other objects to their internal OID number. If you drop a table then recreate
it, it will get a new OID and your function will no longer be able to find
it.

There are two ways to deal with this:
1. Don't use plpgsql for these functions, use SQL or TCL/Perl/some other
interpreted language
2. Use EXECUTE to build a query from a string.

You should find plenty on this in the mailing list archives, usually in
connection to TEMPorary tables.

HTH

PS - If you think the documentation needs clarifying, suggestions on wording
are always appreciated, preferably on pgsql-docs mailing list.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

We have an FAQ for this:

<H4><A name="4.26">4.26</A>) Why can't I reliably create/drop
temporary tables in PL/PgSQL functions?</H4>

It says temporary tables, but it is valid for real tables too when you
creating/dropping them in the function.

---------------------------------------------------------------------------

PostgreSQL Bugs List wrote:
--
Bruce Momjian | http://candle.pha.pa.us
pgman (AT) candle (DOT) pha.pa.us | (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 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match