Re: WITH clause

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!  This is just the kind of discussion I was
trying to start :)

Cheers,
D
-- 
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100    cell: +1 415 235 3778