Thread: 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
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 <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Gregory Stark <stark@enterprisedb.com> 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
On Mon, Apr 09, 2007 at 06:43:08PM -0400, Tom Lane wrote: > Gregory Stark <stark@enterprisedb.com> 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 <david@fetter.org> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate
David Fetter <david@fetter.org> 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
On Mon, Apr 09, 2007 at 09:08:31PM -0400, Tom Lane wrote: > David Fetter <david@fetter.org> 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 <david@fetter.org> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate
"David Fetter" <david@fetter.org> 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
This has been saved for the 8.4 release:
http://momjian.postgresql.org/cgi-bin/pgpatches_hold
---------------------------------------------------------------------------
Gregory Stark wrote:
> "David Fetter" <david@fetter.org> 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 <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
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 <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +