Re: [PATCHES] non-recursive WITH clause support
This has been saved for the 8.4 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold --- Gregory Stark wrote: > > Here's an updated patch that fixes the bug I had. This is now functional basic > non-recursive WITH clause support. > > http://community.enterprisedb.com/recursive/with-pg82stable-v2.patch.gz > > It's a pretty short simple patch as is; it just directly inlines any WITH > clauses as if they had been written as subqueries. We'll have to do something > much more clever to get recursive queries to work but for non-recursive > queries that's sufficient. > > Example: > > postgres=# with a as (select 1 as x) select * from (select * from a) as x; > x > --- > 1 > (1 row) > > > > > -- > Gregory Stark > EnterpriseDB http://www.enterprisedb.com > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] non-recursive WITH clause support
This has been saved for the 8.4 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold --- Gregory Stark wrote: > "David Fetter" <[EMAIL PROTECTED]> writes: > > > On Mon, Apr 09, 2007 at 06:43:08PM -0400, Tom Lane wrote: > > > >> So this does not really add any new functionality, it's just variant > >> syntax for something you can do about as easily without it, right? > > > > Not totally as easily. For example, you can do some kinds of > > aggregation with a few fewer keystrokes. > > I think "fewer keystrokes" is exactly what Tom meant by a variant syntax > without new functionality. That's an accurate description. > > I suppose it depends in part on how important we think it is to add variant > syntaxes just because they're blessed by the ANSI standard. If this were a > syntax we were creating just for our user's convenience it would be a pretty > weak justification for an incompatibility. But if there are users who expect > this syntax to work because it's standard then it could be considered an > omission in our standards compliance. > > I'm actually not too sure what the answer is. I hadn't heard of it before the > discussion about recursive queries myself. > > -- > Gregory Stark > EnterpriseDB http://www.enterprisedb.com > > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] non-recursive WITH clause support
"David Fetter" <[EMAIL PROTECTED]> writes: > On Mon, Apr 09, 2007 at 06:43:08PM -0400, Tom Lane wrote: > >> So this does not really add any new functionality, it's just variant >> syntax for something you can do about as easily without it, right? > > Not totally as easily. For example, you can do some kinds of > aggregation with a few fewer keystrokes. I think "fewer keystrokes" is exactly what Tom meant by a variant syntax without new functionality. That's an accurate description. I suppose it depends in part on how important we think it is to add variant syntaxes just because they're blessed by the ANSI standard. If this were a syntax we were creating just for our user's convenience it would be a pretty weak justification for an incompatibility. But if there are users who expect this syntax to work because it's standard then it could be considered an omission in our standards compliance. I'm actually not too sure what the answer is. I hadn't heard of it before the discussion about recursive queries myself. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] non-recursive WITH clause support
On Mon, Apr 09, 2007 at 09:08:31PM -0400, Tom Lane wrote: > David Fetter <[EMAIL PROTECTED]> writes: > > On Mon, Apr 09, 2007 at 06:43:08PM -0400, Tom Lane wrote: > >> So this does not really add any new functionality, it's just variant > >> syntax for something you can do about as easily without it, right? > > > Not totally as easily. For example, you can do some kinds of > > aggregation with a few fewer keystrokes. > > Example please? It's not obvious to me what this does that wouldn't be > exactly isomorphic to creating a temporary view. It would be isomorphic, but happens automagically for the scope of the query instead of being three queries. Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] non-recursive WITH clause support
David Fetter <[EMAIL PROTECTED]> writes: > On Mon, Apr 09, 2007 at 06:43:08PM -0400, Tom Lane wrote: >> So this does not really add any new functionality, it's just variant >> syntax for something you can do about as easily without it, right? > Not totally as easily. For example, you can do some kinds of > aggregation with a few fewer keystrokes. Example please? It's not obvious to me what this does that wouldn't be exactly isomorphic to creating a temporary view. regards, tom lane ---(end of broadcast)--- TIP 1: 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: [PATCHES] non-recursive WITH clause support
On Mon, Apr 09, 2007 at 06:43:08PM -0400, Tom Lane wrote: > Gregory Stark <[EMAIL PROTECTED]> writes: > > Here's an updated patch that fixes the bug I had. This is now functional > > basic > > non-recursive WITH clause support. > > http://community.enterprisedb.com/recursive/with-pg82stable-v2.patch.gz > > It's a pretty short simple patch as is; it just directly inlines any WITH > > clauses as if they had been written as subqueries. > > So this does not really add any new functionality, it's just variant > syntax for something you can do about as easily without it, right? Not totally as easily. For example, you can do some kinds of aggregation with a few fewer keystrokes. > The downside that I see is is that it changes WITH and SET into fully > reserved words, which no doubt will break a few people's applications. Really? > While we're probably going to have to do that eventually, I'd like > to be able to point to some non-negligible benefit resulting from > the change when we do it. > > So my inclination is to not apply this in its current form, but to > wait for the full recursive-WITH feature before taking the > compatibility hit. Is there some way to poll people for uses of WITH and SET in places they shouldn't be? Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] non-recursive WITH clause support
Gregory Stark <[EMAIL PROTECTED]> writes: > Here's an updated patch that fixes the bug I had. This is now functional basic > non-recursive WITH clause support. > http://community.enterprisedb.com/recursive/with-pg82stable-v2.patch.gz > It's a pretty short simple patch as is; it just directly inlines any WITH > clauses as if they had been written as subqueries. So this does not really add any new functionality, it's just variant syntax for something you can do about as easily without it, right? The downside that I see is is that it changes WITH and SET into fully reserved words, which no doubt will break a few people's applications. While we're probably going to have to do that eventually, I'd like to be able to point to some non-negligible benefit resulting from the change when we do it. So my inclination is to not apply this in its current form, but to wait for the full recursive-WITH feature before taking the compatibility hit. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] non-recursive WITH clause support
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- Gregory Stark wrote: > > Here's an updated patch that fixes the bug I had. This is now functional basic > non-recursive WITH clause support. > > http://community.enterprisedb.com/recursive/with-pg82stable-v2.patch.gz > > It's a pretty short simple patch as is; it just directly inlines any WITH > clauses as if they had been written as subqueries. We'll have to do something > much more clever to get recursive queries to work but for non-recursive > queries that's sufficient. > > Example: > > postgres=# with a as (select 1 as x) select * from (select * from a) as x; > x > --- > 1 > (1 row) > > > > > -- > Gregory Stark > EnterpriseDB http://www.enterprisedb.com > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[PATCHES] non-recursive WITH clause support
Here's an updated patch that fixes the bug I had. This is now functional basic non-recursive WITH clause support. http://community.enterprisedb.com/recursive/with-pg82stable-v2.patch.gz It's a pretty short simple patch as is; it just directly inlines any WITH clauses as if they had been written as subqueries. We'll have to do something much more clever to get recursive queries to work but for non-recursive queries that's sufficient. Example: postgres=# with a as (select 1 as x) select * from (select * from a) as x; x --- 1 (1 row) -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org