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