From: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
To: blo.talkto@gmail.com
To: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17061: Impossible to query the fields of the tuple created by SEARCH BREADTH FIRST BY .. SET ..
Date: Tue, 6 Jul 2021 19:56:10 +0200
Message-ID: <4a068167-37ed-3d6c-5ec5-c9b03cae84e6@enterprisedb.com> (raw)
In-Reply-To: <17061-dd7f4825b7da3a9d@postgresql.org>
References: <17061-dd7f4825b7da3a9d@postgresql.org>
On 17.06.21 13:44, PG Bug reporting form wrote:
> -- So I try to get the "*DEPTH*" column but it fails with (I get the same
> error with (breadth).id) :
> --
> -- ERROR: CTE m does not have attribute 3
>
> 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 (breadth)."*DEPTH*"
> FROM mtree m;
We could put a workaround for this into expandRecordVariable() to handle
the extra columns similar to what we have in markTargetListOrigin(), but
it's still not going to work, because then you'll get
ERROR: record type has not been registered
This is similar to what you get now if you write something like
...
) SEARCH DEPTH FIRST BY id SET seq
SELECT (seq[1]).*
FROM mtree m;
It's not really meant to be used that way. I'm not sure whether it's
worth spending extra effort on.
> -- The following works but feels a little hacky
>
> 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 row_to_json(breadth) -> '*DEPTH*'
> FROM mtree m;
This works because it does not require type information for the fields
in the row you're digging into (which is what the "registering" of the
record type would accomplish).
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: peter.eisentraut@enterprisedb.com, blo.talkto@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17061: Impossible to query the fields of the tuple created by SEARCH BREADTH FIRST BY .. SET ..
In-Reply-To: <4a068167-37ed-3d6c-5ec5-c9b03cae84e6@enterprisedb.com>
* 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