Skip to content

Commit de59571

Browse files
gaoxueyugaoxueyu
authored
Merge pull request #1179 from pierreforstmann/patch2
add ASCII SQL function
2 parents 427869d + bb181d1 commit de59571

File tree

5 files changed

+264
-1
lines changed

5 files changed

+264
-1
lines changed

‎contrib/ivorysql_ora/Makefile‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -73,7 +73,8 @@ ORA_REGRESS = \
7373
ora_like_operator \
7474
ora_xml_functions \
7575
dbms_utility \
76-
ora_dbms_output
76+
ora_dbms_output \
77+
ora_ascii
7778

7879
SHLIB_LINK += -lxml2
7980

‎contrib/ivorysql_ora/expected/ora_ascii.out‎

Lines changed: 58 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,58 @@
1+
--
2+
-- ora_ascii.sql: test ASCII function
3+
--
4+
--
5+
select ascii(321) from dual;
6+
ascii
7+
-------
8+
51
9+
(1 row)
10+
11+
select ascii(213.3f) from dual;
12+
ascii
13+
-------
14+
50
15+
(1 row)
16+
17+
select ascii(123.4d) from dual;
18+
ascii
19+
-------
20+
49
21+
(1 row)
22+
23+
select ascii('abc') from dual;
24+
ascii
25+
-------
26+
97
27+
(1 row)
28+
29+
select ascii('xyz') from dual;
30+
ascii
31+
-------
32+
120
33+
(1 row)
34+
35+
select ascii(to_char('2026-01-01'::date,'DD-MON-YYYY HH24:MI:SS')) from dual;
36+
ascii
37+
-------
38+
48
39+
(1 row)
40+
41+
select ascii(to_char('2026-01-11 01:02:03.00'::timestamp,'DD-MON-YYYY HH24:MI:SS.FF6')) from dual;
42+
ascii
43+
-------
44+
49
45+
(1 row)
46+
47+
select ascii(to_char('2026-01-22 01:02:03.00 +01:00'::timestamptz,'DD-MON-YYYY HH24:MI:SS TZH:TZM')) from dual;
48+
ascii
49+
-------
50+
50
51+
(1 row)
52+
53+
select ascii('') is null from dual;
54+
?column?
55+
----------
56+
t
57+
(1 row)
58+

‎contrib/ivorysql_ora/sql/ora_ascii.sql‎

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,21 @@
1+
--
2+
-- ora_ascii.sql: test ASCII function
3+
--
4+
--
5+
select ascii(321) from dual;
6+
7+
select ascii(213.3f) from dual;
8+
9+
select ascii(123.4d) from dual;
10+
11+
select ascii('abc') from dual;
12+
13+
select ascii('xyz') from dual;
14+
15+
select ascii(to_char('2026-01-01'::date,'DD-MON-YYYY HH24:MI:SS')) from dual;
16+
17+
select ascii(to_char('2026-01-11 01:02:03.00'::timestamp,'DD-MON-YYYY HH24:MI:SS.FF6')) from dual;
18+
19+
select ascii(to_char('2026-01-22 01:02:03.00 +01:00'::timestamptz,'DD-MON-YYYY HH24:MI:SS TZH:TZM')) from dual;
20+
21+
select ascii('') is null from dual;

‎contrib/ivorysql_ora/src/builtin_functions/builtin_functions--1.0.sql‎

