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 byteaHowever, 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
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
