Re: 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