Lines changed: 58 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1384,3 +1384,61 @@ BEGIN
13841384
END;
13851385
$$ LANGUAGE plisql SECURITY INVOKER;
13861386
/* End - SYS_CONTEXT */
1387+
1388+
/* Begin - ASCII */
1389+
CREATE FUNCTION sys.ascii(sys.number)
1390+
RETURNS INT
1391+
AS 'MODULE_PATHNAME','ora_ascii'
1392+
LANGUAGE C
1393+
STRICT
1394+
PARALLEL SAFE
1395+
IMMUTABLE;
1396+
1397+
CREATE FUNCTION sys.ascii(sys.binary_float)
1398+
RETURNS INT
1399+
AS 'MODULE_PATHNAME','ora_ascii'
1400+
LANGUAGE C
1401+
STRICT
1402+
PARALLEL SAFE
1403+
IMMUTABLE;
1404+
1405+
CREATE FUNCTION sys.ascii(sys.binary_double)
1406+
RETURNS INT
1407+
AS 'MODULE_PATHNAME','ora_ascii'
1408+
LANGUAGE C
1409+
STRICT
1410+
PARALLEL SAFE
1411+
IMMUTABLE;
1412+
1413+
CREATE FUNCTION sys.ascii(sys.oravarcharchar)
1414+
RETURNS INT
1415+
AS 'MODULE_PATHNAME','ora_ascii'
1416+
LANGUAGE C
1417+
STRICT
1418+
PARALLEL SAFE
1419+
IMMUTABLE;
1420+
1421+
CREATE FUNCTION sys.ascii(sys.oradate)
1422+
RETURNS INT
1423+
AS 'MODULE_PATHNAME','ora_ascii'
1424+
LANGUAGE C
1425+
STRICT
1426+
PARALLEL SAFE
1427+
STABLE;
1428+
1429+
CREATE FUNCTION sys.ascii(sys.oratimestamp)
1430+
RETURNS INT
1431+
AS 'MODULE_PATHNAME','ora_ascii'
1432+
LANGUAGE C
1433+
STRICT
1434+
PARALLEL SAFE
1435+
STABLE;
1436+
1437+
CREATE FUNCTION sys.ascii(sys.oratimestamptz)
1438+
RETURNS INT
1439+
AS 'MODULE_PATHNAME','ora_ascii'
1440+
LANGUAGE C
1441+
STRICT
1442+
PARALLEL SAFE
1443+
STABLE;
1444+
/* End - ASCII */

‎contrib/ivorysql_ora/src/builtin_functions/character_datatype_functions.c‎

Lines changed: 125 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -42,6 +42,12 @@
4242

4343
#include "../include/common_datatypes.h"
4444

45+
#include "catalog/pg_type.h"
46+
#include "utils/date.h"
47+
#include "utils/timestamp.h"
48+
49+
#include "utils/guc.h"
50+
4551
PG_FUNCTION_INFO_V1(oracharlen);
4652
PG_FUNCTION_INFO_V1(oracharoctetlen);
4753
PG_FUNCTION_INFO_V1(oravarcharlen);
@@ -68,6 +74,7 @@ PG_FUNCTION_INFO_V1(oracle_instr_4);
6874
PG_FUNCTION_INFO_V1(ora_asciistr);
6975
PG_FUNCTION_INFO_V1(ora_to_multi_byte);
7076
PG_FUNCTION_INFO_V1(ora_to_single_byte);
77+
PG_FUNCTION_INFO_V1(ora_ascii);
7178

7279
#define PG_STR_GET_TEXT(str_) \
7380
DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(str_)))
@@ -2316,3 +2323,121 @@ ora_to_single_byte(PG_FUNCTION_ARGS)
23162323

