BUG #1059: Second Call of a PGSQL-function fails

On Wednesday 21 January 2004 21:24, PostgreSQL Bugs List wrote:

Bug reference: 1059
Logged by: Wilhelm
Email address: wilhelm.pakulla@gmx.de

PostgreSQL version: 7.4
Operating system: Linux
Description: Second Call of a PGSQL-function fails

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

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:

The following bug has been logged online:

Bug reference: 1059
Logged by: Wilhelm

Email address: wilhelm.pakulla@gmx.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

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.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