Re: Possible Bug in 9.2beta3

Are you asking me or the other experts? I had not even heard of a
common table expression a few weeks ago, so I doubt I'm qualified to
opine what ought to be possible; I just know what I'm trying to do.
Basically I've got a recursive CTE with rows, some of which have
information that I want to be split between two rows in a result
table. I couldn't see a way to do that except to "copy" the working
table of the outer recursive CTE to an intermediary inner CTE, so that
then I could refer to that intermediary CTE twice, once for each row
that I want to result from one row in the outer recursive CTE. If
that makes sense.

It wouldn't surprise me at all to learn of a better way to do what I
want, but to answer your question: on the one hand yes, I was
obviously trying to "get around" a limitation that was expressed to me
in a specific error message about only referring to a recursive CTE
working-table variable in one location. On the other hand, I don't
see why I shouldn't be able to look at that working table more than
once on each iteration.

I imagine the "optimal" answer requires more knowledge of what's going
on under the covers than I have, as well as familiarity with the SQL
standard and more experience & expertise than I have in how to solve
problems using SQL. As far as facility with SQL, I'm still pretty
much stumbling in the dark and learning by trial-and-error and reading
whatever I can find that seems on point, so, again, my current idea of
what ought to be possible is based on a rather uninformed foundation.

Adam Mackler

On Wed, Aug 15, 2012 at 12:53 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Adam Mackler <AdamMackler(at)gmail(dot)com> writes:
>> WITH RECURSIVE
>> tab(id_key,link) AS ( VALUES (1,17), (2,17), (3,17), (4,17), (6,17), (5,17) ),
>> iter (id_key, row_type, link) AS (
>> SELECT 0, 'base', 17
>> UNION(
>> WITH remaining(id_key, row_type, link, min) AS (
>> SELECT tab.id_key, 'true'::text, iter.link, MIN(tab.id_key) OVER ()
>> FROM tab INNER JOIN iter USING (link)
>> WHERE tab.id_key > iter.id_key
>> ),
>> first_remaining AS (
>> SELECT id_key, row_type, link
>> FROM remaining
>> WHERE id_key=min
>> ),
>> effect AS (
>> SELECT tab.id_key, 'new'::text, tab.link
>> FROM first_remaining e INNER JOIN tab ON e.id_key=tab.id_key
>> /* Try changing this WHERE clause to other false expressions */
>> WHERE e.row_type='false'
>> )
>> SELECT * FROM first_remaining
>> /* Try uncommenting the next line */
>> --UNION SELECT * FROM effect
>> )
>> )
>> SELECT DISTINCT * FROM iter
>
> Right offhand I'm inclined to think that the reference to "iter"
> inside the first sub-WITH ought to be disallowed. I don't recall
> the exact rules about where a recursive reference can appear, but
> it sure doesn't seem like that ought to be OK, does it?
>
> regards, tom lane

--
Adam Mackler