From: | Hannu Krosing <hannu(at)tm(dot)ee> |
---|---|
To: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> |
Cc: | Dennis Björklund <db(at)zigo(dot)dhs(dot)org>, "Magnus Naeslund(f)" <mag(at)fbab(dot)net>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: 7.4 Wishlist |
Date: | 2002-12-03 19:06:57 |
Message-ID: | 1038942416.17102.64.camel@huli |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-advocacy pgsql-general pgsql-hackers |
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(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
> >
--
Hannu Krosing <hannu(at)tm(dot)ee>
From | Date | Subject | |
---|---|---|---|
Next Message | Marc G. Fournier | 2002-12-03 19:13:56 | Re: PostgreSQL in Universities (Was: Re: 7.4 Wishlist) |
Previous Message | Ian Barwick | 2002-12-03 18:02:08 | Re: French translation proposal for the FAQ |
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2002-12-03 19:07:37 | Re: alter table differences on Cygwin and Linux? |
Previous Message | Richard Huxton | 2002-12-03 19:04:04 | Re: Regarding connection thru web application |
From | Date | Subject | |
---|---|---|---|
Next Message | Marc G. Fournier | 2002-12-03 19:11:36 | Re: [GENERAL] PostgreSQL Global Development Group Announces |
Previous Message | Magnus Naeslund(f) | 2002-12-03 18:32:48 | Re: Backend crash with tsearch |