Re: BUG #17061: Impossible to query the fields of the tuple created by SEARCH BREADTH FIRST BY .. SET ..

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