Re: Error check always bypassed in tablefunc.c - Mailing 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