Re: temporary views

> Bruce Momjian wrote:
> >
> > We can handle the temp views thing two ways, either allow views to map
> > to temp tables by name, or allow temp views to map to temp tables that
> > exist at creation time and drop the views on session exit. The original
> > poster clearly wanted the first behavior, but I agree with Peter that
> > the second has fewer surprises for the user and is more standard.
> I think, that referring tables by names would be enough. I found another
> problem connected to this. There is something like this in documetation:
> "Existing permanent tables with the same name are not visible
> (in this session) while the temporary table exists." Why not to overlap
> permanent table? Currently it doesn't work:
>
> create table x (a integer);
> create view y as select * from x;
> select * from y;
> OK
> create temp table x as select * from x;
> select * from y;
> ERROR: Relation "x" with OID 364752 no longer exists

Yes, we could add code that tried the temp table first, and if it didn't
match the oid, fall back to the permanent table. Of course, it would
break the temp table overlap rules.

Of course, there is the question of whether it is worth doing this. If
you create the view after the temp table is created it would properly
map to the temp table. If you have created a temp table that masks the
real table, maybe you want the view to fail.

Temp tables masking real tables is already pretty powerful and mapping
some fallback rules on top of this seems a little too powerful and perhaps
a little too confusing.

> > It would be interesting of plpgsql could try for an table match by oid
> > first, and if that fails, try a match by table name and match only if a
> > temp table is hit. So basically the only table-name matching that would
> > happen would be hits on temp tables.
> But why only plpgsql? Would it be difficult to add it to SQL
> implementation
> of PostgreSQL?

Yes, it would be done there too.

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