RE: [HACKERS] Novice Question - Mailing list pgsql-hackers

> > OK, this is a complete novice question.
> > Do we or do we not have temp tables (and/or unnamed tables)? 
> > If we don't why not?
> > I can see them making quite a few places of current 
> development a lot
> > easier and flexible.  I do realize TANSTAAFL, but as I grok 
> it could be
> > used for multiple tuple return from functions, the
> > deferrable-constraints problems, implementing the SQL92 
> join syntax, and
> > subselects in from clauses and select lists.
> 
> It is on the TODO list, and is not a hard job at all.

Well, in that case...
I am a visionary when not well informed, so before I get more knowledge
than my optimism can stomach, let me share my vision(s).

REWRITE/RULE SYSTEM: If we could easily keep the targetlist result in a temporary table
(hey it's already in memory, pipe it out to disk async if we run out of
ram) we could easily recreate any statepoint of a transaction.  All we
need is a way of determining which targetlist we need before we need
them.  I think that the crossref table could greatly assist here.  (Now
I read Vadim's message about the above and I think this is already
figured out, but I was lost at about line 3 of his reply.)  

This would also allow application of certain currently restricted cases,
such as view on select union and select distinct.  "How?" you may ask,
because the rule could be executed as is and the new query applied to
the temp table results (more costly than the current method but it will
get the job done).  I'll let Jan take the idea from there, but I think
you get the idea.

SQL92 JOIN syntax: I get into everything don't I, Tom.  "What is my temp-table view for
JOINS?" you ask.  Well, I'm glad you did.
If unnammed-temp-tables were treated as normal tables within a
select/JOIN clause easily implemented JOINS.  This also takes multiple
passes, but if I'm not mistake something like his is already done for
joins anyway.  Example of my idea:  (t1 LEFT OUTER JOIN t2 ON t1.i=t2.i) a1would generate a temporary table
named/aliaseda1 that had the
 
same results as (I think this will work):select t1.*, t2.* from t1, t2 WHERE t1.i=t2.i      UNION ALL      select t1.*,
NULL,NULL, ... FROM t1 WHERE NOT EXISTS (select 1
 
FROM t2 where t1.i=t2.i);
which is then taken and used in the next join clause.  This also make it
easy to include subselects in the from clause, just make the results a
temp-table (I know that this doesn't give you much extra power, but I'm
a sucker for easily realized flexibility).  So, Tom what do you think?

TUPLES RETURNED FROM FUNCTIONS: I believe this is another area under Jan's domain, but I could be
wrong.  The idea would be to treat these function as a temp-table.
"Why?" (I know you're thinking it) because then we could have these
functions in our from clause.  Alias the function and you can access
it's return members just as a table (ie select * from my_function();).

Oh well, I have more vision, but I'll let you guys either diminish or
augment these before I share any more.  SO, be gentle with me :).
-DEJ