Re: [HACKERS] 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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] 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] > ORy = 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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] 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] > > ORy = 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 [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100cell: +1 415 235 3778 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] 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] > > > ORy = 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
Re: [HACKERS] ORDER BY and DISTINCT ON
On Fri, Dec 12, 2003 at 18:39:20 -0500, Neil Conway <[EMAIL PROTECTED]> wrote: > /* >* If the user writes both DISTINCT ON and ORDER BY, then the >* two expression lists must match (until one or the other >* runs out). Otherwise the ORDER BY requires a different >* sort order than the DISTINCT does, and we can't implement >* that with only one sort pass (and if we do two passes, the >* results will be rather unpredictable). However, it's OK to >* have more DISTINCT ON expressions than ORDER BY >* expressions; we can just add the extra DISTINCT values to >* the sort list, much as we did above for ordinary DISTINCT >* fields. >* >* Actually, it'd be OK for the common prefixes of the two >* lists to match in any order, but implementing that check >* seems like more trouble than it's worth. >*/ > > Does this strike anyone else as being wrong? These seem like reasonable restrictions. There are easy work arounds for the above restrictions, so the restrictions aren't a significant burden. In a world with unlimited developer resources it would be nice to be able to properly handle any order by list. In the real world I doubt that that benefit is worth having a major developer work on this rather than working on any of a number of other things which will result in more benefit. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] WITH clause
Hannu Krosing <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] ORDER BY and DISTINCT ON
Neil Conway <[EMAIL PROTECTED]> writes: > We reject the following query: > > nconway=# create table abc (a int, b int, c int); > CREATE TABLE > nconway=# select distinct on (a) a, b, c from abc order by b, c, a; > ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY > expressions What would you expect to happen here? Do you really want: select distinct on (b,c,a) a,b,c from abc order by b,c,a; or is that you want select * from (select distinct on (a) a,b,c order by a) order by b,c,a; Ie, pick a random record for each a and then sort by b,c? Think of DISTINCT ON as a special form of GROUP BY that instead of doing aggregate just returns the first record. So, like DISTINCT ON, GROUP BY also insists on the user providing the ORDER BY clause. I suppose you could argue postgres could implicitly introduce an extra sort step when the user-provided ORDER BY doesn't match the GROUP BY or DISTINCT ON clause but it seems like the user is probably confused if he really wants a random record and then sort on columns that weren't sorted previous to the DISTINCT ON. -- greg ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Walker/mutator prototype.
Kurt Roeckx <[EMAIL PROTECTED]> writes: > I'm trying to change all the walkers and mutators to have a more > strict prototype. I had to do this with lots of casts. > > I don't really like the idea of having all those generic pointer > types (Node * and void *), but currently see no better way to deal > with it. This code is incorrect. You have to declare the function prototype to match the parameters that will actually be passed, not to match how they'll be used. By casting the function pointers you're confusing the compiler into thinking the variables are already the correct format and don't need to be cast. The correct way to write this type of code is to prototype the functions with void* or Node* or whatever variables will actually be passed, then immediately assign the arguments to a local variable of the correct type. Admittedly I doubt you'll actually run into any problems on any architecture you're likely to see. But the behaviour is undefined in ANSI 89 C. As a side-point, personally I find the profusion of casts at every callpoint to be far uglier, and also more error-prone than the single cast at the beginning of each call-back. -- greg ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] ORDER BY and DISTINCT ON
Greg Stark <[EMAIL PROTECTED]> writes: > Do you really want: > > select distinct on (b,c,a) a,b,c from abc order by b,c,a; > > or is that you want > > select * from (select distinct on (a) a,b,c order by a) order by > b,c,a; If I understand you correctly, I don't think I would expect either. - ORDER BY provides a sort order for the result set - DISTINCT ON specifies a list of expressions, and says: "For each set of rows in the result set for which these expressions are all equal, retain the first row and throw the rest away", where the "first row" is defined by the ORDER BY sort order So I'd expect this query to (a) keep at most one of every distinct 'a' value. When throwing out duplicates, we should keep the row that would come first as specified by the ORDER BY sort order (b) sort the result set by b,c,a ISTM this interpretation is pretty logical, and that the current restriction is made purely for the sake of ease-of-implementation. If that's the case, we should at least document this restriction, and perhaps plan on correcting it in the future. -Neil ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
