David Garamond wrote:
> May I request that connectby() supports BYTEA keys too? My keys are GUID
> (16-byte stored in BYTEA). In this case, branch_delim does not make
> sense because the keys should be fixed-length anyway, unless if
> connectby() also wants to support outputing the branch as encoded text.
What exactly doesn't work? I tried a simple test and it seems to work fine:
CREATE TABLE connectby_bytea(keyid bytea, parent_keyid bytea, pos int);
copy connectby_bytea from stdin;
row\\001 \N 0
row\\002 row\\001 0
row\\003 row\\001 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
\.
regression=# SELECT * FROM connectby('connectby_bytea', 'keyid',
'parent_keyid', 'row\\002', 0, '') AS t(keyid bytea, parent_keyid bytea,
level int, branch text);
keyid | parent_keyid | level | branch
---------+--------------+-------+------------------------------
row\002 | | 0 | row\002
row\004 | row\002 | 1 | row\002row\004
row\006 | row\004 | 2 | row\002row\004row\006
row\010 | row\006 | 3 | row\002row\004row\006row\010
row\005 | row\002 | 1 | row\002row\005
row\011 | row\005 | 2 | row\002row\005row\011
(6 rows)
> 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.
Joe