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(at)fetter(dot)org http://fetter.org/
phone: +1 510 893 6100 cell: +1 415 235 3778