pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feed From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: blo.talkto@gmail.com
Subject: BUG #17061: Impossible to query the fields of the tuple created by SEARCH BREADTH FIRST BY .. SET ..
Date: Thu, 17 Jun 2021 11:44:28 +0000
Message-ID: <17061-dd7f4825b7da3a9d@postgresql.org> (raw )
The following bug has been logged on the website:
Bug reference: 17061
Logged by: Benoit Lobréau
Email address: blo.talkto@gmail.com
PostgreSQL version: 14beta1
Operating system: Fedora 33
Description:
Hi,
I try to access the field inside the tuple generated by SEARCH BREADTH
FIRST.
A use case would be to get all the records with depth 3.
I fail to access it and I don't understand the error. I wonder if it works
as intended ?
Here is a test case :
DROP TABLE tree;
CREATE TABLE tree(id int, parent_id int, name text);
ALTER TABLE tree ADD PRIMARY KEY (id);
INSERT INTO tree(id, parent_id, name)
VALUES (1, NULL, 'Albert'),
(2, 1, 'Bob'),
(3, 1, 'Barbara'),
(4, 1, 'Britney'),
(5, 3, 'Clara'),
(6, 3, 'Clement'),
(7, 2, 'Craig'),
(8, 5, 'Debby'),
(9, 5, 'Dave'),
(10, 9, 'Edwin');
-- The following query shows that the fields of the tuple are named:
--
-- row_to_json
-- -----------------------
-- {"*DEPTH*":0,"id":1}
-- {"*DEPTH*":1,"id":2}
-- {"*DEPTH*":1,"id":3}
-- {"*DEPTH*":1,"id":4}
-- {"*DEPTH*":2,"id":5}
-- {"*DEPTH*":2,"id":6}
-- {"*DEPTH*":2,"id":7}
-- {"*DEPTH*":3,"id":8}
-- {"*DEPTH*":3,"id":9}
-- {"*DEPTH*":4,"id":10}
-- (10 rows)
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)
FROM mtree m;
-- 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;
-- 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;
view thread (8+ messages) latest in thread
Message-ID: <17061-dd7f4825b7da3a9d@postgresql.org>
Permalink: ../17061-dd7f4825b7da3a9d@postgresql.org/
Also on: postgresql.org/message-id/17061-dd7f4825b7da3a9d@postgresql.org
copy link · copy postgr.es
reply 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: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, blo.talkto@gmail.com
Subject: Re: BUG #17061: Impossible to query the fields of the tuple created by SEARCH BREADTH FIRST BY .. SET ..
In-Reply-To: <17061-dd7f4825b7da3a9d@postgresql.org>
* 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