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 <t...@sss.pgh.pa.us> wrote: > Adam Mackler <adammack...@gmail.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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
