Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit6da469b

Browse files
committed
Allow casting between bytea and integer types.
This allows smallint, integer, and bigint values to be cast to andfrom bytea. The bytea value is the two's complement representation ofthe integer, with the most significant byte first. For example: 1234::bytea -> \x000004d2 (-1234)::bytea -> \xfffffb2eAuthor: Aleksander Alekseev <aleksander@timescale.com>Reviewed-by: Joel Jacobson <joel@compiler.org>Reviewed-by: Yugo Nagata <nagata@sraoss.co.jp>Reviewed-by: Peter Eisentraut <peter@eisentraut.org>Reviewed-by: Michael Paquier <michael@paquier.xyz>Reviewed-by: Dean Rasheed <dean.a.rasheed@gmail.com>Discussion:https://postgr.es/m/CAJ7c6TPtOp6%2BkFX5QX3fH1SVr7v65uHr-7yEJ%3DGMGQi5uhGtcA%40mail.gmail.com
1 parentd611f8b commit6da469b

File tree

8 files changed

+281
-1
lines changed

8 files changed

+281
-1
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5035,6 +5035,23 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
50355035
</variablelist>
50365036
</para>
50375037

5038+
<para>
5039+
In addition, it is possible to cast integral values to and from type
5040+
<type>bytea</type>. Casting an integer to <type>bytea</type> produces
5041+
2, 4, or 8 bytes, depending on the width of the integer type. The result
5042+
is the two's complement representation of the integer, with the most
5043+
significant byte first. Some examples:
5044+
<programlisting>
5045+
1234::smallint::bytea <lineannotation>\x04d2</lineannotation>
5046+
cast(1234 as bytea) <lineannotation>\x000004d2</lineannotation>
5047+
cast(-1234 as bytea) <lineannotation>\xfffffb2e</lineannotation>
5048+
'\x8000'::bytea::smallint <lineannotation>-32768</lineannotation>
5049+
'\x8000'::bytea::integer <lineannotation>32768</lineannotation>
5050+
</programlisting>
5051+
Casting a <type>bytea</type> to an integer will raise an error if the
5052+
length of the <type>bytea</type> exceeds the width of the integer type.
5053+
</para>
5054+
50385055
<para>
50395056
See also the aggregate function <function>string_agg</function> in
50405057
<xref linkend="functions-aggregate"/> and the large object functions

‎src/backend/utils/adt/varlena.c

Lines changed: 96 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4057,6 +4057,102 @@ bytea_sortsupport(PG_FUNCTION_ARGS)
40574057
PG_RETURN_VOID();
40584058
}
40594059

