Hi Dean,
> Those are all instances of a value that's outside a specific range
> that you might not otherwise know, rather than being out of range of
> the type itself. For that, we generally don't say what the range of
> the type is. For example, we currently do:
>
> select repeat('1', 50)::bit(50)::int;
> ERROR: integer out of range
Thanks. I agree that the proposed error messages look nicer than the
one I used in v6. Here is the corrected patch.
--
Best regards,
Aleksander Alekseev
From b9b5e358d00945bc0c4bb4a1b6e52497a6014690 Mon Sep 17 00:00:00 2001
From: Aleksander Alekseev <aleksander@timescale.com>
Date: Mon, 26 Aug 2024 12:09:59 +0300
Subject: [PATCH v7] Allow casting between bytea and integer types.
For instance:
SELECT '\x12345678'::bytea::integer;
SELECT 0x12345678::bytea;
This works with int2's, int4's and int8's.
Author: Aleksander Alekseev
Reviewed-by: Peter Eisentraut, Michael Paquier, Dean Rasheed
Discussion: https://postgr.es/m/CAJ7c6TPtOp6%2BkFX5QX3fH1SVr7v65uHr-7yEJ%3DGMGQi5uhGtcA%40mail.gmail.com
BUMP CATVERSION
---
src/backend/utils/adt/int.c | 84 ++++++++++++++++
src/include/catalog/pg_cast.dat | 14 +++
src/include/catalog/pg_proc.dat | 18 ++++
src/test/regress/expected/opr_sanity.out | 6 ++
src/test/regress/expected/strings.out | 120 +++++++++++++++++++++++
src/test/regress/sql/strings.sql | 34 +++++++
6 files changed, 276 insertions(+)
diff --git a/src/backend/utils/adt/int.c b/src/backend/utils/adt/int.c
index b5781989a64..0be739bae64 100644
--- a/src/backend/utils/adt/int.c
+++ b/src/backend/utils/adt/int.c
@@ -336,6 +336,90 @@ int4send(PG_FUNCTION_ARGS)
* ===================
*/
+/* Common code for bytea_int2, bytea_int4 and bytea_int8 */
+static int64
+bytea_integer(const bytea *v, int len)
+{
+ int offset = 0;
+ int64 result = 0;
+
+ while (len--)
+ {
+ result = result << 8;
+ result |= ((unsigned char *) VARDATA_ANY(v))[offset];
+ offset++;
+ }
+
+ return result;
+}
+
+/* Cast bytea -> int2 */
+Datum
+bytea_int2(PG_FUNCTION_ARGS)
+{
+ bytea *v = PG_GETARG_BYTEA_PP(0);
+ int len = VARSIZE_ANY_EXHDR(v);
+
+ if (len > sizeof(int16))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("smallint out of range")));
+
+ PG_RETURN_INT16((int16) bytea_integer(v, len));
+}
+
+/* Cast bytea -> int4 */
+Datum
+bytea_int4(PG_FUNCTION_ARGS)
+{
+ bytea *v = PG_GETARG_BYTEA_PP(0);
+ int len = VARSIZE_ANY_EXHDR(v);
+
+ if (len > sizeof(int32))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("integer out of range")));
+
+ PG_RETURN_INT32((int32) bytea_integer(v, len));
+}
+
+/* Cast bytea -> int8 */
+Datum
+bytea_int8(PG_FUNCTION_ARGS)
+{
+ bytea *v = PG_GETARG_BYTEA_PP(0);
+ int len = VARSIZE_ANY_EXHDR(v);
+
+ if (len > sizeof(int64))
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("bigint out of range")));
+
+
+ PG_RETURN_INT64(bytea_integer(v, len));
+}
+
+/* Cast int2 -> bytea; currently just a wrapper for int2send() */
+Datum
+int2_bytea(PG_FUNCTION_ARGS)
+{
+ return int2send(fcinfo);
+}
+
+/* Cast int4 -> bytea; currently just a wrapper for int4send() */
+Datum
+int4_bytea(PG_FUNCTION_ARGS)
+{
+ return int4send(fcinfo);
+}
+
+/* Cast int8 -> bytea; currently just a wrapper for int8send() */
+Datum
+int8_bytea(PG_FUNCTION_ARGS)
+{
+ return int8send(fcinfo);
+}
+
Datum
i2toi4(PG_FUNCTION_ARGS)
{
diff --git a/src/include/catalog/pg_cast.dat b/src/include/catalog/pg_cast.dat
index a26ba34e869..ab46be606f0 100644
--- a/src/include/catalog/pg_cast.dat
+++ b/src/include/catalog/pg_cast.dat
@@ -320,6 +320,20 @@
{ castsource => 'varchar', casttarget => 'name', castfunc => 'name(varchar)',
castcontext => 'i', castmethod => 'f' },
+# Allow explicit coercions between bytea and integer types
+{ castsource => 'int2', casttarget => 'bytea', castfunc => 'bytea(int2)',
+ castcontext => 'e', castmethod => 'f' },
+{ castsource => 'int4', casttarget => 'bytea', castfunc => 'bytea(int4)',
+ castcontext => 'e', castmethod => 'f' },
+{ castsource => 'int8', casttarget => 'bytea', castfunc => 'bytea(int8)',
+ castcontext => 'e', castmethod => 'f' },
+{ castsource => 'bytea', casttarget => 'int2', castfunc => 'int2(bytea)',
+ castcontext => 'e', castmethod => 'f' },
+{ castsource => 'bytea', casttarget => 'int4', castfunc => 'int4(bytea)',
+ castcontext => 'e', castmethod => 'f' },
+{ castsource => 'bytea', casttarget => 'int8', castfunc => 'int8(bytea)',
+ castcontext => 'e', castmethod => 'f' },
+
# Allow explicit coercions between int4 and "char"
{ castsource => 'char', casttarget => 'int4', castfunc => 'int4(char)',
castcontext => 'e', castmethod => 'f' },
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 872cd6e01a3..18187f9375a 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -1164,6 +1164,24 @@
{ oid => '409', descr => 'convert char(n) to name',
proname => 'name', proleakproof => 't', prorettype => 'name',
proargtypes => 'bpchar', prosrc => 'bpchar_name' },
+{ oid => '8577', descr => 'convert int2 to bytea',
+ proname => 'bytea', proleakproof => 't', prorettype => 'bytea',
+ proargtypes => 'int2', prosrc => 'int2_bytea' },
+{ oid => '8578', descr => 'convert int4 to bytea',
+ proname => 'bytea', proleakproof => 't', prorettype => 'bytea',
+ proargtypes => 'int4', prosrc => 'int4_bytea' },
+{ oid => '8579', descr => 'convert int8 to bytea',
+ proname => 'bytea', proleakproof => 't', prorettype => 'bytea',
+ proargtypes => 'int8', prosrc => 'int8_bytea' },
+{ oid => '8580', descr => 'convert bytea to int2',
+ proname => 'int2', proleakproof => 't', prorettype => 'int2',
+ proargtypes => 'bytea', prosrc => 'bytea_int2' },
+{ oid => '8581', descr => 'convert bytea to int4',
+ proname => 'int4', proleakproof => 't', prorettype => 'int4',
+ proargtypes => 'bytea', prosrc => 'bytea_int4' },
+{ oid => '8582', descr => 'convert bytea to int8',
+ proname => 'int8', proleakproof => 't', prorettype => 'int8',
+ proargtypes => 'bytea', prosrc => 'bytea_int8' },
{ oid => '449', descr => 'hash',
proname => 'hashint2', prorettype => 'int4', proargtypes => 'int2',
diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out
index b673642ad1d..462c56b7887 100644
--- a/src/test/regress/expected/opr_sanity.out
+++ b/src/test/regress/expected/opr_sanity.out
@@ -875,6 +875,12 @@ uuid_extract_timestamp(uuid)
uuid_extract_version(uuid)
crc32(bytea)
crc32c(bytea)
+bytea(smallint)
+bytea(integer)
+bytea(bigint)
+int2(bytea)
+int4(bytea)
+int8(bytea)
bytea_larger(bytea,bytea)
bytea_smaller(bytea,bytea)
-- Check that functions without argument are not marked as leakproof.
diff --git a/src/test/regress/expected/strings.out b/src/test/regress/expected/strings.out
index b65bb2d5368..69f1e66aa98 100644
--- a/src/test/regress/expected/strings.out
+++ b/src/test/regress/expected/strings.out
@@ -2690,3 +2690,123 @@ ERROR: invalid Unicode code point: 2FFFFF
SELECT unistr('wrong: \xyz');
ERROR: invalid Unicode escape
HINT: Unicode escapes must be \XXXX, \+XXXXXX, \uXXXX, or \UXXXXXXXX.
+--
+-- Test coercions between bytea and integer types
+--
+SET bytea_output TO hex;
+SELECT 0x1234::int2::bytea;
+ bytea
+--------
+ \x1234
+(1 row)
+
+SELECT 0x12345678::int4::bytea;
+ bytea
+------------
+ \x12345678
+(1 row)
+
+SELECT 0x1122334455667788::int8::bytea;
+ bytea
+--------------------
+ \x1122334455667788
+(1 row)
+
+SELECT ''::bytea::int2 = 0;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT '\x12'::bytea::int2 = 0x12;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT '\x1234'::bytea::int2 = 0x1234;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT '\x123456'::bytea::int2; -- error
+ERROR: smallint out of range
+SELECT ''::bytea::int4 = 0;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT '\x12'::bytea::int4 = 0x12;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT '\x12345678'::bytea::int4 = 0x12345678;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT '\x123456789A'::bytea::int4; -- error
+ERROR: integer out of range
+SELECT ''::bytea::int8 = 0;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT '\x12'::bytea::int8 = 0x12;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT '\x1122334455667788'::bytea::int8 = 0x1122334455667788;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT '\x112233445566778899'::bytea::int8; -- error
+ERROR: bigint out of range
+-- max integer values
+SELECT '\x7FFF'::bytea::int2 = 0x7FFF;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT '\x7FFFFFFF'::bytea::int4 = 0x7FFFFFFF;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT '\x7FFFFFFFFFFFFFFF'::bytea::int8 = 0x7FFFFFFFFFFFFFFF;
+ ?column?
+----------
+ t
+(1 row)
+
+-- min integer values
+SELECT '\x8000'::bytea::int2 = -0x8000;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT '\x80000000'::bytea::int4 = -0x80000000;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT '\x8000000000000000'::bytea::int8 = -0x8000000000000000;
+ ?column?
+----------
+ t
+(1 row)
+
diff --git a/src/test/regress/sql/strings.sql b/src/test/regress/sql/strings.sql
index 8e0f3a0e75f..16cceb5c746 100644
--- a/src/test/regress/sql/strings.sql
+++ b/src/test/regress/sql/strings.sql
@@ -848,3 +848,37 @@ SELECT unistr('wrong: \udb99\u0061');
SELECT unistr('wrong: \U0000db99\U00000061');
SELECT unistr('wrong: \U002FFFFF');
SELECT unistr('wrong: \xyz');
+
+--
+-- Test coercions between bytea and integer types
+--
+SET bytea_output TO hex;
+
+SELECT 0x1234::int2::bytea;
+SELECT 0x12345678::int4::bytea;
+SELECT 0x1122334455667788::int8::bytea;
+
+SELECT ''::bytea::int2 = 0;
+SELECT '\x12'::bytea::int2 = 0x12;
+SELECT '\x1234'::bytea::int2 = 0x1234;
+SELECT '\x123456'::bytea::int2; -- error
+
+SELECT ''::bytea::int4 = 0;
+SELECT '\x12'::bytea::int4 = 0x12;
+SELECT '\x12345678'::bytea::int4 = 0x12345678;
+SELECT '\x123456789A'::bytea::int4; -- error
+
+SELECT ''::bytea::int8 = 0;
+SELECT '\x12'::bytea::int8 = 0x12;
+SELECT '\x1122334455667788'::bytea::int8 = 0x1122334455667788;
+SELECT '\x112233445566778899'::bytea::int8; -- error
+
+-- max integer values
+SELECT '\x7FFF'::bytea::int2 = 0x7FFF;
+SELECT '\x7FFFFFFF'::bytea::int4 = 0x7FFFFFFF;
+SELECT '\x7FFFFFFFFFFFFFFF'::bytea::int8 = 0x7FFFFFFFFFFFFFFF;
+
+-- min integer values
+SELECT '\x8000'::bytea::int2 = -0x8000;
+SELECT '\x80000000'::bytea::int4 = -0x80000000;
+SELECT '\x8000000000000000'::bytea::int8 = -0x8000000000000000;
--
2.47.1
