Re: Error check always bypassed in tablefunc.c - Mailing list pgsql-hackers
From | Michael Paquier |
---|---|
Subject | Re: Error check always bypassed in tablefunc.c |
Date | |
Msg-id | CAB7nPqRrVcnGF1fr3Pk8rZvi7JCg+9Gqs02b58jL1YqRzhkftQ@mail.gmail.com Whole thread Raw |
In response to | Re: Error check always bypassed in tablefunc.c (Michael Paquier <michael.paquier@gmail.com>) |
Responses |
Re: Error check always bypassed in tablefunc.c
|
List | pgsql-hackers |
On Tue, Jan 20, 2015 at 8:47 AM, Michael Paquier <michael.paquier@gmail.com> wrote: > On Mon, Jan 19, 2015 at 11:06 PM, Joe Conway <mail@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 | 0row3 | row1 | 0row4 | row2 | 1row5 | row2 | 0row6 | row4 | 0row7 | row3 | 0row8 | row6 | 0row9 | row5 | 0row1 | 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 | row1row2 | row1 | 1 | row1~row2row4 | row2 | 2 | row1~row2~row4row6 | row4 | 3 | row1~row2~row4~row6row5 | row2 | 2 | row1~row2~row5row9 | row5 | 3 | row1~row2~row5~row9row3 | row1 | 1 | row1~row3row7 | 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.keyidAS 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_listFROM connectby_tree ORDER BY ct_full_list;keyid | parent_keyid | level | ct_full_list -------+--------------+-------+---------------------row1 | null | 0 | row1row2 | row1 | 1 | row1~row2row4 | row2 | 2 | row1~row2~row4row6 | row4 | 3 | row1~row2~row4~row6row5 | row2 | 2 | row1~row2~row5row9 | row5 | 3 | row1~row2~row5~row9row3 | row1 | 1 | row1~row3row7 | 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
pgsql-hackers by date: