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 ..
Date: Sat, 18 Sep 2021 17:20:17 -0500
Message-ID: <20210918222017.GA12334@ahch-to> (raw)
In-Reply-To: <972632.1623941414@sss.pgh.pa.us>
References: <17061-dd7f4825b7da3a9d@postgresql.org>
<972632.1623941414@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: jcasanov@systemguards.com.ec, tgl@sss.pgh.pa.us, peter.eisentraut@enterprisedb.com
Subject: Re: BUG #17061: Impossible to query the fields of the tuple created by SEARCH BREADTH FIRST BY .. SET ..
In-Reply-To: <20210918222017.GA12334@ahch-to>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox