Re: connectby for BYTEA keys

David Garamond wrote:
> Now that I enter as an escaped string, I get this error:
>
> db1=> SELECT * FROM connectby('treeadj1b', 'id', 'parent_id',
> '\\353\\024\\257\\130\\336\\305\\061\\045\\276\\175\\106\\056\\101\\173\\217\\326',
>
> 0) AS t(keyid bytea, parent_keyid bytea, level int);
> ERROR: invalid input syntax for type bytea
>
> However, direct SELECT is fine:

Ah, I see the problem now in the form of a bug in connectby(). The
connectby internal sql statement was using an unescaped string to do its
recursive join. The direct select is fine because the escaped string
above is not actually the culprit. Somewhere in your chain of data you
have a '\\134'::bytea character. To illustrate:

CREATE TABLE connectby_bytea(keyid bytea, parent_keyid bytea, pos int);

copy connectby_bytea from stdin;
row\\134 \N 0
row\\002 row\\134 0
row\\003 row\\134 0
row\\004 row\\002 1
row\\005 row\\002 0
row\\006 row\\004 0
row\\007 row\\003 0
row\\010 row\\006 0
row\\011 row\\005 0
\.

--without patch
regression=# SELECT * FROM connectby('connectby_bytea', 'keyid',
'parent_keyid', 'row\\134', 0, '') AS t(keyid bytea, parent_keyid bytea,
level int, branch text);
ERROR: invalid input syntax for type bytea

--with attached patch
regression=# SELECT * FROM connectby('connectby_bytea', 'keyid',
'parent_keyid', 'row\\134', 0, '') AS t(keyid bytea, parent_keyid bytea,
level int, branch text);
keyid | parent_keyid | level | branch
---------+--------------+-------+-------------------------------------
row\\ | | 0 | row\134
row\002 | row\\ | 1 | row\134row\002
row\004 | row\002 | 2 | row\134row\002row\004
row\006 | row\004 | 3 | row\134row\002row\004row\006
row\010 | row\006 | 4 | row\134row\002row\004row\006row\010
row\005 | row\002 | 2 | row\134row\002row\005
row\011 | row\005 | 3 | row\134row\002row\005row\011
row\003 | row\\ | 1 | row\134row\003
row\007 | row\003 | 2 | row\134row\003row\007
(9 rows)

HTH,

Joe