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 <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. +