Thread: recursive view syntax

I noticed we don't implement the recursive view syntax, even though it's
part of the standard SQL feature set for recursive queries.  Here is a
patch to add that.  It basically converts

CREATE RECURSIVE VIEW name (columns) AS SELECT ...;

to

CREATE VIEW name AS WITH RECURSIVE name (columns) AS (SELECT ...) SELECT
columns FROM name;

Peter Eisentraut <peter_e@gmx.net> writes:
> I noticed we don't implement the recursive view syntax, even though it's
> part of the standard SQL feature set for recursive queries.  Here is a
> patch to add that.

Can't you simplify that by using "SELECT * FROM name"?
        regards, tom lane

On Tue, 2012-11-13 at 23:44 -0500, Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
> > I noticed we don't implement the recursive view syntax, even though it's
> > part of the standard SQL feature set for recursive queries.  Here is a
> > patch to add that.
> 
> Can't you simplify that by using "SELECT * FROM name"?

You mean in the expansion?

Maybe, but SELECT * is perhaps something best avoided because of unclear
side effects.

At 2012-11-13 23:32:15 -0500, peter_e@gmx.net wrote:
>
> I noticed we don't implement the recursive view syntax, even though
> it's part of the standard SQL feature set for recursive queries.
> Here is a patch to add that.  It basically converts
> 
> CREATE RECURSIVE VIEW name (columns) AS SELECT ...;
> 
> to
> 
> CREATE VIEW name AS WITH RECURSIVE name (columns) AS (SELECT ...)
> SELECT columns FROM name;

Hi.

Sorry I took so long to post a review of this patch. I'm afraid it
tempted me to digress into figuring out how WITH RECURSIVE works. :-)

I don't have much to say about the patch, though: it applies to HEAD and
builds cleanly, passes "make check", and has suitable documentation with
an example. The code looks fine.

I'm marking it ready for committer.

-- Abhijit

* Peter Eisentraut (peter_e@gmx.net) wrote:
> I noticed we don't implement the recursive view syntax, even though it's
> part of the standard SQL feature set for recursive queries.  Here is a
> patch to add that.  It basically converts
>
> CREATE RECURSIVE VIEW name (columns) AS SELECT ...;
>
> to
>
> CREATE VIEW name AS WITH RECURSIVE name (columns) AS (SELECT ...) SELECT
> columns FROM name;

I've done another review of this patch and it looks pretty good to me.
My only complaint is that there isn't a single comment inside
makeRecursiveViewSelect().

One other thought is- I'm guessing this isn't going to work:

CREATE RECURSIVE VIEW name (columns) AS WITH ... SELECT ...;

Does the spec explicitly allow or disallow that?  Should we provide any
comments about it?
Thanks,
    Stephen

On Fri, 2013-01-18 at 10:00 -0500, Stephen Frost wrote:
> I've done another review of this patch and it looks pretty good to me.
> My only complaint is that there isn't a single comment inside
> makeRecursiveViewSelect().

Added some of that and committed.

> One other thought is- I'm guessing this isn't going to work:
> 
> CREATE RECURSIVE VIEW name (columns) AS WITH ... SELECT ...;
> 
> Does the spec explicitly allow or disallow that?  Should we provide any
> comments about it?

That works fine, AFAICT.  It just becomes another level of WITH.