4060+
/* Cast bytea -> int2 */
4061+
Datum
4062+
bytea_int2(PG_FUNCTION_ARGS)
4063+
{
4064+
bytea*v=PG_GETARG_BYTEA_PP(0);
4065+
intlen=VARSIZE_ANY_EXHDR(v);
4066+
uint16result;
4067+
4068+
/* Check that the byte array is not too long */
4069+
if (len>sizeof(result))
4070+
ereport(ERROR,
4071+
errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
4072+
errmsg("smallint out of range"));
4073+
4074+
/* Convert it to an integer; most significant bytes come first */
4075+
result=0;
4076+
for (inti=0;i<len;i++)
4077+
{
4078+
result <<=BITS_PER_BYTE;
4079+
result |= ((unsignedchar*)VARDATA_ANY(v))[i];
4080+
}
4081+
4082+
PG_RETURN_INT16(result);
4083+
}
4084+
4085+
/* Cast bytea -> int4 */
4086+
Datum
4087+
bytea_int4(PG_FUNCTION_ARGS)
4088+
{
4089+
bytea*v=PG_GETARG_BYTEA_PP(0);
4090+
intlen=VARSIZE_ANY_EXHDR(v);
4091+
uint32result;
4092+
4093+
/* Check that the byte array is not too long */
4094+
if (len>sizeof(result))
4095+
ereport(ERROR,
4096+
errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
4097+
errmsg("integer out of range"));
4098+
4099+
/* Convert it to an integer; most significant bytes come first */
4100+
result=0;
4101+
for (inti=0;i<len;i++)
4102+
{
4103+
result <<=BITS_PER_BYTE;
4104+
result |= ((unsignedchar*)VARDATA_ANY(v))[i];
4105+
}
4106+
4107+
PG_RETURN_INT32(result);
4108+
}
4109+
4110+
/* Cast bytea -> int8 */
4111+
Datum
4112+
bytea_int8(PG_FUNCTION_ARGS)
4113+
{
4114+
bytea*v=PG_GETARG_BYTEA_PP(0);
4115+
intlen=VARSIZE_ANY_EXHDR(v);
4116+
uint64result;
4117+
4118+
/* Check that the byte array is not too long */
4119+
if (len>sizeof(result))
4120+
ereport(ERROR,
4121+
errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
4122+
errmsg("bigint out of range"));
4123+
4124+
/* Convert it to an integer; most significant bytes come first */
4125+
result=0;
4126+
for (inti=0;i<len;i++)
4127+
{
4128+
result <<=BITS_PER_BYTE;
4129+
result |= ((unsignedchar*)VARDATA_ANY(v))[i];
4130+
}
4131+
4132+
PG_RETURN_INT64(result);
4133+
}
4134+
4135+
/* Cast int2 -> bytea; can just use int2send() */
4136+
Datum
4137+
int2_bytea(PG_FUNCTION_ARGS)
4138+
{
4139+
returnint2send(fcinfo);
4140+
}
4141+
4142+
/* Cast int4 -> bytea; can just use int4send() */
4143+
Datum
4144+
int4_bytea(PG_FUNCTION_ARGS)
4145+
{
4146+
returnint4send(fcinfo);
4147+
}
4148+
4149+
/* Cast int8 -> bytea; can just use int8send() */
4150+
Datum
4151+
int8_bytea(PG_FUNCTION_ARGS)
4152+
{
4153+
returnint8send(fcinfo);
4154+
}
4155+
40604156
/*
40614157
* appendStringInfoText
40624158
*

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -57,6 +57,6 @@
5757
*/
5858

5959
/*yyyymmddN */
60-
#defineCATALOG_VERSION_NO202503031
60+
#defineCATALOG_VERSION_NO202503071
6161

6262
#endif

‎src/include/catalog/pg_cast.dat

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -320,6 +320,20 @@
320320
{ castsource => 'varchar', casttarget => 'name', castfunc => 'name(varchar)',
321321
castcontext => 'i', castmethod => 'f' },
322322

323+
# Allow explicit coercions between bytea and integer types
324+
{ castsource => 'int2', casttarget => 'bytea', castfunc => 'bytea(int2)',
325+
castcontext => 'e', castmethod => 'f' },
326+
{ castsource => 'int4', casttarget => 'bytea', castfunc => 'bytea(int4)',
327+
castcontext => 'e', castmethod => 'f' },
328+
{ castsource => 'int8', casttarget => 'bytea', castfunc => 'bytea(int8)',
329+
castcontext => 'e', castmethod => 'f' },
330+
{ castsource => 'bytea', casttarget => 'int2', castfunc => 'int2(bytea)',
331+
castcontext => 'e', castmethod => 'f' },
332+
{ castsource => 'bytea', casttarget => 'int4', castfunc => 'int4(bytea)',
333+
castcontext => 'e', castmethod => 'f' },
334+
{ castsource => 'bytea', casttarget => 'int8', castfunc => 'int8(bytea)',
335+
castcontext => 'e', castmethod => 'f' },
336+
323337
# Allow explicit coercions between int4 and "char"
324338
{ castsource => 'char', casttarget => 'int4', castfunc => 'int4(char)',
325339
castcontext => 'e', castmethod => 'f' },

‎src/include/catalog/pg_proc.dat

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1165,6 +1165,25 @@
11651165
proname => 'name', proleakproof => 't', prorettype => 'name',
11661166
proargtypes => 'bpchar', prosrc => 'bpchar_name' },
11671167

