Re: 7.4 Wishlist - Mailing list pgsql-hackers

TODO updated.  Thanks for the clarification.

---------------------------------------------------------------------------

Hannu Krosing wrote:
> On Tue, 2002-12-03 at 16:00, Bruce Momjian wrote:
> > Is WITH a TODO item?
> 
> It is disguised as 
> 
> Exotic Features
> ===============
> 
> * Add sql3 recursive unions
> 
> Which was added at my request in dark times, possibly when PostgreSQL
> was called postgres95 ;)
> 
> This should be changed  to two items
> 
> * Add SQL99 WITH clause to SELECT
> 
> * Add SQL99 WITH RECURSIVE to SELECT
> 
> 
> > ---------------------------------------------------------------------------
> > 
> > Hannu Krosing wrote:
> > > On Tue, 2002-12-03 at 09:20, Dennis Bj?rklund wrote:
> > > > On Tue, 3 Dec 2002, Magnus Naeslund(f) wrote:
> > > > 
> > > > > Now convert this query so that it only evaluates the date_part thing
> > > > > ONCE:
> > > > > 
> > > > > select t.id, date_part('days',now()-t.stamp) from table_name t where
> > > > > date_part('days',now()-t.stamp) > 20;
> > > > 
> > > > Something like this could work:
> > > > 
> > > > select *
> > > >   from (select t.id, date_part('days',now()-t.stamp) AS d
> > > >           from table_name t) AS t1
> > > >  where t1.d > 20;
> > > > 
> > > > That aside I also would like some sort of local names. Something like the
> > > > let construct used in many functional languages (not exaclty what you want
> > > > above, but still):
> > > > 
> > > > let t1 = select * from foo;
> > > >     t2 = select * from bar;
> > > > in select * from t1 natural join t2;
> > > > 
> > > > But even though I would like to give name to subexpressions like above, I
> > > > still think postgresql should stick to standards as close as possible.
> > > 
> > > the standard way of doing it would be SQL99's WITH :
> > > 
> > > with t1 as (select * from foo)
> > >      t2 as (select * from bar)
> > > select * from t1 natural join t2;
> > > 
> > > you can even use preceeding queries
> > > 
> > > with t1 as (select a,b from foo)
> > >      t1less as (select a,b from t1 where a < 0)
> > >      t1zero as (select a,b from t1 where a = 0)
> > > select * from t1zero, t1less, where t1zero.b = t1less.a;
> > > 
> > > Having working WITH clause is also a prerequisite to implementing SQL99
> > > recursive queries (where each query in WITH clause sees all other
> > > queries in the WITH clause)
> > > 
> > > I sent a patch to this list recently that implements the above syntax,
> > > but I currently dont have knowledge (nor time to aquire it), so if
> > > someone else does not do it it will have to wait until January.
> > > 
> > > OTOH, I think that turning my parsetree to a plan would be quite easy
> > > for someone familiar with turning parestrees into plans ;)
> > > 
> > > I offer to check if it works in current (and make it work again if it
> > > does not) if someone would be willing to hold my hand in implementation
> > > parsetree-->plan part ;). 
> > > 
> > > I think that for non-recursive queries this is all that needs to be
> > > done, i.e. the plan would not care if the subqueries were from FROM,
> > > from WITH or from separately defined views.
> > > 
> > > -- 
> > > Hannu Krosing <hannu@tm.ee>
> > > 
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 3: if posting/reading through Usenet, please send an appropriate
> > > subscribe-nomail command to majordomo@postgresql.org so that your
> > > message can get through to the mailing list cleanly
> > > 
> -- 
> Hannu Krosing <hannu@tm.ee>
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073