Thread: connectby for BYTEA keys
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. 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). -- dave
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
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
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
Index: contrib/tablefunc/tablefunc.c
===================================================================
RCS file: /cvsroot/pgsql-server/contrib/tablefunc/tablefunc.c,v
retrieving revision 1.25
diff -c -r1.25 tablefunc.c
*** contrib/tablefunc/tablefunc.c 2 Oct 2003 03:51:40 -0000 1.25
--- contrib/tablefunc/tablefunc.c 8 Feb 2004 15:36:29 -0000
***************
*** 79,84 ****
--- 79,85 ----
MemoryContext per_query_ctx,
AttInMetadata *attinmeta,
Tuplestorestate *tupstore);
+ static char *quote_literal_cstr(char *rawstr);
typedef struct
{
***************
*** 1319,1341 ****
/* Build initial sql statement */
if (!show_serial)
{
! appendStringInfo(sql, "SELECT %s, %s FROM %s WHERE %s = '%s' AND %s IS NOT NULL AND %s <> %s",
key_fld,
parent_key_fld,
relname,
parent_key_fld,
! start_with,
key_fld, key_fld, parent_key_fld);
serial_column = 0;
}
else
{
! appendStringInfo(sql, "SELECT %s, %s FROM %s WHERE %s = '%s' AND %s IS NOT NULL AND %s <> %s ORDER BY %s",
key_fld,
parent_key_fld,
relname,
parent_key_fld,
! start_with,
key_fld, key_fld, parent_key_fld,
orderby_fld);
serial_column = 1;
--- 1320,1342 ----
/* Build initial sql statement */
if (!show_serial)
{
! appendStringInfo(sql, "SELECT %s, %s FROM %s WHERE %s = %s AND %s IS NOT NULL AND %s <> %s",
key_fld,
parent_key_fld,
relname,
parent_key_fld,
! quote_literal_cstr(start_with),
key_fld, key_fld, parent_key_fld);
serial_column = 0;
}
else
{
! appendStringInfo(sql, "SELECT %s, %s FROM %s WHERE %s = %s AND %s IS NOT NULL AND %s <> %s ORDER BY %s",
key_fld,
parent_key_fld,
relname,
parent_key_fld,
! quote_literal_cstr(start_with),
key_fld, key_fld, parent_key_fld,
orderby_fld);
serial_column = 1;
***************
*** 1690,1693 ****
--- 1691,1712 ----
}
return tupdesc;
+ }
+
+ /*
+ * Return a properly quoted literal value.
+ * Uses quote_literal in quote.c
+ */
+ static char *
+ quote_literal_cstr(char *rawstr)
+ {
+ text *rawstr_text;
+ text *result_text;
+ char *result;
+
+ rawstr_text = DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(rawstr)));
+ result_text = DatumGetTextP(DirectFunctionCall1(quote_literal, PointerGetDatum(rawstr_text)));
+ result = DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(result_text)));
+
+ return result;
}
Joe Conway wrote:
> --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);
Joe,
Thanks for the fix.
--
dave