Re: WITH clause - Mailing list pgsql-hackers

From Hannu Krosing
Subject Re: WITH clause
Date
Msg-id 1071354425.2864.15.camel@fuji.krosing.net
Whole thread Raw
In response to Re: WITH clause  (David Fetter <david@fetter.org>)
List pgsql-hackers
David Fetter kirjutas L, 13.12.2003 kell 23:17:
> On Sat, Dec 13, 2003 at 10:58:59PM +0200, Hannu Krosing wrote:
> > David Fetter kirjutas R, 12.12.2003 kell 20:13:
> > > Kind people,
> 
> > > I'm looking to the SQL WITH clause as a way to get better regex
> > > support in PostgreSQL.  I've been chatting a little bit about
> > > this, and here's an idea for a behavior.  Implementation details
> > > TBD.
> 
> > > WITH res = match (x.foo, '([0-9]+)x([0-9]+)')
> > > SELECT *
> > > FROM x
> > > WHERE y = res[2]
> > > OR    y = res[3];
> > 
> > why not use current standard syntax
> > 
> > SELECT x.*
> >   FROM x, 
> >      (select match (x.foo, '([0-9]+)x([0-9]+)')
> >         from x innerx
> >        where innerx.pk = x.pk
> >        ) as res
> > HAVING y = get_match_group(res, 2)
> >     OR y = get_match_group(res, 3)
> > ;
> > 
> > with functions match(str,regex) which returns a match object and
> > get_match_group which extracts matched groups from it.
> 
> Hannu, excellent idea! 

As I understand it the SQL Standard WITH can be rewritten to SELECT in
FROM if the query is not recursive and does not reference other
subqueries. That's why I proposed the above syntax (we don't yet support
WITH). 

>  This is just the kind of discussion I was
> trying to start :)

Thanks ;)

Actually I notice that WHERE could be used there instead of HAVING.
Should be faster too.

------------
Hannu



pgsql-hackers by date:

Previous
From: David Fetter
Date:
Subject: Re: WITH clause
Next
From: Bruno Wolff III
Date:
Subject: Re: ORDER BY and DISTINCT ON