Skip to content

add ASCII SQL function #1179

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
gaoxueyu merged 6 commits into from
Jan 29, 2026
Merged

add ASCII SQL function #1179

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
6 commits
Select commit Hold shift + click to select a range
27dc720
created ora_ascii C function for ASCII SQL function
pierreforstmann Jan 26, 2026
7c20d9a
fix ora_ascii: BINARY_FLOAT mapped to float4 and BINARY_DOUBLE mapped…
pierreforstmann Jan 27, 2026
e0951a6
add test for ASCII function
pierreforstmann Jan 27, 2026
9f16e98
fixed ASCII SQL function PR coderabbit comments
pierreforstmann Jan 27, 2026
2b53c49
removed nls_date_format, nls_timestamp, nls_timestamp_tz variable dec…
pierreforstmann Jan 27, 2026
bb181d1
fix empty string handling for ASCII SQL function
pierreforstmann Jan 27, 2026
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
3 changes: 2 additions & 1 deletion contrib/ivorysql_ora/Makefile
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -73,7 +73,8 @@ ORA_REGRESS = \
ora_like_operator \
ora_xml_functions \
dbms_utility \
ora_dbms_output
ora_dbms_output \
ora_ascii

SHLIB_LINK += -lxml2

Expand Down
58 changes: 58 additions & 0 deletions contrib/ivorysql_ora/expected/ora_ascii.out
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,58 @@
--
-- ora_ascii.sql: test ASCII function
--
--
select ascii(321) from dual;
ascii
-------
51
(1 row)

select ascii(213.3f) from dual;
ascii
-------
50
(1 row)

select ascii(123.4d) from dual;
ascii
-------
49
(1 row)

select ascii('abc') from dual;
ascii
-------
97
(1 row)

select ascii('xyz') from dual;
ascii
-------
120
(1 row)

select ascii(to_char('2026-01-01'::date,'DD-MON-YYYY HH24:MI:SS')) from dual;
ascii
-------
48
(1 row)

select ascii(to_char('2026-01-11 01:02:03.00'::timestamp,'DD-MON-YYYY HH24:MI:SS.FF6')) from dual;
ascii
-------
49
(1 row)

select ascii(to_char('2026-01-22 01:02:03.00 +01:00'::timestamptz,'DD-MON-YYYY HH24:MI:SS TZH:TZM')) from dual;
ascii
-------
50
(1 row)

select ascii('') is null from dual;
?column?
----------
t
(1 row)

21 changes: 21 additions & 0 deletions contrib/ivorysql_ora/sql/ora_ascii.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,21 @@
--
-- ora_ascii.sql: test ASCII function
--
--
select ascii(321) from dual;

select ascii(213.3f) from dual;

select ascii(123.4d) from dual;

select ascii('abc') from dual;

select ascii('xyz') from dual;

select ascii(to_char('2026-01-01'::date,'DD-MON-YYYY HH24:MI:SS')) from dual;

select ascii(to_char('2026-01-11 01:02:03.00'::timestamp,'DD-MON-YYYY HH24:MI:SS.FF6')) from dual;

select ascii(to_char('2026-01-22 01:02:03.00 +01:00'::timestamptz,'DD-MON-YYYY HH24:MI:SS TZH:TZM')) from dual;

select ascii('') is null from dual;
58 changes: 58 additions & 0 deletions contrib/ivorysql_ora/src/builtin_functions/builtin_functions--1.0.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -1384,3 +1384,61 @@ BEGIN
END;
$$ LANGUAGE plisql SECURITY INVOKER;
/* End - SYS_CONTEXT */

/* Begin - ASCII */
CREATE FUNCTION sys.ascii(sys.number)
RETURNS INT
AS 'MODULE_PATHNAME','ora_ascii'
LANGUAGE C
STRICT
PARALLEL SAFE
IMMUTABLE;

CREATE FUNCTION sys.ascii(sys.binary_float)
RETURNS INT
AS 'MODULE_PATHNAME','ora_ascii'
LANGUAGE C
STRICT
PARALLEL SAFE
IMMUTABLE;

CREATE FUNCTION sys.ascii(sys.binary_double)
RETURNS INT
AS 'MODULE_PATHNAME','ora_ascii'
LANGUAGE C
STRICT
PARALLEL SAFE
IMMUTABLE;

CREATE FUNCTION sys.ascii(sys.oravarcharchar)
RETURNS INT
AS 'MODULE_PATHNAME','ora_ascii'
LANGUAGE C
STRICT
PARALLEL SAFE
IMMUTABLE;

CREATE FUNCTION sys.ascii(sys.oradate)
RETURNS INT
AS 'MODULE_PATHNAME','ora_ascii'
LANGUAGE C
STRICT
PARALLEL SAFE
STABLE;

CREATE FUNCTION sys.ascii(sys.oratimestamp)
RETURNS INT
AS 'MODULE_PATHNAME','ora_ascii'
LANGUAGE C
STRICT
PARALLEL SAFE
STABLE;

