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(at)postgresql(dot)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