23172324
PG_RETURN_TEXT_P(dst);
23182325
}
2326+
2327+
2328+
2329+
/*******************************************************************
2330+
* ora_ascii
2331+
*
2332+
* Purpose:
2333+
* Implementation of Oracle ASCII function.
2334+
*
2335+
* It takes as input parameter:
2336+
* - a number,
2337+
* - a binary float,
2338+
* - a binary double,
2339+
* - a date,
2340+
* - a timestamp
2341+
* - a timestamp with time zone
2342+
* - a string
2343+
*
2344+
* and returns ASCII codepoint of the first character
2345+
* of the corresponding string.
2346+
*
2347+
*******************************************************************/
2348+
2349+
/*
2350+
* cannot find include files
2351+
*/
2352+
extern Datum binary_float_out(PG_FUNCTION_ARGS);
2353+
extern Datum binary_double_out(PG_FUNCTION_ARGS);
2354+
2355+
Datum
2356+
ora_ascii(PG_FUNCTION_ARGS)
2357+
{
2358+
Oid argtype = get_fn_expr_argtype(fcinfo->flinfo, 0);
2359+
char *str = NULL;
2360+
2361+
if (PG_ARGISNULL(0))
2362+
PG_RETURN_NULL();
2363+
2364+
2365+
switch (argtype)
2366+
{
2367+
/*
2368+
* Oracle data types only defined in src/bin/psql/psqlplus.h
2369+
* and in src/include/catalog/pg_type_d.h
2370+
*/
2371+
case NUMBEROID: {
2372+
/* number */
2373+
Numeric val = PG_GETARG_NUMERIC(0);
2374+
str = DatumGetCString(DirectFunctionCall1(numeric_out, NumericGetDatum(val)));
2375+
break;
2376+
}
2377+
case BINARY_FLOATOID: {
2378+
float4 val = PG_GETARG_FLOAT4(0);
2379+
str = DatumGetCString(DirectFunctionCall1(binary_float_out, Float4GetDatum(val)));
2380+
break;
2381+
}
2382+
case BINARY_DOUBLEOID: {
2383+
float8 val = PG_GETARG_FLOAT8(0);
2384+
str = DatumGetCString(DirectFunctionCall1(binary_double_out, Float8GetDatum(val)));
2385+
break;
2386+
}
2387+
case ORACHARCHAROID:
2388+
case ORAVARCHARCHAROID : {
2389+
/* char, varchar, varchar2 */
2390+
text *txt = PG_GETARG_TEXT_PP(0);
2391+
str = text_to_cstring(txt);
2392+
break;
2393+
}
2394+
case ORADATEOID: {
2395+
Timestamp timestamp = PG_GETARG_TIMESTAMP(0);
2396+
text *date_str;
2397+
2398+
date_str = DatumGetTextP(DirectFunctionCall2(timestamp_to_char,
2399+
TimestampGetDatum(timestamp),
2400+
PointerGetDatum(cstring_to_text(nls_date_format))));
2401+
2402+
str = text_to_cstring(date_str);
2403+
break;
2404+
}
2405+
case ORATIMESTAMPOID: {
2406+
Timestamp val = PG_GETARG_TIMESTAMP(0);
2407+
text *timestamp_str;
2408+
2409+
timestamp_str = DatumGetTextP(DirectFunctionCall2(timestamp_to_char,
2410+
TimestampGetDatum(val),
2411+
PointerGetDatum(cstring_to_text(nls_timestamp_format))));
2412+
str = text_to_cstring(timestamp_str);
2413+
break;
2414+
}
2415+
case ORATIMESTAMPTZOID: {
2416+
TimestampTz val = PG_GETARG_TIMESTAMPTZ(0);
2417+
text *timestamptz_str;
2418+
2419+
timestamptz_str = DatumGetTextP(DirectFunctionCall2(timestamptz_to_char,
2420+
TimestampTzGetDatum(val),
2421+
PointerGetDatum(cstring_to_text(nls_timestamp_tz_format))));
2422+
str = text_to_cstring(timestamptz_str);
2423+
break;
2424+
}
2425+
2426+
default: {
2427+
ereport(ERROR,
2428+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
2429+
errmsg("Data type oid=%d not yet implemented", argtype)));
2430+
break;
2431+
}
2432+
}
2433+
2434+
if (str == NULL || str[0] == '\0') {
2435+
/* 1. Set the null flag */
2436+
fcinfo->isnull = true;
2437+
2438+
/* 2. Return a placeholder value */
2439+
PG_RETURN_VOID();
2440+
}
2441+
2442+
PG_RETURN_INT32((unsigned char) str[0]);
2443+
}

0 commit comments

Comments
 (0)