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];

Here res[1] would be the whole pattern match, res[2] & res[3] would be
the set of digits before x and the set of digits after x,
respectively.

Ideally, there could also be (optionally) res[pre] and res[post] which
would refer to the stuff in foo that preceds the match and stuff in
foo that follows the match, respectively.

Perl weenies may think of $&, $1, ... $n, $` and $'.

Comments, ideas, brickbats, and "you're on crack" comments welcome. :)

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

Fri, 12 Dec 2003 10:13:56 -0800
David Fetter <david@fetter.org>
WITH clause
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?

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


Fri, 12 Dec 2003 19:47:26 +0100
Peter Eisentraut <peter_e@gmx.net>
Re: WITH clause
David Fetter <david@fetter.org> writes:
> 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];

This doesn't seem to be even remotely compatible with SQL99's idea
of WITH.

			regards, tom lane

Fri, 12 Dec 2003 13:52:32 -0500
Tom Lane <tgl@sss.pgh.pa.us>
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@fetter.org http://fetter.org/
phone: +1 510 893 6100    cell: +1 415 235 3778

Fri, 12 Dec 2003 10:57:27 -0800
David Fetter <david@fetter.org>
Re: WITH clause
On Fri, Dec 12, 2003 at 10:13:56AM -0800, 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.

I think you could be rather looking at keeping the regmatch_t * from
RE_compile_and_execute somewhere in the TopTransactionContext, and
create accessor functions to it.  You could do

BEGIN;
SELECT ... x ~ '([0-9]+)x([0-9]+)';
SELECT regex_parens(1), regex_parens(2);
COMMIT;

Etc, you get the idea.  Of course you could only access the captured
expressions from the last regex, and only within the same transaction
block.

-- 
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
Maybe there's lots of data loss but the records of data loss are also lost.
(Lincoln Yeoh)

Fri, 12 Dec 2003 16:07:54 -0300
Alvaro Herrera Munoz <alvherre@dcc.uchile.cl>
Re: WITH clause
In article <20031212190754.GA11548@dcc.uchile.cl> you wrote:
> On Fri, Dec 12, 2003 at 10:13:56AM -0800, 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.

> I think you could be rather looking at keeping the regmatch_t * from
> RE_compile_and_execute somewhere in the TopTransactionContext, and
> create accessor functions to it.  You could do

> BEGIN;
> SELECT ... x ~ '([0-9]+)x([0-9]+)';
> SELECT regex_parens(1), regex_parens(2);
> COMMIT;

> Etc, you get the idea.  Of course you could only access the captured
> expressions from the last regex, and only within the same
> transaction block.

A couple of little issues with this:

1.  It's assuming there will be exactly one regular expression with
associated atoms.  This is probably fixable by making the function
take two arguments, and it would be really, really nice if these were
nameable.  F'rex, regex_matches(regex => 1, match => 2) or
regex_matches(regex => 'the_one_i_wanted', match => 'post')

2.  It doesn't allow for the regex atoms to be used in the SQL
statement.

BTW, I should have said "row-level environment variables" rather than
"statement-level environment variables," although some of the latter
would be nice, too :)

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

Sophomoric (adj.)  The itch to be unique.

Fri, 12 Dec 2003 11:36:26 -0800
david@fetter.org (David Fetter)
Re: WITH clause
david@fetter.org (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];

So you're proposing what amounts to the LET function in Lisp:

  (let* ((homepath (user-homedir-pathname))
         (maildir (make-pathname :directory (append
                                              (pathname-directory
                                               homepath)
                                            '("Mail"))))
         (mailhome (merge-pathnames maildir homepath)))
    (do-something-with-mail mailhome)
    (do-something-with-home homepath))

Or the ML let structure...

let a = 1 and b = 2 in
  a + b;;
- : int = 3

I think this could be a "simply smashing" idea, allowing values to get
declared once, up front, offering the further potential for them to be
reused.

Unfortunately, this doesn't seem to fit with the way WITH is defined
in SQL.

And while the LISP presentation may have "way too many parentheses,"
the ML syntax, being pretty much orthogonal to what SQL currently
offers, strikes me as being preferable.
-- 
select 'cbbrowne' || '@' || 'ntlug.org';
http://www.ntlug.org/~cbbrowne/unix.html
:FATAL ERROR -- ILLEGAL ERROR

Sat, 13 Dec 2003 00:18:27 -0500
Christopher Browne <cbbrowne@acm.org>
Re: WITH clause
On Sat, 13 Dec 2003, Christopher Browne wrote:

> Unfortunately, this doesn't seem to fit with the way WITH is defined
> in SQL.

How is the WITH construct defined in SQL?

-- 
/Dennis


Sat, 13 Dec 2003 08:08:32 +0100 (CET)
Dennis Bjorklund <db@zigo.dhs.org>
Re: WITH clause
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


Sat, 13 Dec 2003 22:58:59 +0200
Hannu Krosing <hannu@tm.ee>
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

Sat, 13 Dec 2003 13:17:28 -0800
David Fetter <david@fetter.org>
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


Sun, 14 Dec 2003 00:27:05 +0200
Hannu Krosing <hannu@tm.ee>
Re: WITH clause

Hannu Krosing <hannu@tm.ee> writes:

> 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)
> ;

Well you don't need to go fetch from the table an extra time. Presumably the
data will be cached but it's still a lot of extra work to process the data
twice.

You could just do

select * 
  from (
        select x.*,  
              (select match(foo, '([0-9]+)x([0-9]+)') as res
       )
 where y = res[2]
    or y = res[3]



But what Hannu's saying is that the SQL Standard WITH is precisely syntactic
sugar for subqueries used like above.

It sounds like WITH is to subqueries as let is to lambda....


-- 
greg


13 Dec 2003 21:56:00 -0500
Greg Stark <gsstark@mit.edu>
Re: WITH clause