CREATE FUNCTION sys.ascii(sys.oratimestamptz)
RETURNS INT
AS 'MODULE_PATHNAME','ora_ascii'
LANGUAGE C
STRICT
PARALLEL SAFE
STABLE;
/* End - ASCII */
125 changes: 125 additions & 0 deletions contrib/ivorysql_ora/src/builtin_functions/character_datatype_functions.c
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -42,6 +42,12 @@

#include "../include/common_datatypes.h"

#include "catalog/pg_type.h"
#include "utils/date.h"
#include "utils/timestamp.h"

#include "utils/guc.h"

PG_FUNCTION_INFO_V1(oracharlen);
PG_FUNCTION_INFO_V1(oracharoctetlen);
PG_FUNCTION_INFO_V1(oravarcharlen);
Expand All @@ -68,6 +74,7 @@ PG_FUNCTION_INFO_V1(oracle_instr_4);
PG_FUNCTION_INFO_V1(ora_asciistr);
PG_FUNCTION_INFO_V1(ora_to_multi_byte);
PG_FUNCTION_INFO_V1(ora_to_single_byte);
PG_FUNCTION_INFO_V1(ora_ascii);

#define PG_STR_GET_TEXT(str_) \
DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(str_)))
Expand Down Expand Up @@ -2316,3 +2323,121 @@ ora_to_single_byte(PG_FUNCTION_ARGS)

PG_RETURN_TEXT_P(dst);
}



/*******************************************************************
* ora_ascii
*
* Purpose:
* Implementation of Oracle ASCII function.
*
* It takes as input parameter:
* - a number,
* - a binary float,
* - a binary double,
* - a date,
* - a timestamp
* - a timestamp with time zone
* - a string
*
* and returns ASCII codepoint of the first character
* of the corresponding string.
*
*******************************************************************/

/*
* cannot find include files
*/
extern Datum binary_float_out(PG_FUNCTION_ARGS);
extern Datum binary_double_out(PG_FUNCTION_ARGS);

Datum
ora_ascii(PG_FUNCTION_ARGS)
{
Oid argtype = get_fn_expr_argtype(fcinfo->flinfo, 0);
char *str = NULL;

if (PG_ARGISNULL(0))
PG_RETURN_NULL();


switch (argtype)
{
/*
* Oracle data types only defined in src/bin/psql/psqlplus.h
* and in src/include/catalog/pg_type_d.h
*/
case NUMBEROID: {
/* number */
Numeric val = PG_GETARG_NUMERIC(0);
str = DatumGetCString(DirectFunctionCall1(numeric_out, NumericGetDatum(val)));
break;
Comment thread
coderabbitai[bot] marked this conversation as resolved.
}
case BINARY_FLOATOID: {
float4 val = PG_GETARG_FLOAT4(0);
str = DatumGetCString(DirectFunctionCall1(binary_float_out, Float4GetDatum(val)));
break;
}
case BINARY_DOUBLEOID: {
float8 val = PG_GETARG_FLOAT8(0);
str = DatumGetCString(DirectFunctionCall1(binary_double_out, Float8GetDatum(val)));
break;
}
Comment thread
coderabbitai[bot] marked this conversation as resolved.
case ORACHARCHAROID:
case ORAVARCHARCHAROID : {
/* char, varchar, varchar2 */
text *txt = PG_GETARG_TEXT_PP(0);
str = text_to_cstring(txt);
break;
}
case ORADATEOID: {
Timestamp timestamp = PG_GETARG_TIMESTAMP(0);
text *date_str;

date_str = DatumGetTextP(DirectFunctionCall2(timestamp_to_char,
TimestampGetDatum(timestamp),
PointerGetDatum(cstring_to_text(nls_date_format))));

str = text_to_cstring(date_str);
break;
}
case ORATIMESTAMPOID: {
Timestamp val = PG_GETARG_TIMESTAMP(0);
text *timestamp_str;

timestamp_str = DatumGetTextP(DirectFunctionCall2(timestamp_to_char,
TimestampGetDatum(val),
PointerGetDatum(cstring_to_text(nls_timestamp_format))));
str = text_to_cstring(timestamp_str);
break;
}
case ORATIMESTAMPTZOID: {
TimestampTz val = PG_GETARG_TIMESTAMPTZ(0);
text *timestamptz_str;

timestamptz_str = DatumGetTextP(DirectFunctionCall2(timestamptz_to_char,
TimestampTzGetDatum(val),
PointerGetDatum(cstring_to_text(nls_timestamp_tz_format))));
str = text_to_cstring(timestamptz_str);
break;
}

default: {
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("Data type oid=%d not yet implemented", argtype)));
break;
}
}

if (str == NULL || str[0] == '\0') {
/* 1. Set the null flag */
fcinfo->isnull = true;

/* 2. Return a placeholder value */
PG_RETURN_VOID();
}

PG_RETURN_INT32((unsigned char) str[0]);
}