Re: temporary views

> Bruce Momjian wrote:
> > 
> > > Hi
> > > I have simple question: How to create view on a temporary table?
> > > I need this, because pl/pgsql function returns data via temporary table.
> > >
> > > View created on a temporary table is useful only to the end of session.
> > > Next time i create the same temp table i get
> > > "Table xxx with oid xxx doesn't exist"
> > 
> > Just name your temporary table the same name in every session.  Why
> > bother with a view.
> 
> Creating a view makes my life easier. My temporary table has fields 
> like id1,id2,id3,id4 and view translates it using inner joins to
> name1,name2,name3,name4. This temp table has always the same
> name and I don't want to do the translation inside pl/pgsql function.

OK, basically there is no way to create views reliably on temp tables:creattest=> create temp table x(y
int);CREATEtest=>create view z on x as select * from x;ERROR:  parser: parse error at or near "on"test=> create view z
asselect * from x;CREATEtest=> select * from z; y ---(0 rows)
 

Of course this works, but exiting the session and restarting it gets
you:
test=> create temp table x(y int);CREATEtest=> select * from z;ERROR:  Relation "x" with OID 16562 no longer exists

Internally, the problem is that the temp table is referenced by oid, not
table name.  If you create a temp in a later session, it doesn't have
the same oid as the one in the session where you created the view.

What actually should happen is that the view should go away at the end
of the session.  However, unlike indexes, we can have several tables
involved in a view so it is hard to know exactly how to handle this.

Seems like a TODO item, at least.  What we could do is to create views
as TEMP if they use temp tables and drop the view as soon as the session
ends .  You of course would have to recreate the view each time but
because it is a _temp_ view, it could be done reliably by multiple
backends at the same time.

Added to TODO:
* Allow views on temporary tables to behave as temporary views

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026