1168+
{ oid => '8577', descr => 'convert int2 to bytea',
1169+
proname => 'bytea', proleakproof => 't', prorettype => 'bytea',
1170+
proargtypes => 'int2', prosrc => 'int2_bytea' },
1171+
{ oid => '8578', descr => 'convert int4 to bytea',
1172+
proname => 'bytea', proleakproof => 't', prorettype => 'bytea',
1173+
proargtypes => 'int4', prosrc => 'int4_bytea' },
1174+
{ oid => '8579', descr => 'convert int8 to bytea',
1175+
proname => 'bytea', proleakproof => 't', prorettype => 'bytea',
1176+
proargtypes => 'int8', prosrc => 'int8_bytea' },
1177+
{ oid => '8580', descr => 'convert bytea to int2',
1178+
proname => 'int2', prorettype => 'int2',
1179+
proargtypes => 'bytea', prosrc => 'bytea_int2' },
1180+
{ oid => '8581', descr => 'convert bytea to int4',
1181+
proname => 'int4', prorettype => 'int4',
1182+
proargtypes => 'bytea', prosrc => 'bytea_int4' },
1183+
{ oid => '8582', descr => 'convert bytea to int8',
1184+
proname => 'int8', prorettype => 'int8',
1185+
proargtypes => 'bytea', prosrc => 'bytea_int8' },
1186+
11681187
{ oid => '449', descr => 'hash',
11691188
proname => 'hashint2', prorettype => 'int4', proargtypes => 'int2',
11701189
prosrc => 'hashint2' },

‎src/test/regress/expected/opr_sanity.out

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -875,6 +875,9 @@ uuid_extract_timestamp(uuid)
875875
uuid_extract_version(uuid)
876876
crc32(bytea)
877877
crc32c(bytea)
878+
bytea(smallint)
879+
bytea(integer)
880+
bytea(bigint)
878881
bytea_larger(bytea,bytea)
879882
bytea_smaller(bytea,bytea)
880883
-- Check that functions without argument are not marked as leakproof.

‎src/test/regress/expected/strings.out

Lines changed: 102 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2358,6 +2358,108 @@ SELECT set_byte('\x1234567890abcdef00'::bytea, 7, 11);
23582358

23592359
SELECT set_byte('\x1234567890abcdef00'::bytea, 99, 11); -- error
23602360
ERROR: index 99 out of valid range, 0..8
2361+
--
2362+
-- conversions between bytea and integer types
2363+
--
2364+
SELECT 0x1234::int2::bytea AS "\x1234", (-0x1234)::int2::bytea AS "\xedcc";
2365+
\x1234 | \xedcc
2366+
--------+--------
2367+
\x1234 | \xedcc
2368+
(1 row)
2369+
2370+
SELECT 0x12345678::int4::bytea AS "\x12345678", (-0x12345678)::int4::bytea AS "\xedcba988";
2371+
\x12345678 | \xedcba988
2372+
------------+------------
2373+
\x12345678 | \xedcba988
2374+
(1 row)
2375+
2376+
SELECT 0x1122334455667788::int8::bytea AS "\x1122334455667788",
2377+
(-0x1122334455667788)::int8::bytea AS "\xeeddccbbaa998878";
2378+
\x1122334455667788 | \xeeddccbbaa998878
2379+
--------------------+--------------------
2380+
\x1122334455667788 | \xeeddccbbaa998878
2381+
(1 row)
2382+
2383+
SELECT ''::bytea::int2 AS "0";
2384+
0
2385+
---
2386+
0
2387+
(1 row)
2388+
2389+
SELECT '\x12'::bytea::int2 AS "18";
2390+
18
2391+
----
2392+
18
2393+
(1 row)
2394+
2395+
SELECT '\x1234'::bytea::int2 AS "4460";
2396+
4460
2397+
------
2398+
4660
2399+
(1 row)
2400+
2401+
SELECT '\x123456'::bytea::int2; -- error
2402+
ERROR: smallint out of range
2403+
SELECT ''::bytea::int4 AS "0";
2404+
0
2405+
---
2406+
0
2407+
(1 row)
2408+
2409+
SELECT '\x12'::bytea::int4 AS "18";
2410+
18
2411+
----
2412+
18
2413+
(1 row)
2414+
2415+
SELECT '\x12345678'::bytea::int4 AS "305419896";
2416+
305419896
2417+
-----------
2418+
305419896
2419+
(1 row)
2420+
2421+
SELECT '\x123456789A'::bytea::int4; -- error
2422+
ERROR: integer out of range
2423+
SELECT ''::bytea::int8 AS "0";
2424+
0
2425+
---
2426+
0
2427+
(1 row)
2428+
2429+
SELECT '\x12'::bytea::int8 AS "18";
2430+
18
2431+
----
2432+
18
2433+
(1 row)
2434+
2435+
SELECT '\x1122334455667788'::bytea::int8 AS "1234605616436508552";
2436+
1234605616436508552
2437+
---------------------
2438+
1234605616436508552
2439+
(1 row)
2440+
2441+
SELECT '\x112233445566778899'::bytea::int8; -- error
2442+
ERROR: bigint out of range
2443+
-- min/max integer values
2444+
SELECT '\x8000'::bytea::int2 AS "-32768", '\x7FFF'::bytea::int2 AS "32767";
2445+
-32768 | 32767
2446+
--------+-------
2447+
-32768 | 32767
2448+
(1 row)
2449+
2450+
SELECT '\x80000000'::bytea::int4 AS "-2147483648", '\x7FFFFFFF'::bytea::int4 AS "2147483647";
2451+
-2147483648 | 2147483647
2452+
-------------+------------
2453+
-2147483648 | 2147483647
2454+
(1 row)
2455+
2456+
SELECT '\x8000000000000000'::bytea::int8 AS "-9223372036854775808",
2457+
'\x7FFFFFFFFFFFFFFF'::bytea::int8 AS "9223372036854775807";
2458+
-9223372036854775808 | 9223372036854775807
2459+
----------------------+---------------------
2460+
-9223372036854775808 | 9223372036854775807
2461+
(1 row)
2462+
23612463
--
23622464
-- test behavior of escape_string_warning and standard_conforming_strings options
23632465
--

