Date: Sat, 18 Sep 2021 17:20:17 -0500
From: Jaime Casanova <jcasanov@systemguards.com.ec>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: blo.talkto@gmail.com, pgsql-bugs@lists.postgresql.org,
	Peter Eisentraut <peter.eisentraut@enterprisedb.com>
Subject: Re: BUG #17061: Impossible to query the fields of the tuple created
 by SEARCH BREADTH FIRST BY .. SET ..
Message-ID: <20210918222017.GA12334@ahch-to>
References: <17061-dd7f4825b7da3a9d@postgresql.org>
 <972632.1623941414@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <972632.1623941414@sss.pgh.pa.us>
User-Agent: Mutt/1.10.1 (2018-07-13)
Archived-At: 
 <https://www.postgresql.org/message-id/20210918222017.GA12334%40ahch-to>
Precedence: bulk

On Thu, Jun 17, 2021 at 10:50:14AM -0400, Tom Lane wrote:
> [ screwed up the cc somehow the first time, sorry for the duplicate ]
> 
> PG Bug reporting form <noreply@postgresql.org> writes:
> > WITH RECURSIVE mtree(id, name) AS ( ...
> > ) SEARCH BREADTH FIRST BY id SET breadth
> > SELECT (breadth)."*DEPTH*"
> > FROM mtree m;
> > ERROR:  CTE m does not have attribute 3
> 
> Yeah, I get that with "SELECT (breadth).*" as well.  I'm not entirely sure
> what this silly-looking syntax is supposed to mean, but it seems to be
> adding an output column named "breadth" to the CTE.  The error is
> occurring because said column has not been added to the relevant
> CommonTableExpr struct.  Peter?
> 

Just to add fuel to the fire, I just noted that you cannot create a view
based on a recursive CTE using this syntax.

"""
create view v1 as
WITH RECURSIVE mtree(id, name) AS (
   SELECT id, name
     FROM tree
    WHERE id = 1
   UNION ALL
   SELECT t.id, t.name
     FROM tree AS t
          INNER JOIN mtree AS m ON t.parent_id = m.id
) SEARCH BREADTH FIRST BY id SET breadth
SELECT *
FROM mtree m;

ERROR:  column "breadth" has pseudo-type record
"""

-- 
Jaime Casanova
Director de Servicios Profesionales
SystemGuards - Consultores de PostgreSQL