Re: 7.4 Wishlist

Is WITH a TODO item?

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

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(at)tm(dot)ee>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

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