Re: pl/pgsql create table

Christoph Dalitz wrote:
> > Neil Conway wrote:
> > > Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073