Re: connectby for BYTEA keys - Mailing list pgsql-general

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;
  }