Bytea/Base64 encoders for libpq - interested?
Hi!
Please find attached some very simple encoders/decoders for bytea and base64.
Bytea encoder is very picky about what it leaves unescaped - basically the
base64
char set ;-)
Since this seems to be a very poorly documented but much asked-for thing, I
thought
you would maybe like to add this code to libpq (so that everyone benefits).
I'm aware that function renames might be necessary, though.
If you like, I could make the code fit into libpq, and send diffs.
Any comments/interests?
Greetings,
Joerg
Attachments:
On Tue, Aug 28, 2001 at 11:07:32AM +0200, Joerg Hessdoerfer wrote:
Hi!
Please find attached some very simple encoders/decoders for bytea and base64.
Bytea encoder is very picky about what it leaves unescaped - basically the
base64
char set ;-)Since this seems to be a very poorly documented but much asked-for thing, I
thought
you would maybe like to add this code to libpq (so that everyone benefits).I'm aware that function renames might be necessary, though.
If you like, I could make the code fit into libpq, and send diffs.Any comments/interests?
What implement base64 PostgreSQL datetype that use externaly base64 and
internaly same things as bytea. It prevent FE and parser problems with
"bad" chars and internaly for data storage save less space than text
with base64. Of course it doesn't solve a problem with encoding/decoding
data in your application to/from base64. May be implement for this
datetype cast to/from bytea too.
SELECT my_bytea::base64 FROM foo;
INSERT INTO foo (my_bytea) VALUES ('some_base64_string'::bytea);
And you can still fetch all data directly in batea by binary cursor.
Comments?
Karel
--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/
C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
At 11:55 AM 28-08-2001 +0200, Karel Zak wrote:
What implement base64 PostgreSQL datetype that use externaly base64 and
internaly same things as bytea. It prevent FE and parser problems with
"bad" chars and internaly for data storage save less space than text
with base64. Of course it doesn't solve a problem with encoding/decoding
data in your application to/from base64. May be implement for this
datetype cast to/from bytea too.SELECT my_bytea::base64 FROM foo;
INSERT INTO foo (my_bytea) VALUES ('some_base64_string'::bytea);
And you can still fetch all data directly in batea by binary cursor.
Comments?
Sounds good to me. Even better if the base64 parser is bulletproof and
tolerant of junk. That way base64 email attachments may not even need to be
processed much - just filter a bit and shove it in :).
But shouldn't there be a ::base64 somewhere in the insert statement?
Cheerio,
Link.
At 11:55 AM 28-08-2001 +0200, Karel Zak wrote:
What implement base64 PostgreSQL datetype that use externaly base64 and
internaly same things as bytea. It prevent FE and parser problems with
Another point:
I have no problems with base64[1]OK, I can't convert base64 to ASCII mentally yet. But I don't think that should really a factor.. However I was thinking that it might be
far easier for the C/C++/Java (and other low level languages) bunch to do
hexadecimal. e.g. zero zero for null, zero A for line feed.
It expands things in the input/output stream, but it might be worth some
consideration. Simplicity, cpu usage etc.
Cheerio,
Link.
[1]: OK, I can't convert base64 to ASCII mentally yet. But I don't think that should really a factor.
that should really a factor.
Where did we leave this?
On Tue, Aug 28, 2001 at 11:07:32AM +0200, Joerg Hessdoerfer wrote:
Hi!
Please find attached some very simple encoders/decoders for bytea and base64.
Bytea encoder is very picky about what it leaves unescaped - basically the
base64
char set ;-)Since this seems to be a very poorly documented but much asked-for thing, I
thought
you would maybe like to add this code to libpq (so that everyone benefits).I'm aware that function renames might be necessary, though.
If you like, I could make the code fit into libpq, and send diffs.Any comments/interests?
What implement base64 PostgreSQL datetype that use externaly base64 and
internaly same things as bytea. It prevent FE and parser problems with
"bad" chars and internaly for data storage save less space than text
with base64. Of course it doesn't solve a problem with encoding/decoding
data in your application to/from base64. May be implement for this
datetype cast to/from bytea too.SELECT my_bytea::base64 FROM foo;
INSERT INTO foo (my_bytea) VALUES ('some_base64_string'::bytea);
And you can still fetch all data directly in batea by binary cursor.
Comments?
Karel
--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
Where did we leave this?
I don't think adding a datatype just to provide base64 encoding is
a wise approach. The overhead of a new datatype (in the sense of
providing operators/functions for it) will be much more than the
benefit. I think providing encode/decode functions is sufficient...
and we have those already, don't we?
regards, tom lane
Bruce Momjian <pgman@candle.pha.pa.us> writes:
Where did we leave this?
I don't think adding a datatype just to provide base64 encoding is
a wise approach. The overhead of a new datatype (in the sense of
providing operators/functions for it) will be much more than the
benefit. I think providing encode/decode functions is sufficient...
and we have those already, don't we?
Agreed.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
I don't think adding a datatype just to provide base64 encoding is
a wise approach. The overhead of a new datatype (in the sense of
providing operators/functions for it) will be much more than the
benefit. I think providing encode/decode functions is sufficient...
and we have those already, don't we?
It might be nice to have a PQbyteaEscape or some such function available in
the libpq client library so that arbitrary binary could be escaped on the
client side and used in a sql statement. I actually wrote this already as an
addition to the PHP PostgreSQL extension, but it would make more sense, now
that I think about it, for it to be in libpq and called from PHP (or
whatever). Comments?
On a related note, are there any other bytea functions we should have in the
backend before freezing for 7.2? I was thinking it would be nice to have a
way to cast bytea into text and vice-versa, so that the normal text
functions could be used for things like LIKE and concatenation. Any interest
in this? If so, any guidance WRT how it should be implemented?
-- Joe
I don't think adding a datatype just to provide base64 encoding is
a wise approach. The overhead of a new datatype (in the sense of
providing operators/functions for it) will be much more than the
benefit. I think providing encode/decode functions is sufficient...
and we have those already, don't we?It might be nice to have a PQbyteaEscape or some such function available in
the libpq client library so that arbitrary binary could be escaped on the
client side and used in a sql statement. I actually wrote this already as an
addition to the PHP PostgreSQL extension, but it would make more sense, now
that I think about it, for it to be in libpq and called from PHP (or
whatever). Comments?
Good idea. I will commit the non-bytea escape in a day and you can base
a bytea one on that. You will have to pass in the length of the field
because of course it is not null terminated.
On a related note, are there any other bytea functions we should have in the
backend before freezing for 7.2? I was thinking it would be nice to have a
way to cast bytea into text and vice-versa, so that the normal text
functions could be used for things like LIKE and concatenation. Any interest
in this? If so, any guidance WRT how it should be implemented?
I can't see why you can't do that. The only problem is passing a \0
(null byte) back to the client.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026"Joe Conway" <joseph.conway@home.com> writes:
I was thinking it would be nice to have a
way to cast bytea into text and vice-versa,
How will you handle a null byte in bytea data? Transforming it directly
into an embedded null in a text object is NOT an acceptable answer,
because too many of the text functions will misbehave on such data.
regards, tom lane
It might be nice to have a PQbyteaEscape or some such function available
in
the libpq client library so that arbitrary binary could be escaped on
the
client side and used in a sql statement. I actually wrote this already
as an
addition to the PHP PostgreSQL extension, but it would make more sense,
now
that I think about it, for it to be in libpq and called from PHP (or
whatever). Comments?Good idea. I will commit the non-bytea escape in a day and you can base
a bytea one on that. You will have to pass in the length of the field
because of course it is not null terminated.
OK.
On a related note, are there any other bytea functions we should have in
the
backend before freezing for 7.2? I was thinking it would be nice to have
a
way to cast bytea into text and vice-versa, so that the normal text
functions could be used for things like LIKE and concatenation. Any
interest
in this? If so, any guidance WRT how it should be implemented?
I can't see why you can't do that. The only problem is passing a \0
(null byte) back to the client.
Well, ISTM the simplest (if not the most efficient) way to do bytea-to-text
would be a function that takes the escaped string value from byteaout, and
creates a text value directly from it. The only danger I can think of is
that very long strings might need to be truncated in length, since the
escaped string could be significantly longer than the binary.
Text-to-bytea should be a straight copy, since nothing that can be
represented as text cannot be represented as bytea.
Any comments or concerns?
-- Joe
On Mon, Sep 03, 2001 at 08:48:22PM -0400, Tom Lane wrote:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
Where did we leave this?
I don't think adding a datatype just to provide base64 encoding is
a wise approach. The overhead of a new datatype (in the sense of
providing operators/functions for it) will be much more than the
benefit. I think providing encode/decode functions is sufficient...
and we have those already, don't we?
Agree too. But 1000 "bad" chars encoded by base64 vs. encoded by
escape, what is longer and more expensive for transfer between FE
and BE?
A base64 problem is that encode all chars in string, but in the
real usage some data contains "bad" chars occasional only.
Karel
--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/
C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
Joe Conway writes:
On a related note, are there any other bytea functions we should have in the
backend before freezing for 7.2?
The SQL standards has a lot of functions for BLOB...
I was thinking it would be nice to have a
way to cast bytea into text and vice-versa, so that the normal text
functions could be used for things like LIKE and concatenation.
Better write a native LIKE function for bytea, now that some parts are
threatening to make the text-LIKE function use the locale collating
sequence. (Multibyte aware text could also have interesting effects.)
--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
"Joe Conway" <joseph.conway@home.com> writes:
Well, ISTM the simplest (if not the most efficient) way to do bytea-to-text
would be a function that takes the escaped string value from byteaout, and
creates a text value directly from it. The only danger I can think of is
that very long strings might need to be truncated in length, since the
escaped string could be significantly longer than the binary.
Text-to-bytea should be a straight copy, since nothing that can be
represented as text cannot be represented as bytea.
Ugh ... if the conversion functions are not inverses then I think they
lose much of their value. I could see doing either of these:
1. Conversion functions based on byteaout/byteain.
2. Bytea to text escapes *only* null bytes, text to bytea treats only
"\0" as an escape sequence.
Or maybe both, with two pairs of conversion functions.
In any case, we have to decide whether these coercion functions should
be named after the types --- ie, should they be made invokable as
implicit coercions? I'm dubious that that's a good idea; if we do it
then all sorts of textual operations will suddenly be allowed for bytea
without any explicit conversion, which is likely to do more harm than
good. The reason for having a separate bytea type is exactly so that
you *can't* apply text ops to it without thinking.
regards, tom lane
On a related note, are there any other bytea functions we should have in
the
backend before freezing for 7.2?
The SQL standards has a lot of functions for BLOB...
OK - thanks. I'll take a look.
I was thinking it would be nice to have a
way to cast bytea into text and vice-versa, so that the normal text
functions could be used for things like LIKE and concatenation.Better write a native LIKE function for bytea, now that some parts are
threatening to make the text-LIKE function use the locale collating
sequence. (Multibyte aware text could also have interesting effects.)
Sounds like good advice. I'll try to get both the cast functions and a
native bytea LIKE function done.
-- Joe
Ugh ... if the conversion functions are not inverses then I think they
lose much of their value. I could see doing either of these:1. Conversion functions based on byteaout/byteain.
2. Bytea to text escapes *only* null bytes, text to bytea treats only
"\0" as an escape sequence.Or maybe both, with two pairs of conversion functions.
In any case, we have to decide whether these coercion functions should
be named after the types --- ie, should they be made invokable as
implicit coercions? I'm dubious that that's a good idea; if we do it
then all sorts of textual operations will suddenly be allowed for bytea
without any explicit conversion, which is likely to do more harm than
good. The reason for having a separate bytea type is exactly so that
you *can't* apply text ops to it without thinking.regards, tom lane
You're right, as usual (I was tired when I wrote this last night ;). But I
think we have to escape/unescape both null and '\', don't we?
I agree that it would be better to *not* allow implicit coercions. Given
that, any preferences on function names? Are text_to_bytea() and
bytea_to_text() too ugly?
-- Joe
"Joe Conway" <joseph.conway@home.com> writes:
You're right, as usual (I was tired when I wrote this last night ;). But I
think we have to escape/unescape both null and '\', don't we?
Yeah, you're right. My turn to have not thought hard enough.
I agree that it would be better to *not* allow implicit coercions. Given
that, any preferences on function names? Are text_to_bytea() and
bytea_to_text() too ugly?
They're pretty ugly, but more importantly they're only suitable if we
have exactly one conversion function each way. If we have two, what
will we call the second one?
I think it's okay to let the argument type be implicit in the function
argument list. Something like text_escaped(bytea) and text_direct(bytea)
(with inverses bytea_escaped(text) and bytea_direct(text)) might do.
I'm not totally happy with "direct" to suggest minimum escaping, though.
Better ideas anyone?
regards, tom lane
You're right, as usual (I was tired when I wrote this last night ;). But I
think we have to escape/unescape both null and '\', don't we?
Yes, I think backslashes need special escapes too.
Let me ask a bigger question. We have the length of the text string in
the varlena header. Are we concerned about backend code not handling
NULL in text fields, or frontend code returning strings with embedded
nulls?
I see problems in the text() functions for nulls, but is such a
limitation required for text types?
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
* Tom Lane <tgl@sss.pgh.pa.us> [010904 12:01]:
They're pretty ugly, but more importantly they're only suitable if we
have exactly one conversion function each way. If we have two, what
will we call the second one?I think it's okay to let the argument type be implicit in the function
argument list. Something like text_escaped(bytea) and text_direct(bytea)
(with inverses bytea_escaped(text) and bytea_direct(text)) might do.
I'm not totally happy with "direct" to suggest minimum escaping, though.
Better ideas anyone?
Cooked vs raw?
LER
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
Bruce Momjian <pgman@candle.pha.pa.us> writes:
Let me ask a bigger question. We have the length of the text string in
the varlena header. Are we concerned about backend code not handling
NULL in text fields, or frontend code returning strings with embedded
nulls?
The former.
I see problems in the text() functions for nulls, but is such a
limitation required for text types?
Unless you want to re-implement strcoll() and friends from scratch.
regards, tom lane
Bruce Momjian <pgman@candle.pha.pa.us> writes:
Let me ask a bigger question. We have the length of the text string in
the varlena header. Are we concerned about backend code not handling
NULL in text fields, or frontend code returning strings with embedded
nulls?The former.
I see problems in the text() functions for nulls, but is such a
limitation required for text types?Unless you want to re-implement strcoll() and friends from scratch.
Yes, I saw strcoll().
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026Peter Eisentraut <peter_e@gmx.net> writes:
Why not just stick these things into encode() and name them
"my-cool-encoding" or whatever.
Sounds good to me ...
regards, tom lane
Import Notes
Reply to msg id not found: Pine.LNX.4.30.0109050054260.828-100000@peter.localdomainReference msg id not found: Pine.LNX.4.30.0109050054260.828-100000@peter.localdomain | Resolved by subject fallback
Tom Lane writes:
I agree that it would be better to *not* allow implicit coercions. Given
that, any preferences on function names? Are text_to_bytea() and
bytea_to_text() too ugly?They're pretty ugly, but more importantly they're only suitable if we
have exactly one conversion function each way. If we have two, what
will we call the second one?
Why not just stick these things into encode() and name them
"my-cool-encoding" or whatever. There is no truly natural conversion
between text and bytea, so encode/decode seem like the proper place.
--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
Peter Eisentraut <peter_e@gmx.net> writes:
Why not just stick these things into encode() and name them
"my-cool-encoding" or whatever.Sounds good to me ...
regards, tom lane
Sounds good to me too. Patch forthcoming . . .
-- Joe
I agree that it would be better to *not* allow implicit coercions.
Given
that, any preferences on function names? Are text_to_bytea() and
bytea_to_text() too ugly?They're pretty ugly, but more importantly they're only suitable if we
have exactly one conversion function each way. If we have two, what
will we call the second one?Why not just stick these things into encode() and name them
"my-cool-encoding" or whatever. There is no truly natural conversion
between text and bytea, so encode/decode seem like the proper place.
(I'm sending directly to Peter, Tom, and Bruce because you were all involved
in this thread, and the list seems to be down)
Here's a patch for bytea string functions. As discussed:
text encode(bytea, 'escape')
bytea decode(text, 'escape')
to allow conversion bytea-text/text-bytea conversion. Also implemented
(SQL99 defines Binary Strings with all of these operators):
byteacat and "||" operator
substring
trim (only did trim(bytea, bytea) since there is no default trim character
for bunary per SQL99)
length (just aliased octet_length, which is correct for bytea, I think)
position
like and "~~" operator
not like and "!~~" operator
I think that's it.
Passes all regression tests. Based on the discussion, I did not create
functions to allow casting text-to-bytea or bytea-to-text -- it sounded like
we just want people to use encode/decode. I'm still planning to write
PQescapeBytea, but that will come later as a seperate patch. One operator
defined by SQL99, but not implemented here (or for text datatype, that I
could see) is the "overlay" function (modifies string argument by replacing
a substring given start and length with a replacement string). It sounds
useful -- any interest?
Review and comments much appreciated!
-- Joe
Attachments:
bytea_string_funcs_r00.diffapplication/octet-stream; name=bytea_string_funcs_r00.diffDownload
diff -Naur pgsql.virg/src/backend/utils/adt/encode.c pgsql.dev/src/backend/utils/adt/encode.c
--- pgsql.virg/src/backend/utils/adt/encode.c Mon Sep 3 22:45:44 2001
+++ pgsql.dev/src/backend/utils/adt/encode.c Tue Sep 4 18:59:36 2001
@@ -19,8 +19,8 @@
struct pg_encoding
{
- unsigned (*encode_len) (unsigned dlen);
- unsigned (*decode_len) (unsigned dlen);
+ unsigned (*encode_len) (const uint8 *data, unsigned dlen);
+ unsigned (*decode_len) (const uint8 *data, unsigned dlen);
unsigned (*encode) (const uint8 *data, unsigned dlen, uint8 *res);
unsigned (*decode) (const uint8 *data, unsigned dlen, uint8 *res);
};
@@ -50,7 +50,7 @@
if (enc == NULL)
elog(ERROR, "No such encoding");
- resultlen = enc->encode_len(datalen);
+ resultlen = enc->encode_len(VARDATA(data), datalen);
result = palloc(VARHDRSZ + resultlen);
res = enc->encode(VARDATA(data), datalen, VARDATA(result));
@@ -81,7 +81,7 @@
if (enc == NULL)
elog(ERROR, "No such encoding");
- resultlen = enc->decode_len(datalen);
+ resultlen = enc->decode_len(VARDATA(data), datalen);
result = palloc(VARHDRSZ + resultlen);
res = enc->decode(VARDATA(data), datalen, VARDATA(result));
@@ -169,13 +169,13 @@
}
static unsigned
-hex_enc_len(unsigned srclen)
+hex_enc_len(const uint8 * src, unsigned srclen)
{
return srclen << 1;
}
static unsigned
-hex_dec_len(unsigned srclen)
+hex_dec_len(const uint8 * src, unsigned srclen)
{
return srclen >> 1;
}
@@ -308,19 +308,144 @@
static unsigned
-b64_enc_len(unsigned srclen)
+b64_enc_len(const uint8 * src, unsigned srclen)
{
/* 3 bytes will be converted to 4, linefeed after 76 chars */
return (srclen + 2) * 4 / 3 + srclen / (76 * 3 / 4);
}
static unsigned
-b64_dec_len(unsigned srclen)
+b64_dec_len(const uint8 * src, unsigned srclen)
{
return (srclen * 3) >> 2;
}
/*
+ * Escape
+ * Minimally escape bytea to text.
+ * De-escape text to bytea.
+ *
+ * Only two characters are escaped:
+ * \000 (null) and \134 (backslash)
+ *
+ * De-escapes anything normally handled by byteain
+ * ("\\", or any "\###" octal)
+ */
+
+#define VAL(CH) ((CH) - '0')
+#define DIG(VAL) ((VAL) + '0')
+
+static unsigned
+esc_encode(const uint8 *src, unsigned srclen, uint8 *dst)
+{
+ const uint8 *end = src + srclen;
+ uint8 *rp = dst;
+ int val;
+ int len = 0;
+
+ while (src < end)
+ {
+ if ((*src == 0) || (*src == 92))
+ {
+ val = *src;
+ rp[0] = '\\';
+ rp[3] = DIG(val & 07);
+ val >>= 3;
+ rp[2] = DIG(val & 07);
+ val >>= 3;
+ rp[1] = DIG(val & 03);
+ rp += 4;
+ len += 4;
+ }
+ else
+ {
+ *rp++ = *src;
+ len++;
+ }
+
+ src++;
+ }
+ *rp = '\0';
+
+ return len;
+}
+
+static unsigned
+esc_decode(const uint8 *src, unsigned srclen, uint8 *dst)
+{
+ const uint8 *end = src + srclen;
+ uint8 *rp = dst;
+ int val;
+ int len = 0;
+
+ while (src < end)
+ {
+ if (*src != '\\' || *++src == '\\')
+ *rp++ = *src++;
+ else
+ {
+ val = VAL(*src++);
+ val <<= 3;
+ val += VAL(*src++);
+ val <<= 3;
+ *rp++ = val + VAL(*src++);
+ }
+
+ len++;
+ }
+ return len;
+}
+
+static unsigned
+esc_enc_len(const uint8 *src, unsigned srclen)
+{
+ const uint8 *end = src + srclen;
+ int len = 0;
+
+ while (src < end)
+ {
+ if ((*src == 0) || (*src == 92))
+ len += 4;
+ else
+ len++;
+
+ src++;
+ }
+
+ /*
+ * Allow for null terminator
+ */
+ len++;
+
+ return len;
+}
+
+static unsigned
+esc_dec_len(const uint8 *src, unsigned srclen)
+{
+ const uint8 *end = src + srclen;
+ int len = 0;
+
+ while (src < end)
+ {
+ if (*src++ == '\\')
+ {
+ if (*src == '\\')
+ src++;
+ else if (!isdigit((unsigned char) *src++) ||
+ !isdigit((unsigned char) *src++) ||
+ !isdigit((unsigned char) *src++))
+ {
+ elog(ERROR, "escape: Bad input string for type bytea");
+ }
+ }
+
+ len++;
+ }
+ return len;
+}
+
+/*
* Common
*/
@@ -330,6 +455,7 @@
} enclist[] = {
{"hex", { hex_enc_len, hex_dec_len, hex_encode, hex_decode }},
{"base64", { b64_enc_len, b64_dec_len, b64_encode, b64_decode }},
+ {"escape", { esc_enc_len, esc_dec_len, esc_encode, esc_decode }},
{NULL, { NULL, NULL, NULL, NULL } }
};
diff -Naur pgsql.virg/src/backend/utils/adt/like.c pgsql.dev/src/backend/utils/adt/like.c
--- pgsql.virg/src/backend/utils/adt/like.c Mon Sep 3 22:45:44 2001
+++ pgsql.dev/src/backend/utils/adt/like.c Wed Sep 5 00:03:44 2001
@@ -120,6 +120,9 @@
#define CopyAdvChar(dst, src, srclen) (*(dst)++ = *(src)++, (srclen)--)
#endif
+#define BYTEA_CHAREQ(p1, p2) (*(p1) == *(p2))
+#define BYTEA_NextChar(p, plen) ((p)++, (plen)--)
+#define BYTEA_CopyAdvChar(dst, src, srclen) (*(dst)++ = *(src)++, (srclen)--)
/*
* interface routines called by the function manager
@@ -209,6 +212,48 @@
PG_RETURN_BOOL(result);
}
+Datum
+bytealike(PG_FUNCTION_ARGS)
+{
+ bytea *str = PG_GETARG_BYTEA_P(0);
+ bytea *pat = PG_GETARG_BYTEA_P(1);
+ bool result;
+ unsigned char *s,
+ *p;
+ int slen,
+ plen;
+
+ s = VARDATA(str);
+ slen = (VARSIZE(str) - VARHDRSZ);
+ p = VARDATA(pat);
+ plen = (VARSIZE(pat) - VARHDRSZ);
+
+ result = (MatchBytea(s, slen, p, plen) == LIKE_TRUE);
+
+ PG_RETURN_BOOL(result);
+}
+
+Datum
+byteanlike(PG_FUNCTION_ARGS)
+{
+ bytea *str = PG_GETARG_BYTEA_P(0);
+ bytea *pat = PG_GETARG_BYTEA_P(1);
+ bool result;
+ unsigned char *s,
+ *p;
+ int slen,
+ plen;
+
+ s = VARDATA(str);
+ slen = (VARSIZE(str) - VARHDRSZ);
+ p = VARDATA(pat);
+ plen = (VARSIZE(pat) - VARHDRSZ);
+
+ result = (MatchBytea(s, slen, p, plen) != LIKE_TRUE);
+
+ PG_RETURN_BOOL(result);
+}
+
/*
* Case-insensitive versions
*/
@@ -395,6 +440,103 @@
PG_RETURN_TEXT_P(result);
}
+/*
+ * like_escape_bytea() --- given a pattern and an ESCAPE string,
+ * convert the pattern to use Postgres' standard backslash escape convention.
+ */
+Datum
+like_escape_bytea(PG_FUNCTION_ARGS)
+{
+ bytea *pat = PG_GETARG_BYTEA_P(0);
+ bytea *esc = PG_GETARG_BYTEA_P(1);
+ bytea *result;
+ unsigned char *p,
+ *e,
+ *r;
+ int plen,
+ elen;
+ bool afterescape;
+
+ p = VARDATA(pat);
+ plen = (VARSIZE(pat) - VARHDRSZ);
+ e = VARDATA(esc);
+ elen = (VARSIZE(esc) - VARHDRSZ);
+
+ /*
+ * Worst-case pattern growth is 2x --- unlikely, but it's hardly worth
+ * trying to calculate the size more accurately than that.
+ */
+ result = (text *) palloc(plen * 2 + VARHDRSZ);
+ r = VARDATA(result);
+
+ if (elen == 0)
+ {
+
+ /*
+ * No escape character is wanted. Double any backslashes in the
+ * pattern to make them act like ordinary characters.
+ */
+ while (plen > 0)
+ {
+ if (*p == '\\')
+ *r++ = '\\';
+ BYTEA_CopyAdvChar(r, p, plen);
+ }
+ }
+ else
+ {
+
+ /*
+ * The specified escape must be only a single character.
+ */
+ BYTEA_NextChar(e, elen);
+ if (elen != 0)
+ elog(ERROR, "ESCAPE string must be empty or one character");
+ e = VARDATA(esc);
+
+ /*
+ * If specified escape is '\', just copy the pattern as-is.
+ */
+ if (*e == '\\')
+ {
+ memcpy(result, pat, VARSIZE(pat));
+ PG_RETURN_BYTEA_P(result);
+ }
+
+ /*
+ * Otherwise, convert occurrences of the specified escape
+ * character to '\', and double occurrences of '\' --- unless they
+ * immediately follow an escape character!
+ */
+ afterescape = false;
+ while (plen > 0)
+ {
+ if (BYTEA_CHAREQ(p, e) && !afterescape)
+ {
+ *r++ = '\\';
+ BYTEA_NextChar(p, plen);
+ afterescape = true;
+ }
+ else if (*p == '\\')
+ {
+ *r++ = '\\';
+ if (!afterescape)
+ *r++ = '\\';
+ BYTEA_NextChar(p, plen);
+ afterescape = false;
+ }
+ else
+ {
+ BYTEA_CopyAdvChar(r, p, plen);
+ afterescape = false;
+ }
+ }
+ }
+
+ VARATT_SIZEP(result) = r - ((unsigned char *) result);
+
+ PG_RETURN_BYTEA_P(result);
+}
/*
** Originally written by Rich $alz, mirror!rs, Wed Nov 26 19:03:17 EST 1986.
@@ -614,3 +756,91 @@
*/
return LIKE_ABORT;
} /* MatchTextIC() */
+
+/*
+ * Same as above, but specifically for bytea (binary) datatype
+ */
+static int
+MatchBytea(unsigned char *t, int tlen, unsigned char *p, int plen)
+{
+ /* Fast path for match-everything pattern */
+ if ((plen == 1) && (*p == '%'))
+ return LIKE_TRUE;
+
+ while ((tlen > 0) && (plen > 0))
+ {
+ if (*p == '\\')
+ {
+ /* Next pattern char must match literally, whatever it is */
+ BYTEA_NextChar(p, plen);
+ if ((plen <= 0) || !BYTEA_CHAREQ(t, p))
+ return LIKE_FALSE;
+ }
+ else if (*p == '%')
+ {
+ /* %% is the same as % according to the SQL standard */
+ /* Advance past all %'s */
+ while ((plen > 0) && (*p == '%'))
+ BYTEA_NextChar(p, plen);
+ /* Trailing percent matches everything. */
+ if (plen <= 0)
+ return LIKE_TRUE;
+
+ /*
+ * Otherwise, scan for a text position at which we can match
+ * the rest of the pattern.
+ */
+ while (tlen > 0)
+ {
+
+ /*
+ * Optimization to prevent most recursion: don't recurse
+ * unless first pattern char might match this text char.
+ */
+ if (BYTEA_CHAREQ(t, p) || (*p == '\\') || (*p == '_'))
+ {
+ int matched = MatchBytea(t, tlen, p, plen);
+
+ if (matched != LIKE_FALSE)
+ return matched; /* TRUE or ABORT */
+ }
+
+ BYTEA_NextChar(t, tlen);
+ }
+
+ /*
+ * End of text with no match, so no point in trying later
+ * places to start matching this pattern.
+ */
+ return LIKE_ABORT;
+ }
+ else if ((*p != '_') && !BYTEA_CHAREQ(t, p))
+ {
+
+ /*
+ * Not the single-character wildcard and no explicit match?
+ * Then time to quit...
+ */
+ return LIKE_FALSE;
+ }
+
+ BYTEA_NextChar(t, tlen);
+ BYTEA_NextChar(p, plen);
+ }
+
+ if (tlen > 0)
+ return LIKE_FALSE; /* end of pattern, but not of text */
+
+ /* End of input string. Do we have matching pattern remaining? */
+ while ((plen > 0) && (*p == '%')) /* allow multiple %'s at end of
+ * pattern */
+ BYTEA_NextChar(p, plen);
+ if (plen <= 0)
+ return LIKE_TRUE;
+
+ /*
+ * End of text with no match, so no point in trying later places to
+ * start matching this pattern.
+ */
+ return LIKE_ABORT;
+} /* MatchBytea() */
diff -Naur pgsql.virg/src/backend/utils/adt/oracle_compat.c pgsql.dev/src/backend/utils/adt/oracle_compat.c
--- pgsql.virg/src/backend/utils/adt/oracle_compat.c Mon Sep 3 22:45:44 2001
+++ pgsql.dev/src/backend/utils/adt/oracle_compat.c Wed Sep 5 12:06:32 2001
@@ -349,6 +349,78 @@
PG_RETURN_TEXT_P(ret);
}
+/********************************************************************
+ *
+ * byteatrim
+ *
+ * Syntax:
+ *
+ * bytea byteatrim(byta string, bytea set)
+ *
+ * Purpose:
+ *
+ * Returns string with characters removed from the front and back
+ * up to the first character not in set.
+ *
+ * Cloned from btrim and modified as required.
+ ********************************************************************/
+
+Datum
+byteatrim(PG_FUNCTION_ARGS)
+{
+ bytea *string = PG_GETARG_BYTEA_P(0);
+ bytea *set = PG_GETARG_BYTEA_P(1);
+ bytea *ret;
+ char *ptr,
+ *end,
+ *ptr2,
+ *end2;
+ int m;
+
+ if ((m = VARSIZE(string) - VARHDRSZ) <= 0 ||
+ (VARSIZE(set) - VARHDRSZ) <= 0)
+ PG_RETURN_BYTEA_P(string);
+
+ ptr = VARDATA(string);
+ end = VARDATA(string) + VARSIZE(string) - VARHDRSZ - 1;
+ end2 = VARDATA(set) + VARSIZE(set) - VARHDRSZ - 1;
+
+ while (m > 0)
+ {
+ ptr2 = VARDATA(set);
+ while (ptr2 <= end2)
+ {
+ if (*ptr == *ptr2)
+ break;
+ ++ptr2;
+ }
+ if (ptr2 > end2)
+ break;
+ ptr++;
+ m--;
+ }
+
+ while (m > 0)
+ {
+ ptr2 = VARDATA(set);
+ while (ptr2 <= end2)
+ {
+ if (*end == *ptr2)
+ break;
+ ++ptr2;
+ }
+ if (ptr2 > end2)
+ break;
+ end--;
+ m--;
+ }
+
+ ret = (bytea *) palloc(VARHDRSZ + m);
+ VARATT_SIZEP(ret) = VARHDRSZ + m;
+ memcpy(VARDATA(ret), ptr, m);
+
+ PG_RETURN_BYTEA_P(ret);
+}
/********************************************************************
*
diff -Naur pgsql.virg/src/backend/utils/adt/selfuncs.c pgsql.dev/src/backend/utils/adt/selfuncs.c
--- pgsql.virg/src/backend/utils/adt/selfuncs.c Mon Sep 3 22:45:44 2001
+++ pgsql.dev/src/backend/utils/adt/selfuncs.c Wed Sep 5 00:45:47 2001
@@ -2992,6 +2992,11 @@
datum1, datum2));
break;
+ case BYTEAOID:
+ result = DatumGetBool(DirectFunctionCall2(bytealt,
+ datum1, datum2));
+ break;
+
default:
elog(ERROR, "string_lessthan: unexpected datatype %u", datatype);
result = false;
diff -Naur pgsql.virg/src/backend/utils/adt/varlena.c pgsql.dev/src/backend/utils/adt/varlena.c
--- pgsql.virg/src/backend/utils/adt/varlena.c Mon Sep 3 22:45:44 2001
+++ pgsql.dev/src/backend/utils/adt/varlena.c Wed Sep 5 10:50:02 2001
@@ -662,6 +662,147 @@
PG_RETURN_INT32(VARSIZE(v) - VARHDRSZ);
}
+/*
+ * byteacat -
+ * takes two bytea* and returns a bytea* that is the concatenation of
+ * the two.
+ *
+ * Cloned from textcat and modified as required.
+ */
+Datum
+byteacat(PG_FUNCTION_ARGS)
+{
+ bytea *t1 = PG_GETARG_BYTEA_P(0);
+ bytea *t2 = PG_GETARG_BYTEA_P(1);
+ int len1,
+ len2,
+ len;
+ bytea *result;
+ char *ptr;
+
+ len1 = (VARSIZE(t1) - VARHDRSZ);
+ if (len1 < 0)
+ len1 = 0;
+
+ len2 = (VARSIZE(t2) - VARHDRSZ);
+ if (len2 < 0)
+ len2 = 0;
+
+ len = len1 + len2 + VARHDRSZ;
+ result = (bytea *) palloc(len);
+
+ /* Set size of result string... */
+ VARATT_SIZEP(result) = len;
+
+ /* Fill data field of result string... */
+ ptr = VARDATA(result);
+ if (len1 > 0)
+ memcpy(ptr, VARDATA(t1), len1);
+ if (len2 > 0)
+ memcpy(ptr + len1, VARDATA(t2), len2);
+
+ PG_RETURN_BYTEA_P(result);
+}
+
+/*
+ * bytea_substr()
+ * Return a substring starting at the specified position.
+ * Cloned from text_substr and modified as required.
+ *
+ * Input:
+ * - string
+ * - starting position (is one-based)
+ * - string length
+ *
+ * If the starting position is zero or less, then return from the start of the string
+ * adjusting the length to be consistant with the "negative start" per SQL92.
+ * If the length is less than zero, return the remaining string.
+ *
+ */
+Datum
+bytea_substr(PG_FUNCTION_ARGS)
+{
+ bytea *string = PG_GETARG_BYTEA_P(0);
+ int32 m = PG_GETARG_INT32(1);
+ int32 n = PG_GETARG_INT32(2);
+ bytea *ret;
+ int len;
+
+ len = VARSIZE(string) - VARHDRSZ;
+
+ /* starting position after the end of the string? */
+ if (m > len)
+ {
+ m = 1;
+ n = 0;
+ }
+
+ /*
+ * starting position before the start of the string? then offset into
+ * the string per SQL92 spec...
+ */
+ else if (m < 1)
+ {
+ n += (m - 1);
+ m = 1;
+ }
+
+ /* m will now become a zero-based starting position */
+ m--;
+ if (((m + n) > len) || (n < 0))
+ n = (len - m);
+
+ ret = (bytea *) palloc(VARHDRSZ + n);
+ VARATT_SIZEP(ret) = VARHDRSZ + n;
+
+ memcpy(VARDATA(ret), VARDATA(string) + m, n);
+
+ PG_RETURN_BYTEA_P(ret);
+}
+
+/*
+ * byteapos -
+ * Return the position of the specified substring.
+ * Implements the SQL92 POSITION() function.
+ * Cloned from textpos and modified as required.
+ */
+Datum
+byteapos(PG_FUNCTION_ARGS)
+{
+ bytea *t1 = PG_GETARG_BYTEA_P(0);
+ bytea *t2 = PG_GETARG_BYTEA_P(1);
+ int pos;
+ int px,
+ p;
+ int len1,
+ len2;
+ char *p1,
+ *p2;
+
+ if (VARSIZE(t2) <= VARHDRSZ)
+ PG_RETURN_INT32(1); /* result for empty pattern */
+
+ len1 = (VARSIZE(t1) - VARHDRSZ);
+ len2 = (VARSIZE(t2) - VARHDRSZ);
+
+ p1 = VARDATA(t1);
+ p2 = VARDATA(t2);
+
+ pos = 0;
+ px = (len1 - len2);
+ for (p = 0; p <= px; p++)
+ {
+ if ((*p2 == *p1) && (memcmp(p1, p2, len2) == 0))
+ {
+ pos = p + 1;
+ break;
+ };
+ p1++;
+ };
+
+ PG_RETURN_INT32(pos);
+}
+
/*-------------------------------------------------------------
* byteaGetByte
*
diff -Naur pgsql.virg/src/include/catalog/catversion.h pgsql.dev/src/include/catalog/catversion.h
--- pgsql.virg/src/include/catalog/catversion.h Mon Sep 3 22:45:52 2001
+++ pgsql.dev/src/include/catalog/catversion.h Wed Sep 5 12:17:00 2001
@@ -53,6 +53,6 @@
*/
/* yyyymmddN */
-#define CATALOG_VERSION_NO 200108251
+#define CATALOG_VERSION_NO 200109051
#endif
diff -Naur pgsql.virg/src/include/catalog/pg_operator.h pgsql.dev/src/include/catalog/pg_operator.h
--- pgsql.virg/src/include/catalog/pg_operator.h Mon Sep 3 22:45:52 2001
+++ pgsql.dev/src/include/catalog/pg_operator.h Wed Sep 5 11:39:49 2001
@@ -811,11 +811,15 @@
/* bytea operators */
DATA(insert OID = 1955 ( "=" PGUID 0 b t t 17 17 16 1955 1956 1957 1957 byteaeq eqsel eqjoinsel ));
-DATA(insert OID = 1956 ( "<>" PGUID 0 b t f 17 17 16 1956 1955 0 0 byteane neqsel neqjoinsel ));
-DATA(insert OID = 1957 ( "<" PGUID 0 b t f 17 17 16 1959 1960 0 0 bytealt scalarltsel scalarltjoinsel ));
-DATA(insert OID = 1958 ( "<=" PGUID 0 b t f 17 17 16 1960 1959 0 0 byteale scalarltsel scalarltjoinsel ));
-DATA(insert OID = 1959 ( ">" PGUID 0 b t f 17 17 16 1957 1958 0 0 byteagt scalargtsel scalargtjoinsel ));
-DATA(insert OID = 1960 ( ">=" PGUID 0 b t f 17 17 16 1958 1957 0 0 byteage scalargtsel scalargtjoinsel ));
+DATA(insert OID = 1956 ( "<>" PGUID 0 b t f 17 17 16 1956 1955 0 0 byteane neqsel neqjoinsel ));
+DATA(insert OID = 1957 ( "<" PGUID 0 b t f 17 17 16 1959 1960 0 0 bytealt scalarltsel scalarltjoinsel ));
+DATA(insert OID = 1958 ( "<=" PGUID 0 b t f 17 17 16 1960 1959 0 0 byteale scalarltsel scalarltjoinsel ));
+DATA(insert OID = 1959 ( ">" PGUID 0 b t f 17 17 16 1957 1958 0 0 byteagt scalargtsel scalargtjoinsel ));
+DATA(insert OID = 1960 ( ">=" PGUID 0 b t f 17 17 16 1958 1957 0 0 byteage scalargtsel scalargtjoinsel ));
+DATA(insert OID = 2016 ( "~~" PGUID 0 b t f 17 17 16 0 2017 0 0 bytealike likesel likejoinsel ));
+#define OID_BYTEA_LIKE_OP 2016
+DATA(insert OID = 2017 ( "!~~" PGUID 0 b t f 17 17 16 0 2016 0 0 byteanlike nlikesel nlikejoinsel ));
+DATA(insert OID = 2018 ( "||" PGUID 0 b t f 17 17 17 0 0 0 0 byteacat - - ));
/*
* function prototypes
diff -Naur pgsql.virg/src/include/catalog/pg_proc.h pgsql.dev/src/include/catalog/pg_proc.h
--- pgsql.virg/src/include/catalog/pg_proc.h Mon Sep 3 22:45:52 2001
+++ pgsql.dev/src/include/catalog/pg_proc.h Wed Sep 5 11:39:20 2001
@@ -2736,6 +2736,28 @@
DATA(insert OID = 1966 ( oidsmaller PGUID 12 f t t t 2 f 26 "26 26" 100 0 0 100 oidsmaller - ));
DESCR("smaller of two");
+DATA(insert OID = 2005 ( bytealike PGUID 12 f t t t 2 f 16 "17 17" 100 0 0 100 bytealike - ));
+DESCR("matches LIKE expression");
+DATA(insert OID = 2006 ( byteanlike PGUID 12 f t t t 2 f 16 "17 17" 100 0 0 100 byteanlike - ));
+DESCR("does not match LIKE expression");
+DATA(insert OID = 2007 ( like PGUID 12 f t t t 2 f 16 "17 17" 100 0 0 100 bytealike - ));
+DESCR("matches LIKE expression");
+DATA(insert OID = 2008 ( notlike PGUID 12 f t t t 2 f 16 "17 17" 100 0 0 100 byteanlike - ));
+DESCR("does not match LIKE expression");
+DATA(insert OID = 2009 ( like_escape PGUID 12 f t t t 2 f 17 "17 17" 100 0 0 100 like_escape_bytea - ));
+DESCR("convert match pattern to use backslash escapes");
+DATA(insert OID = 2010 ( length PGUID 12 f t t t 1 f 23 "17" 100 0 0 100 byteaoctetlen - ));
+DESCR("octet length");
+DATA(insert OID = 2011 ( byteacat PGUID 12 f t t t 2 f 17 "17 17" 100 0 0 100 byteacat - ));
+DESCR("concatenate");
+DATA(insert OID = 2012 ( substring PGUID 12 f t t t 3 f 17 "17 23 23" 100 0 0 100 bytea_substr - ));
+DESCR("return portion of string");
+DATA(insert OID = 2013 ( substring PGUID 14 f t t t 2 f 17 "17 23" 100 0 0 100 "select substring($1, $2, -1)" - ));
+DESCR("return portion of string");
+DATA(insert OID = 2014 ( position PGUID 12 f t t t 2 f 23 "17 17" 100 0 0 100 byteapos - ));
+DESCR("return position of substring");
+DATA(insert OID = 2015 ( btrim PGUID 12 f t t t 2 f 17 "17 17" 100 0 0 100 byteatrim - ));
+DESCR("trim both ends of string");
/*
* prototypes for functions pg_proc.c
diff -Naur pgsql.virg/src/include/utils/builtins.h pgsql.dev/src/include/utils/builtins.h
--- pgsql.virg/src/include/utils/builtins.h Mon Sep 3 22:45:52 2001
+++ pgsql.dev/src/include/utils/builtins.h Wed Sep 5 12:07:05 2001
@@ -421,6 +421,9 @@
extern Datum byteagt(PG_FUNCTION_ARGS);
extern Datum byteage(PG_FUNCTION_ARGS);
extern Datum byteacmp(PG_FUNCTION_ARGS);
+extern Datum byteacat(PG_FUNCTION_ARGS);
+extern Datum byteapos(PG_FUNCTION_ARGS);
+extern Datum bytea_substr(PG_FUNCTION_ARGS);
/* version.c */
extern Datum pgsql_version(PG_FUNCTION_ARGS);
@@ -434,7 +437,10 @@
extern Datum textnlike(PG_FUNCTION_ARGS);
extern Datum texticlike(PG_FUNCTION_ARGS);
extern Datum texticnlike(PG_FUNCTION_ARGS);
+extern Datum bytealike(PG_FUNCTION_ARGS);
+extern Datum byteanlike(PG_FUNCTION_ARGS);
extern Datum like_escape(PG_FUNCTION_ARGS);
+extern Datum like_escape_bytea(PG_FUNCTION_ARGS);
/* oracle_compat.c */
extern Datum lower(PG_FUNCTION_ARGS);
@@ -443,6 +449,7 @@
extern Datum lpad(PG_FUNCTION_ARGS);
extern Datum rpad(PG_FUNCTION_ARGS);
extern Datum btrim(PG_FUNCTION_ARGS);
+extern Datum byteatrim(PG_FUNCTION_ARGS);
extern Datum ltrim(PG_FUNCTION_ARGS);
extern Datum rtrim(PG_FUNCTION_ARGS);
extern Datum translate(PG_FUNCTION_ARGS);
On Wed, Sep 05, 2001 at 01:34:06PM -0700, Joe Conway wrote:
Why not just stick these things into encode() and name them
"my-cool-encoding" or whatever. There is no truly natural conversion
between text and bytea, so encode/decode seem like the proper place.Here's a patch for bytea string functions. As discussed:
text encode(bytea, 'escape')
bytea decode(text, 'escape')
Why are you using \xxx encoding there? As the 'escape' encoding
is supposed to be 'minimalistic' as it escapes only 2
problematic values, then IMHO it would be better to use
\0 and \\ as escapes - takes less room.
--
marko
On Wed, Sep 05, 2001 at 01:34:06PM -0700, Joe Conway wrote:
Why not just stick these things into encode() and name them
"my-cool-encoding" or whatever. There is no truly natural conversion
between text and bytea, so encode/decode seem like the proper place.Here's a patch for bytea string functions. As discussed:
text encode(bytea, 'escape')
bytea decode(text, 'escape')Why are you using \xxx encoding there? As the 'escape' encoding
is supposed to be 'minimalistic' as it escapes only 2
problematic values, then IMHO it would be better to use
\0 and \\ as escapes - takes less room.
Agreed, and I have documented this in the SGML pages. Knowing this,
bytea becomes a much easier format to use.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026Here's a patch for bytea string functions. As discussed:
text encode(bytea, 'escape')
bytea decode(text, 'escape')Why are you using \xxx encoding there? As the 'escape' encoding
is supposed to be 'minimalistic' as it escapes only 2
problematic values, then IMHO it would be better to use
\0 and \\ as escapes - takes less room.Agreed, and I have documented this in the SGML pages. Knowing this,
bytea becomes a much easier format to use.
No problem -- I kind of like the octal style better, but I can see your
point. I'll wait for awhile for more comments, and then send in a new patch.
-- Joe
Here's a patch for bytea string functions. As discussed:
text encode(bytea, 'escape')
bytea decode(text, 'escape')Why are you using \xxx encoding there? As the 'escape' encoding
is supposed to be 'minimalistic' as it escapes only 2
problematic values, then IMHO it would be better to use
\0 and \\ as escapes - takes less room.Agreed, and I have documented this in the SGML pages. Knowing this,
bytea becomes a much easier format to use.No problem -- I kind of like the octal style better, but I can see your
point. I'll wait for awhile for more comments, and then send in a new
patch.
Here's a revised patch. Changes:
1. Now outputs '\\' instead of '\134' when using encode(bytea, 'escape')
Note that I ended up leaving \0 as \000 so that there are no ambiguities
when decoding something like, for example, \0123.
2. Fixed bug in byteain which allowed input values which were not valid
octals (e.g. \789), to be parsed as if they were octals.
Joe
Attachments:
bytea_ops_r01.diffapplication/octet-stream; name=bytea_ops_r01.diffDownload
diff -Naur pgsql.virg/src/backend/utils/adt/selfuncs.c pgsql.dev/src/backend/utils/adt/selfuncs.c
--- pgsql.virg/src/backend/utils/adt/selfuncs.c Sat Aug 11 04:57:46 2001
+++ pgsql.dev/src/backend/utils/adt/selfuncs.c Sun Aug 12 06:09:40 2001
@@ -134,9 +134,21 @@
double *scaledlobound,
unsigned char *hibound,
double *scaledhibound);
+static void convert_bytea_to_scalar(unsigned char *value,
+ int valuelen,
+ double *scaledvalue,
+ unsigned char *lobound,
+ int loboundlen,
+ double *scaledlobound,
+ unsigned char *hibound,
+ int hiboundlen,
+ double *scaledhibound);
static double convert_one_string_to_scalar(unsigned char *value,
int rangelo, int rangehi);
+static double convert_one_bytea_to_scalar(unsigned char *value, int valuelen,
+ int rangelo, int rangehi);
static unsigned char *convert_string_datum(Datum value, Oid typid);
+static unsigned char *convert_bytea_datum(Datum value, int *valuelen);
static double convert_timevalue_to_scalar(Datum value, Oid typid);
static double get_att_numdistinct(Query *root, Var *var,
Form_pg_statistic stats);
@@ -1719,6 +1731,27 @@
}
/*
+ * Built-in bytea type
+ */
+ case BYTEAOID:
+ {
+ int valuelen;
+ int loboundlen;
+ int hiboundlen;
+ unsigned char *valstr = convert_bytea_datum(value, &valuelen);
+ unsigned char *lostr = convert_bytea_datum(lobound, &loboundlen);
+ unsigned char *histr = convert_bytea_datum(hibound, &hiboundlen);
+
+ convert_bytea_to_scalar(valstr, valuelen, scaledvalue,
+ lostr, loboundlen, scaledlobound,
+ histr, hiboundlen, scaledhibound);
+ pfree(valstr);
+ pfree(lostr);
+ pfree(histr);
+ return true;
+ }
+
+ /*
* Built-in time types
*/
case TIMESTAMPOID:
@@ -1787,7 +1820,8 @@
}
/*
- * Do convert_to_scalar()'s work for any character-string data type.
+ * Do convert_to_scalar()'s work for any character-string data type
+ * except bytea (since we can't assume null-termination with bytea)
*
* String datatypes are converted to a scale that ranges from 0 to 1,
* where we visualize the bytes of the string as fractional digits.
@@ -1992,6 +2026,133 @@
pfree(val);
val = xfrmstr;
#endif
+
+ return (unsigned char *) val;
+}
+
+/*
+ * Do convert_to_scalar()'s work for any bytea data type.
+ *
+ * Very similar to convert_string_to_scalar except
+ * we don't assume string-safe null-termination and
+ * therefore pass explicit lengths around.
+ *
+ * Also assumptions about likely "normal" ranges of
+ * charachters have been removed.
+ */
+static void
+convert_bytea_to_scalar(unsigned char *value,
+ int valuelen,
+ double *scaledvalue,
+ unsigned char *lobound,
+ int loboundlen,
+ double *scaledlobound,
+ unsigned char *hibound,
+ int hiboundlen,
+ double *scaledhibound)
+{
+ int rangelo,
+ rangehi,
+ i;
+ unsigned char *sptr;
+
+ rangelo = rangehi = hibound[0];
+ for (i = 0, sptr = lobound; i < loboundlen; i++, sptr++)
+ {
+ if (rangelo > *sptr)
+ rangelo = *sptr;
+ if (rangehi < *sptr)
+ rangehi = *sptr;
+ }
+ for (i = 0, sptr = hibound; i < hiboundlen; i++, sptr++)
+ {
+ if (rangelo > *sptr)
+ rangelo = *sptr;
+ if (rangehi < *sptr)
+ rangehi = *sptr;
+ }
+
+ /*
+ * If range includes less than 10 chars, assume we have not got enough
+ * data, and make it include the entire ASCII set.
+ */
+ if (rangehi - rangelo < 9)
+ {
+ rangelo = 0;
+ rangehi = 255;
+ }
+
+ /*
+ * Now strip any common prefix of the three strings.
+ */
+
+ for (i = 0; i < Min(Min(valuelen, loboundlen), hiboundlen); i++)
+ {
+ if (*lobound != *hibound || *lobound != *value)
+ break;
+ lobound++, hibound++, value++;
+ loboundlen--, hiboundlen--, valuelen--;
+ }
+
+ /*
+ * Now we can do the conversions.
+ */
+ *scaledvalue = convert_one_bytea_to_scalar(value, valuelen, rangelo, rangehi);
+ *scaledlobound = convert_one_bytea_to_scalar(lobound, loboundlen, rangelo, rangehi);
+ *scaledhibound = convert_one_bytea_to_scalar(hibound, hiboundlen, rangelo, rangehi);
+}
+
+static double
+convert_one_bytea_to_scalar(unsigned char *value, int valuelen,
+ int rangelo, int rangehi)
+{
+ double num,
+ denom,
+ base;
+
+ if (valuelen <= 0)
+ return 0.0; /* empty string has scalar value 0 */
+
+ /*
+ * Since base is at least 10, need not consider more than about 20
+ * chars
+ */
+ if (valuelen > 20)
+ valuelen = 20;
+
+ /* Convert initial characters to fraction */
+ base = rangehi - rangelo + 1;
+ num = 0.0;
+ denom = base;
+ while (valuelen-- > 0)
+ {
+ int ch = *value++;
+
+ if (ch < rangelo)
+ ch = rangelo - 1;
+ else if (ch > rangehi)
+ ch = rangehi + 1;
+ num += ((double) (ch - rangelo)) / denom;
+ denom *= base;
+ }
+
+ return num;
+}
+
+/*
+ * Convert a bytea-type Datum into a palloc'd string
+ * and return it's length.
+ *
+ */
+static unsigned char *
+convert_bytea_datum(Datum value, int *valuelen)
+{
+ char *val;
+ char *str = (char *) VARDATA(DatumGetPointer(value));
+
+ *valuelen = VARSIZE(DatumGetPointer(value)) - VARHDRSZ;
+ val = (char *) palloc(*valuelen);
+ memcpy(val, str, *valuelen);
return (unsigned char *) val;
}
diff -Naur pgsql.virg/src/backend/utils/adt/varlena.c pgsql.dev/src/backend/utils/adt/varlena.c
--- pgsql.virg/src/backend/utils/adt/varlena.c Sat Aug 11 04:57:46 2001
+++ pgsql.dev/src/backend/utils/adt/varlena.c Sun Aug 12 02:01:15 2001
@@ -875,3 +875,162 @@
PG_RETURN_TEXT_P(result);
}
+
+
+/*****************************************************************************
+ * Comparison Functions used for bytea
+ *
+ * Note: btree indexes need these routines not to leak memory; therefore,
+ * be careful to free working copies of toasted datums. Most places don't
+ * need to be so careful.
+ *****************************************************************************/
+
+Datum
+byteaeq(PG_FUNCTION_ARGS)
+{
+ bytea *arg1 = PG_GETARG_BYTEA_P(0);
+ bytea *arg2 = PG_GETARG_BYTEA_P(1);
+ int len1,
+ len2;
+ bool result;
+
+ len1 = VARSIZE(arg1) - VARHDRSZ;
+ len2 = VARSIZE(arg2) - VARHDRSZ;
+
+ /* fast path for different-length inputs */
+ if (len1 != len2)
+ result = false;
+ else
+ result = (memcmp(VARDATA(arg1), VARDATA(arg2), len1) == 0);
+
+ PG_FREE_IF_COPY(arg1, 0);
+ PG_FREE_IF_COPY(arg2, 1);
+
+ PG_RETURN_BOOL(result);
+}
+
+Datum
+byteane(PG_FUNCTION_ARGS)
+{
+ bytea *arg1 = PG_GETARG_BYTEA_P(0);
+ bytea *arg2 = PG_GETARG_BYTEA_P(1);
+ int len1,
+ len2;
+ bool result;
+
+ len1 = VARSIZE(arg1) - VARHDRSZ;
+ len2 = VARSIZE(arg2) - VARHDRSZ;
+
+ /* fast path for different-length inputs */
+ if (len1 != len2)
+ result = true;
+ else
+ result = (memcmp(VARDATA(arg1), VARDATA(arg2), len1) != 0);
+
+ PG_FREE_IF_COPY(arg1, 0);
+ PG_FREE_IF_COPY(arg2, 1);
+
+ PG_RETURN_BOOL(result);
+}
+
+Datum
+bytealt(PG_FUNCTION_ARGS)
+{
+ bytea *arg1 = PG_GETARG_BYTEA_P(0);
+ bytea *arg2 = PG_GETARG_BYTEA_P(1);
+ int len1,
+ len2;
+ int cmp;
+
+ len1 = VARSIZE(arg1) - VARHDRSZ;
+ len2 = VARSIZE(arg2) - VARHDRSZ;
+
+ cmp = memcmp(VARDATA(arg1), VARDATA(arg2), Min(len1, len2));
+
+ PG_FREE_IF_COPY(arg1, 0);
+ PG_FREE_IF_COPY(arg2, 1);
+
+ PG_RETURN_BOOL((cmp < 0) || ((cmp == 0) && (len1 < len2)));
+}
+
+Datum
+byteale(PG_FUNCTION_ARGS)
+{
+ bytea *arg1 = PG_GETARG_BYTEA_P(0);
+ bytea *arg2 = PG_GETARG_BYTEA_P(1);
+ int len1,
+ len2;
+ int cmp;
+
+ len1 = VARSIZE(arg1) - VARHDRSZ;
+ len2 = VARSIZE(arg2) - VARHDRSZ;
+
+ cmp = memcmp(VARDATA(arg1), VARDATA(arg2), Min(len1, len2));
+
+ PG_FREE_IF_COPY(arg1, 0);
+ PG_FREE_IF_COPY(arg2, 1);
+
+ PG_RETURN_BOOL((cmp < 0) || ((cmp == 0) && (len1 <= len2)));
+}
+
+Datum
+byteagt(PG_FUNCTION_ARGS)
+{
+ bytea *arg1 = PG_GETARG_BYTEA_P(0);
+ bytea *arg2 = PG_GETARG_BYTEA_P(1);
+ int len1,
+ len2;
+ int cmp;
+
+ len1 = VARSIZE(arg1) - VARHDRSZ;
+ len2 = VARSIZE(arg2) - VARHDRSZ;
+
+ cmp = memcmp(VARDATA(arg1), VARDATA(arg2), Min(len1, len2));
+
+ PG_FREE_IF_COPY(arg1, 0);
+ PG_FREE_IF_COPY(arg2, 1);
+
+ PG_RETURN_BOOL((cmp > 0) || ((cmp == 0) && (len1 > len2)));
+}
+
+Datum
+byteage(PG_FUNCTION_ARGS)
+{
+ bytea *arg1 = PG_GETARG_BYTEA_P(0);
+ bytea *arg2 = PG_GETARG_BYTEA_P(1);
+ int len1,
+ len2;
+ int cmp;
+
+ len1 = VARSIZE(arg1) - VARHDRSZ;
+ len2 = VARSIZE(arg2) - VARHDRSZ;
+
+ cmp = memcmp(VARDATA(arg1), VARDATA(arg2), Min(len1, len2));
+
+ PG_FREE_IF_COPY(arg1, 0);
+ PG_FREE_IF_COPY(arg2, 1);
+
+ PG_RETURN_BOOL((cmp > 0) || ((cmp == 0) && (len1 >= len2)));
+}
+
+Datum
+byteacmp(PG_FUNCTION_ARGS)
+{
+ bytea *arg1 = PG_GETARG_BYTEA_P(0);
+ bytea *arg2 = PG_GETARG_BYTEA_P(1);
+ int len1,
+ len2;
+ int cmp;
+
+ len1 = VARSIZE(arg1) - VARHDRSZ;
+ len2 = VARSIZE(arg2) - VARHDRSZ;
+
+ cmp = memcmp(VARDATA(arg1), VARDATA(arg2), Min(len1, len2));
+ if ((cmp == 0) && (len1 != len2))
+ cmp = (len1 < len2) ? -1 : 1;
+
+ PG_FREE_IF_COPY(arg1, 0);
+ PG_FREE_IF_COPY(arg2, 1);
+
+ PG_RETURN_INT32(cmp);
+}
diff -Naur pgsql.virg/src/include/catalog/catversion.h pgsql.dev/src/include/catalog/catversion.h
--- pgsql.virg/src/include/catalog/catversion.h Sat Aug 11 04:57:50 2001
+++ pgsql.dev/src/include/catalog/catversion.h Sat Aug 11 21:11:59 2001
@@ -53,6 +53,6 @@
*/
/* yyyymmddN */
-#define CATALOG_VERSION_NO 200108101
+#define CATALOG_VERSION_NO 200108111
#endif
diff -Naur pgsql.virg/src/include/catalog/pg_amop.h pgsql.dev/src/include/catalog/pg_amop.h
--- pgsql.virg/src/include/catalog/pg_amop.h Sat Aug 11 04:57:51 2001
+++ pgsql.dev/src/include/catalog/pg_amop.h Sat Aug 11 20:49:01 2001
@@ -250,6 +250,16 @@
DATA(insert ( 403 1077 1068 5 ));
/*
+ * nbtree bytea_ops
+ */
+
+DATA(insert ( 403 1970 1976 1 ));
+DATA(insert ( 403 1970 1977 2 ));
+DATA(insert ( 403 1970 1972 3 ));
+DATA(insert ( 403 1970 1979 4 ));
+DATA(insert ( 403 1970 1978 5 ));
+
+/*
* nbtree date_ops
*/
diff -Naur pgsql.virg/src/include/catalog/pg_amproc.h pgsql.dev/src/include/catalog/pg_amproc.h
--- pgsql.virg/src/include/catalog/pg_amproc.h Sat Aug 11 04:57:51 2001
+++ pgsql.dev/src/include/catalog/pg_amproc.h Sat Aug 11 21:08:47 2001
@@ -101,7 +101,7 @@
DATA(insert (403 1399 1358 1));
DATA(insert (403 424 1596 1));
DATA(insert (403 425 1672 1));
-
+DATA(insert (403 1970 1986 1));
/* hash */
DATA(insert (405 421 449 1));
diff -Naur pgsql.virg/src/include/catalog/pg_opclass.h pgsql.dev/src/include/catalog/pg_opclass.h
--- pgsql.virg/src/include/catalog/pg_opclass.h Sat Aug 11 04:57:51 2001
+++ pgsql.dev/src/include/catalog/pg_opclass.h Sat Aug 11 20:28:52 2001
@@ -123,5 +123,7 @@
DESCR("");
DATA(insert OID = 425 ( varbit_ops 1562 ));
DESCR("");
+DATA(insert OID = 1970 ( bytea_ops 17 ));
+DESCR("");
#endif /* PG_OPCLASS_H */
diff -Naur pgsql.virg/src/include/catalog/pg_operator.h pgsql.dev/src/include/catalog/pg_operator.h
--- pgsql.virg/src/include/catalog/pg_operator.h Sat Aug 11 04:57:51 2001
+++ pgsql.dev/src/include/catalog/pg_operator.h Sat Aug 11 20:47:50 2001
@@ -809,6 +809,14 @@
DATA(insert OID = 1920 ( "+" PGUID 0 l t f 0 701 701 0 0 0 0 float8up - - ));
DATA(insert OID = 1921 ( "+" PGUID 0 l t f 0 1700 1700 0 0 0 0 numeric_uplus - - ));
+/* bytea operators */
+DATA(insert OID = 1972 ( "=" PGUID 0 b t t 17 17 16 1972 1975 1976 1976 byteaeq eqsel eqjoinsel ));
+DATA(insert OID = 1975 ( "<>" PGUID 0 b t f 17 17 16 1975 1972 0 0 byteane neqsel neqjoinsel ));
+DATA(insert OID = 1976 ( "<" PGUID 0 b t f 17 17 16 1978 1979 0 0 bytealt scalarltsel scalarltjoinsel ));
+DATA(insert OID = 1977 ( "<=" PGUID 0 b t f 17 17 16 1979 1978 0 0 byteale scalarltsel scalarltjoinsel ));
+DATA(insert OID = 1978 ( ">" PGUID 0 b t f 17 17 16 1976 1977 0 0 byteagt scalargtsel scalargtjoinsel ));
+DATA(insert OID = 1979 ( ">=" PGUID 0 b t f 17 17 16 1977 1976 0 0 byteage scalargtsel scalargtjoinsel ));
+
/*
* function prototypes
*/
diff -Naur pgsql.virg/src/include/catalog/pg_proc.h pgsql.dev/src/include/catalog/pg_proc.h
--- pgsql.virg/src/include/catalog/pg_proc.h Sat Aug 11 04:57:51 2001
+++ pgsql.dev/src/include/catalog/pg_proc.h Sat Aug 11 21:00:24 2001
@@ -2699,6 +2699,22 @@
DESCR("Convert bytea value into some ascii-only text string");
DATA(insert OID = 1947 ( decode PGUID 12 f t t t 2 f 17 "25 25" 100 0 0 100 binary_decode - ));
DESCR("Convert ascii-encoded text string into bytea value");
+
+DATA(insert OID = 1980 ( byteaeq PGUID 12 f t t t 2 f 16 "17 17" 100 0 0 100 byteaeq - ));
+DESCR("equal");
+DATA(insert OID = 1981 ( bytealt PGUID 12 f t t t 2 f 16 "17 17" 100 0 0 100 bytealt - ));
+DESCR("less-than");
+DATA(insert OID = 1982 ( byteale PGUID 12 f t t t 2 f 16 "17 17" 100 0 0 100 byteale - ));
+DESCR("less-than-or-equal");
+DATA(insert OID = 1983 ( byteagt PGUID 12 f t t t 2 f 16 "17 17" 100 0 0 100 byteagt - ));
+DESCR("greater-than");
+DATA(insert OID = 1984 ( byteage PGUID 12 f t t t 2 f 16 "17 17" 100 0 0 100 byteage - ));
+DESCR("greater-than-or-equal");
+DATA(insert OID = 1985 ( byteane PGUID 12 f t t t 2 f 16 "17 17" 100 0 0 100 byteane - ));
+DESCR("not equal");
+DATA(insert OID = 1986 ( byteacmp PGUID 12 f t t t 2 f 23 "17 17" 100 0 0 100 byteacmp - ));
+DESCR("less-equal-greater");
+
/*
* prototypes for functions pg_proc.c
diff -Naur pgsql.virg/src/include/utils/builtins.h pgsql.dev/src/include/utils/builtins.h
--- pgsql.virg/src/include/utils/builtins.h Sat Aug 11 04:57:55 2001
+++ pgsql.dev/src/include/utils/builtins.h Sat Aug 11 21:04:30 2001
@@ -412,6 +412,13 @@
extern Datum byteaSetBit(PG_FUNCTION_ARGS);
extern Datum binary_encode(PG_FUNCTION_ARGS);
extern Datum binary_decode(PG_FUNCTION_ARGS);
+extern Datum byteaeq(PG_FUNCTION_ARGS);
+extern Datum byteane(PG_FUNCTION_ARGS);
+extern Datum bytealt(PG_FUNCTION_ARGS);
+extern Datum byteale(PG_FUNCTION_ARGS);
+extern Datum byteagt(PG_FUNCTION_ARGS);
+extern Datum byteage(PG_FUNCTION_ARGS);
+extern Datum byteacmp(PG_FUNCTION_ARGS);
/* version.c */
extern Datum pgsql_version(PG_FUNCTION_ARGS);