RAWTOHEX function implementation

bigplaice · bigplaice · commit 06a29b01994b · 2026-04-15T05:43:35.000Z
diff --git a/contrib/ivorysql_ora/expected/ora_raw_long.out b/contrib/ivorysql_ora/expected/ora_raw_long.out
@@ -175,3 +175,52 @@ SELECT * FROM LONG_TEXT ORDER BY II DESC;
 (3 rows)
 
 DROP TABLE LONG_TEXT;
+
+-- rawtohex
+SELECT sys.rawtohex('\xDEADBEEF'::bytea);
+ rawtohex
+----------
+ DEADBEEF
+(1 row)
+
+SELECT sys.rawtohex('\xFF'::raw);
+ rawtohex
+----------
+ FF
+(1 row)
+
+SELECT sys.rawtohex('hello'::text);
+  rawtohex
+------------
+ 68656C6C6F
+(1 row)
+
+SELECT sys.rawtohex('hello'::varchar2);
+  rawtohex
+------------
+ 68656C6C6F
+(1 row)
+
+SELECT sys.rawtohex(sys.hextoraw('DEADBEEF'));
+ rawtohex
+----------
+ DEADBEEF
+(1 row)
+
+SELECT sys.rawtohex(NULL) IS NULL;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT sys.rawtohex('') IS NULL;
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT sys.rawtohex('\x'::bytea) IS NULL;
+ ?column?
+----------
+ t
+(1 row)
diff --git a/contrib/ivorysql_ora/sql/ora_raw_long.sql b/contrib/ivorysql_ora/sql/ora_raw_long.sql
@@ -44,3 +44,13 @@ SELECT * FROM LONG_TEXT ORDER BY II DESC;
 
 DROP TABLE LONG_TEXT;
 
+-- rawtohex
+SELECT sys.rawtohex('\xDEADBEEF'::bytea);
+SELECT sys.rawtohex('\xFF'::raw);
+SELECT sys.rawtohex('hello'::text);
+SELECT sys.rawtohex('hello'::varchar2);
+SELECT sys.rawtohex(sys.hextoraw('DEADBEEF'));
+SELECT sys.rawtohex(NULL) IS NULL;
+SELECT sys.rawtohex('') IS NULL;
+SELECT sys.rawtohex('\x'::bytea) IS NULL;
+
diff --git a/contrib/ivorysql_ora/src/builtin_functions/builtin_functions--1.0.sql b/contrib/ivorysql_ora/src/builtin_functions/builtin_functions--1.0.sql
@@ -952,6 +952,23 @@ PARALLEL SAFE
 STRICT
 IMMUTABLE;
 
+/* support rawtohex function for oracle compatibility */
+CREATE OR REPLACE FUNCTION sys.rawtohex(bytea)
+RETURNS varchar2
+AS $$ SELECT CASE WHEN pg_catalog.octet_length($1) > 0 THEN upper(pg_catalog.encode($1, 'hex'))::varchar2 END; $$ 
+LANGUAGE SQL
+PARALLEL SAFE
+STRICT
+IMMUTABLE;
+
+CREATE OR REPLACE FUNCTION sys.rawtohex(text)
+RETURNS varchar2
+AS $$ SELECT CASE WHEN pg_catalog.octet_length($1) > 0 THEN upper(pg_catalog.encode($1::bytea, 'hex'))::varchar2 END; $$ 
+LANGUAGE SQL
+PARALLEL SAFE
+STRICT
+IMMUTABLE;
+
 
 /***************************************************************
  *
