Re: WITH clause

On Fri, Dec 12, 2003 at 07:47:26PM +0100, Peter Eisentraut wrote:
> David Fetter wrote:
> > 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];

> Two questions:
>
> 1. What does this have to do with the SQL WITH clause?

It seems that the SQL WITH clause is a way to get what I'd think of as
"statement-level environment variables" available to the SQL statement.

> 2. How does the generalize to arbitrary user-defined operators?

I guess that would depend on whether they return arrays or singletons.

Even better, they'd be able to take as arguments and return hashes aka
associative arrays (cf. the TODO for plpgsql named parameters). I'm
not sure what the SQL spec has to say about that last, tho.

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