Re: 7.4 Wishlist - Mailing list pgsql-hackers

From Hannu Krosing
Subject Re: 7.4 Wishlist
Date
Msg-id 1038915968.17102.32.camel@huli
Whole thread Raw
In response to Re: 7.4 Wishlist  (Dennis Björklund <db@zigo.dhs.org>)
Responses Re: 7.4 Wishlist
Re: 7.4 Wishlist
List pgsql-hackers
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>


pgsql-hackers by date:

Previous
From: Karel Zak
Date:
Subject: Re: 7.4 Wishlist
Next
From: Jan Hartmann
Date:
Subject: Re: PostgreSQL in Universities (Was: Re: 7.4 Wishlist)