connectby for BYTEA keys

[David Garamond]

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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Joe Conway]

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[David Garamond]

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\\17 5\\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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Joe Conway]

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\\17 5\\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;
}
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[David Garamond]

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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings