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 ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
