On Wed, Aug 31, 2005 at 07:43:45PM +0000, Matt Miller wrote:
> On Wed, 2005-08-31 at 15:29 -0400, Tom Lane wrote:
> > Matt Miller <mattm@epx.com> writes:
> > > I don't remember the last time I intended to write code that referenced
> > > something that did not exist in the database.
> >
> > Almost every day, people try to write stuff like
> >
> > CREATE TEMP TABLE foo ... ;
> > INSERT INTO foo ... ;
> > etc etc
> > DROP TABLE foo ;
>
> Point taken.
>
> PL/SQL requires all DDL to be dynamic SQL. For example:
>
> execute immediate 'drop table foo';
BTW, the way you handled this case in DB2 was:
CREATE TEMP TABLE foo ...;
CREATE FUNCTION blah AS ...;
DROP TEMP TABLE foo;
This way the object you wanted did exist when you were creating the
function. Of course it would be better if plpgsql could just read the
DDL and deal with it... but I'd say that doing the CREATE TABLE outside
the statement is better than nothing.
Actually, I think you only had to do the CREATE TEMP TABLE outside the
function creation if the function didn't create the temp table itself.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com 512-569-9461