Re: pl/pgsql create table

Christoph Dalitz wrote:
> > Neil Conway wrote:
> > > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > > When referencing created/dropped tables in pl/pgsql, use EXECUTE to
> > > > prevent the table oid from being stored in function as precompiled.  It
> > > > is mentioned in the current FAQ.  The solution is for us to
> > > > automatically add EXECUTE somehow.
> > >
> > > IMHO, no -- the solution is to automatically invalidate compiled query
> > > plans when a dependant relation is removed. Not exactly sure how to do
> > > it, but I was thinking of tackling this for 7.4 (suggestions are
> > > welcome, of course).
> >
> > Yes, but how do you handle cases where the table gets create/dropped
> > inside the transaction.  It is clearly tricky.
> >
> If I remember right, Oracle does not allow DDL-Statements in PL/SQL
> procedures. You have to use a special package (I have forgotten the name)
> which prevents the DDL-Statement from being precompiled.
>
> So maybe this is a simple workaround: forbid DDL-Statements without EXECUTE.

I wish it was that simple.  You could create a temp table, execute the
function, then drop/recreate the table, and when you reexecute the
function, the temp table with the precompiled oid is gone.

It can get even worse because you could create a permanent table, run
the function that accesses it, then create a temp table with the same
name that masks the premanent table, but when you execute the function,
it will not see the temp table properly.  Clearly, it is a mess.

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