Subject: Re: BUG #17061: Impossible to query the fields of the tuple created
 by SEARCH BREADTH FIRST BY .. SET ..
To: blo.talkto@gmail.com, pgsql-bugs@lists.postgresql.org
References: <17061-dd7f4825b7da3a9d@postgresql.org>
From: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
Message-ID: <4a068167-37ed-3d6c-5ec5-c9b03cae84e6@enterprisedb.com>
Date: Tue, 6 Jul 2021 19:56:10 +0200
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:78.0)
 Gecko/20100101 Thunderbird/78.11.0
MIME-Version: 1.0
In-Reply-To: <17061-dd7f4825b7da3a9d@postgresql.org>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/4a068167-37ed-3d6c-5ec5-c9b03cae84e6%40enterprisedb.com>
Precedence: bulk

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).