473,554 Members | 3,240 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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
---------------------------(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

Nov 22 '05 #1
4 3641
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('conn ectby_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\004r ow\006
row\010 | row\006 | 3 | row\002row\004r ow\006row\010
row\005 | row\002 | 1 | row\002row\005
row\011 | row\005 | 2 | row\002row\005r ow\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

Nov 22 '05 #2
Joe Conway wrote:
regression=# SELECT * FROM connectby('conn ectby_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('tree adj1b', 'id', 'parent_id',
'\\353\\024\\25 7\\130\\336\\30 5\\061\\045\\27 6\\175\\106\\05 6\\101\\173\\21 7\\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
----------------------------------+----------------------------------
eb14af58dec5312 5be7d462e417b8f d6 | 7c10111b13693b2 bc9eea87b009148 83
(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*******@postg resql.org

Nov 22 '05 #3
David Garamond wrote:
Now that I enter as an escaped string, I get this error:

db1=> SELECT * FROM connectby('tree adj1b', 'id', 'parent_id',
'\\353\\024\\25 7\\130\\336\\30 5\\061\\045\\27 6\\175\\106\\05 6\\101\\173\\21 7\\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('conn ectby_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('conn ectby_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\002r ow\004
row\006 | row\004 | 3 | row\134row\002r ow\004row\006
row\010 | row\006 | 4 | row\134row\002r ow\004row\006ro w\010
row\005 | row\002 | 2 | row\134row\002r ow\005
row\011 | row\005 | 3 | row\134row\002r ow\005row\011
row\003 | row\\ | 1 | row\134row\003
row\007 | row\003 | 2 | row\134row\003r ow\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)
{
! appendStringInf o(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
{
! appendStringInf o(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)
{
! appendStringInf o(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_c str(start_with) ,
key_fld, key_fld, parent_key_fld) ;
serial_column = 0;
}
else
{
! appendStringInf o(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_c str(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_c str(char *rawstr)
+ {
+ text *rawstr_text;
+ text *result_text;
+ char *result;
+
+ rawstr_text = DatumGetTextP(D irectFunctionCa ll1(textin, CStringGetDatum (rawstr)));
+ result_text = DatumGetTextP(D irectFunctionCa ll1(quote_liter al, PointerGetDatum (rawstr_text))) ;
+ result = DatumGetCString (DirectFunction Call1(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

Nov 22 '05 #4
Joe Conway wrote:
--without patch
regression=# SELECT * FROM connectby('conn ectby_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('conn ectby_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

Nov 22 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
2865
by: mPath Records | last post by:
Hello, I hope someone can help me with this problem - I'm getting nowhere fast and have been working on it for a week. All I'm trying to do is upload an image, store the image in a bytea column of a postrgresql database, pull the image out of the datbase, and verify that the image is still the same and it can be viewed by a browser -...
0
2322
by: Sergei Levchenko | last post by:
hi I have menu table: id | integer | not null default nextval('public.menu_id_seq'::text) parent_id | integer | description | text | I do select: test=> SELECT * FROM connectby('menu','id','parent_id','2',0,'~') t(id
1
6576
by: Baldur Norddahl | last post by:
Hi, I need to do something like this: select * from sms where message ilike 'foo%'; Message is a bytea field with UTF-8 content. The charset is actually not important for me, it would be enough if it could just treat it as us ascii. The statement does not work because postgresql can not convert bytea to text. But this works:
2
21864
by: Alvar Freude | last post by:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi, I want to change a column from text to bytea; since it seems that alter table can't change the column type, i have to add a temporary column and copy the data from the old one to the new, delete the old and rename the new.
0
2362
by: Alvar Freude | last post by:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi, the following I posted already on pgsql-bugs -- perhaps someone has a good workaround or fix or can say me that I'm wrong? There seems to be a bug in handling bytea columns with index and the like-operator.
7
2763
by: C G | last post by:
Dear All, What's the best way to store jpgs in postgresql to use in a web page? I tried to use large objects, but how would you extract them from a table to be viewed in a web-page without having to write them to a scratch file somewhere first? Thanks
7
4439
by: Dennis Gearon | last post by:
when bytea, text, and varchar(no limit entered) columns are used, do they ALWAYS use an extra table/file? Or do they only do it after a certain size of input? Also, if I wanted to put a *.pdf file in a bytea column, what functions do I use to escape any characters in it? ---------------------------(end of...
0
1492
by: Peter Wang | last post by:
I have a function and am using cursor. All the columns I select in the cursor clause are BYTEA datatype. I need to compare the after-fetch-value for two BYTEA columns which is temp2 and temp3 shown as below. I don't think I can compare it because there is no record in temp table which I use for debug purpose. Does anyone know how to...
4
11414
by: Jerry LeVan | last post by:
Hi, I am adding image and large object support in my Cocoa postgresql browser. Are there going to be any enhanced bytea support functions coming along? It seems sorta silly to have to write customized C code to import a file into a bytea field.
0
7507
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7783
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
7873
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5424
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5143
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3546
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
2007
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1115
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
825
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.