Re: WITH clause

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