Re: connectby for BYTEA keys

Joe Conway wrote:
> regression=# SELECT * FROM connectby('connectby_bytea', 'keyid',
> 'parent_keyid', 'row\\002', 0, '') AS t(keyid bytea, parent_keyid bytea,
> level int, branch text);

Oh, I was specifying the fourth argument in BYTEA (decode('hex...','hex')).

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:

db1=> select encode(id,'hex'),encode(parent_id,'hex') from treeadj1b
where
id='\\353\\024\\257\\130\\336\\305\\061\\045\\276\\175\\106\\056\\101\\173\\217\\326';
               encode              |              encode
----------------------------------+----------------------------------
  eb14af58dec53125be7d462e417b8fd6 | 7c10111b13693b2bc9eea87b00914883
(1 row)

This is 7.4.1.

>> Btw, is recursive join (CONNECT BY ...) in SQL standard? (I have a
>> copy of the 1992 draft and it doesn't seem to be there).
>
> I believe it's covered in SQL99, but it is not called CONNECT BY --
> that's an Oracle-ism.

Thanks. I've looked up the DB2 documentation and saw WITH ... (SELECT
... UNION ALL ... SELECT) ... more verbose than CONNECT BY.

--
dave