On Tue, Jan 20, 2015 at 8:47 AM, Michael Paquier
<michael.paqu...@gmail.com> wrote:
> On Mon, Jan 19, 2015 at 11:06 PM, Joe Conway <m...@joeconway.com> wrote:
>> -----BEGIN PGP SIGNED MESSAGE-----
>> Hash: SHA1
>>
>> On 01/19/2015 08:16 AM, Alvaro Herrera wrote:
>>> Haven't looked at this patch, but I wonder if it would be better
>>> to replace the innards of connectby with a rewrite of the query to
>>> use standard WITH queries. Maybe we can remove a couple hundred
>>> lines from tablefunc.c?
>>
>> Seems like a good idea -- connectby is really obsolete for quite a
>> while now other than as an SRF example. I guess we only keep it around
>> for backwards compatibility?
> For master, yes we could brush up things a bit. Now do we really do
> the same for back-branches? I would think that the answer there is
> something close to the patch I sent.
So, using a WITH RECURSIVE, here is a query equivalent to what connectby does:
=# SELECT * FROM connectby_text;
keyid | parent_keyid | pos
-------+--------------+-----
row2 | row1 | 0
row3 | row1 | 0
row4 | row2 | 1
row5 | row2 | 0
row6 | row4 | 0
row7 | row3 | 0
row8 | row6 | 0
row9 | row5 | 0
row1 | null | 0
(9 rows)
=# SELECT * FROM
connectby('connectby_text', 'keyid', 'parent_keyid', 'row1', 3, '~') AS
t(keyid text, parent_keyid text, level int, branch text);
keyid | parent_keyid | level | branch
-------+--------------+-------+---------------------
row1 | null | 0 | row1
row2 | row1 | 1 | row1~row2
row4 | row2 | 2 | row1~row2~row4
row6 | row4 | 3 | row1~row2~row4~row6
row5 | row2 | 2 | row1~row2~row5
row9 | row5 | 3 | row1~row2~row5~row9
row3 | row1 | 1 | row1~row3
row7 | row3 | 2 | row1~row3~row7
(8 rows)
=# WITH RECURSIVE connectby_tree AS
(
SELECT keyid, 0::int AS level, parent_keyid, keyid as
ct_full_list -- root portion
FROM connectby_text
WHERE keyid = 'row1' -- start point
UNION ALL
SELECT ctext.keyid,
(ctree.level + 1)::int AS level,
ctext.parent_keyid,
CAST(ctree.ct_full_list || '~' || ctext.keyid AS text) AS ct_full_list
FROM connectby_text AS ctext
INNER JOIN connectby_tree AS ctree
ON (ctext.parent_keyid = ctree.keyid) -- connect by
WHERE ctree.level <= 2 -- limit of level
)
SELECT keyid, parent_keyid, level, ct_full_list
FROM connectby_tree ORDER BY ct_full_list;
keyid | parent_keyid | level | ct_full_list
-------+--------------+-------+---------------------
row1 | null | 0 | row1
row2 | row1 | 1 | row1~row2
row4 | row2 | 2 | row1~row2~row4
row6 | row4 | 3 | row1~row2~row4~row6
row5 | row2 | 2 | row1~row2~row5
row9 | row5 | 3 | row1~row2~row5~row9
row3 | row1 | 1 | row1~row3
row7 | row3 | 2 | row1~row3~row7
(8 rows)
Using that we got a couple of options:
- Parametrize this query in some set of plpgsql functions and dump
tablefunc to 1.1
- Integrate directly this query in the existing C code and use SPI,
without dumping tablefunc.
Thoughts?
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
