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

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