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;
}
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
