Re: Temporary views

On Wed, Feb 11, 2004 at 12:10:29AM -0500, Tom Lane wrote:
> Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> > Currently you can create temporary tables that are deleted at the end of the
> > session. But how about temporary views? It's just a table with a rule so I
> > don't imagine it would be terribly difficult. Are there any issues I havn't
> > thought of?
>
> > While we're at it, what about temporary functions?
>
> AFAICS, anything created in the temp schema will get zapped at backend
> shutdown. (It would be a good idea to rename RemoveTempRelations and
> related functions in namespace.c if they are going to be used to zap
> other sorts of objects, but they will work as-is.)
>
> So this is doable with just a Small Matter of Programming to pass the
> is-temp flag through from the grammar to wherever the object gets
> created.

Well, the rules should disappear with the table, so I guess everything
should be fine in that respect.

> Whether it's worth the trouble is another question. What's the
> use-case?

Oh, I have a script which executes lots of queries which use several similar
rather complicated subqueries. By encapsulating these subqueries into views
all these queries could be simplified. The subqueries are not identical
between runs, though they are the same within a run.

The subqueries are not used elsewhere in the system and I'd feel better if
the definitions were near the code that used them rather than permanently in
the database where they are just clutter.

The workaround ofcourse is to do:

DROP VIEW x; -- might error
CREATE VIEW x AS ...

... run script ...

DROP VIEW x;

and just hope no-one use the same view/table name elsewhere. It just
occurred to me that this is precisely the problem temp tables solve.

Essentially I'm using views for macro expansion.

Think it's worth it?
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> (... have gone from d-i being barely usable even by its developers
> anywhere, to being about 20% done. Sweet. And the last 80% usually takes
> 20% of the time, too, right?) -- Anthony Towns, debian-devel-announce