473,722 Members | 2,243 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 3675
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
2884
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 - that's all. The upload part works fine... I'm having problems getting out what I put in. The file...
0
2343
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
6656
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
22051
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
2381
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
2785
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
4453
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 broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
0
1507
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 compare the after-fetched-value for two BYTEA datatype in pgsql's function? CREATE OR REPLACE...
4
11435
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
8860
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8738
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 synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9376
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. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9234
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9150
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9086
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 choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8028
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6678
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 instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
3
2146
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 effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.