‎src/test/regress/sql/strings.sql

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -751,6 +751,35 @@ SELECT get_byte('\x1234567890abcdef00'::bytea, 99); -- error
751751
SELECT set_byte('\x1234567890abcdef00'::bytea,7,11);
752752
SELECT set_byte('\x1234567890abcdef00'::bytea,99,11);-- error
753753

754+
--
755+
-- conversions between bytea and integer types
756+
--
757+
SELECT 0x1234::int2::byteaAS"\x1234", (-0x1234)::int2::byteaAS"\xedcc";
758+
SELECT 0x12345678::int4::byteaAS"\x12345678", (-0x12345678)::int4::byteaAS"\xedcba988";
759+
SELECT 0x1122334455667788::int8::byteaAS"\x1122334455667788",
760+
(-0x1122334455667788)::int8::byteaAS"\xeeddccbbaa998878";
761+
762+
SELECT''::bytea::int2AS"0";
763+
SELECT'\x12'::bytea::int2AS"18";
764+
SELECT'\x1234'::bytea::int2AS"4460";
765+
SELECT'\x123456'::bytea::int2;-- error
766+
767+
SELECT''::bytea::int4AS"0";
768+
SELECT'\x12'::bytea::int4AS"18";
769+
SELECT'\x12345678'::bytea::int4AS"305419896";
770+
SELECT'\x123456789A'::bytea::int4;-- error
771+
772+
SELECT''::bytea::int8AS"0";
773+
SELECT'\x12'::bytea::int8AS"18";
774+
SELECT'\x1122334455667788'::bytea::int8AS"1234605616436508552";
775+
SELECT'\x112233445566778899'::bytea::int8;-- error
776+
777+
-- min/max integer values
778+
SELECT'\x8000'::bytea::int2AS"-32768",'\x7FFF'::bytea::int2AS"32767";
779+
SELECT'\x80000000'::bytea::int4AS"-2147483648",'\x7FFFFFFF'::bytea::int4AS"2147483647";
780+
SELECT'\x8000000000000000'::bytea::int8AS"-9223372036854775808",
781+
'\x7FFFFFFFFFFFFFFF'::bytea::int8AS"9223372036854775807";
782+
754783
--
755784
-- test behavior of escape_string_warning and standard_conforming_strings options
756785
--

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp