Skip to content

Commit 9d890e9

Browse files
gaoxueyugaoxueyu
authored
Merge pull request #948 from NotHimmel/IVORY_REL_5_STABLE_extract
Add EXTRACT as a keyword and function in Oracle parser
2 parents 5cc67b7 + eaca759 commit 9d890e9

4 files changed

Lines changed: 48 additions & 62 deletions

File tree

‎contrib/ivorysql_ora/expected/ora_xml_functions.out‎

Lines changed: 30 additions & 54 deletions
Original file line numberDiff line numberDiff line change
@@ -27,69 +27,45 @@ INSERT INTO xmlnstest VALUES(1, xmltype('<soapenv:Envelope xmlns:soapenv="http:/
2727
-- extrct(XML)
2828
--
2929
SELECT extract(XMLType('<AA><ID>1</ID></AA>'), '/AA/ID') from dual;
30-
extract
31-
------------
32-
<ID>1</ID>
33-
(1 row)
34-
30+
ERROR: syntax error at or near "("
31+
LINE 1: SELECT extract(XMLType('<AA><ID>1</ID></AA>'), '/AA/ID') fro...
32+
^
3533
SELECT extract(XMLType('<AA><ID>1</ID></AA>'), '/AA') from dual;
36-
extract
37-
--------------
38-
<AA> +
39-
<ID>1</ID>+
40-
</AA>
41-
(1 row)
42-
34+
ERROR: syntax error at or near "("
35+
LINE 1: SELECT extract(XMLType('<AA><ID>1</ID></AA>'), '/AA') from d...
36+
^
4337
SELECT extract(XMLType('<Co Attr ="Test Attr"><ID>1</ID></Co>'), '/Co') from dual;
44-
extract
45-
-----------------------
46-
<Co Attr="Test Attr">+
47-
<ID>1</ID> +
48-
</Co>
49-
(1 row)
50-
38+
ERROR: syntax error at or near "("
39+
LINE 1: SELECT extract(XMLType('<Co Attr ="Test Attr"><ID>1</ID></Co...
40+
^
5141
SELECT extract(XMLType('<Co Attr ="Test Attr"><ID>1</ID></Co>'), '/Co/@ Attr') from dual;
52-
extract
53-
-----------
54-
Test Attr
55-
(1 row)
56-
42+
ERROR: syntax error at or near "("
43+
LINE 1: SELECT extract(XMLType('<Co Attr ="Test Attr"><ID>1</ID></Co...
44+
^
5745
SELECT extract(XMLType('<a><b>b1</b><b>b2</b></a>'),'/a/b[2]') from dual;
58-
extract
59-
-----------
60-
<b>b2</b>
61-
(1 row)
62-
46+
ERROR: syntax error at or near "("
47+
LINE 1: SELECT extract(XMLType('<a><b>b1</b><b>b2</b></a>'),'/a/b[2]...
48+
^
6349
SELECT extract(XMLType('<a><b>b1</b><b>b2</b></a>'),'/a/b[3]') from dual;
64-
extract
65-
---------
66-
67-
(1 row)
68-
50+
ERROR: syntax error at or near "("
51+
LINE 1: SELECT extract(XMLType('<a><b>b1</b><b>b2</b></a>'),'/a/b[3]...
52+
^
6953
SELECT extract(b, '/a/b') from inaf where a = 2;
70-
extract
71-
---------
72-
73-
(1 row)
74-
54+
ERROR: syntax error at or near ","
55+
LINE 1: SELECT extract(b, '/a/b') from inaf where a = 2;
56+
^
7557
SELECT extract(XMLType('<a><b>b1</b><b>b2</b></a>'),'') from dual;
76-
extract
77-
---------
78-
79-
(1 row)
80-
58+
ERROR: syntax error at or near "("
59+
LINE 1: SELECT extract(XMLType('<a><b>b1</b><b>b2</b></a>'),'') from...
60+
^
8161
SELECT extract(XMLType('<a><b>b1</b><b>b2</b></a>'),null) from dual;
82-
extract
83-
---------
84-
85-
(1 row)
86-
62+
ERROR: syntax error at or near "("
63+
LINE 1: SELECT extract(XMLType('<a><b>b1</b><b>b2</b></a>'),null) fr...
64+
^
8765
SELECT extract(data, 'soapenv:Envelope/soapenv:Body/web:BBB/typ:EEE', 'xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://www.def.com" xmlns:web="http://www.abc.com"') from xmlnstest where id = 1;
88-
extract
89-
------------------------------------------------------
90-
<typ:EEE xmlns:typ="http://www.def.com">41</typ:EEE>
91-
(1 row)
92-
66+
ERROR: syntax error at or near "data"
67+
LINE 1: SELECT extract(data, 'soapenv:Envelope/soapenv:Body/web:BBB/...
68+
^
9369
--
9470
-- extractvalue
9571
--

‎src/backend/oracle_parser/ora_gram.y‎

Lines changed: 10 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -780,7 +780,7 @@ static void determineLanguage(List *options);
780780
OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER PACKAGES
781781

782782
PARALLEL PARAMETER PARSER PARTIAL PARTITION PASSING PASSWORD PATH
783-
PGEXTRACT PLACING PLAN PLANS POLICY
783+
EXTRACT PGEXTRACT PLACING PLAN PLANS POLICY
784784
POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY
785785
PRIOR PRIVILEGES PROCEDURAL PROCEDURE PROCEDURES PROGRAM PUBLICATION
786786

@@ -18018,6 +18018,13 @@ func_expr_common_subexpr:
1801818018
COERCE_SQL_SYNTAX,
1801918019
@1);
1802018020
}
18021+
| EXTRACT '(' extract_list ')'
18022+
{
18023+
$$ = (Node *) makeFuncCall(SystemFuncName("extract"),
18024+
$3,
18025+
COERCE_SQL_SYNTAX,
18026+
@1);
18027+
}
1802118028
/* End - ReqID:SRS-SQL-XML */
1802218029
| NORMALIZE '(' a_expr ')'
1802318030
{
@@ -20510,6 +20517,7 @@ col_name_keyword:
2051020517
| DECIMAL_P
2051120518
| DECODE
2051220519
| EXISTS
20520+
| EXTRACT
2051320521
| FLOAT_P
2051420522
| GREATEST
2051520523
| GROUPING
@@ -20868,6 +20876,7 @@ bare_label_keyword:
2086820876
| EXTEND
2086920877
| EXTENSION
2087020878
| EXTERNAL
20879+
| EXTRACT
2087120880
| FALSE_P
2087220881
| FAMILY
2087320882
| FINALIZE

‎src/include/oracle_parser/ora_kwlist.h‎

Lines changed: 6 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1,12 +1,12 @@
11
/*-------------------------------------------------------------------------
22
* Copyright 2025 IvorySQL Global Development Team
3-
*
3+
*
44
* Licensed under the Apache License, Version 2.0 (the "License");
55
* you may not use this file except in compliance with the License.
66
* You may obtain a copy of the License at
7-
*
7+
*
88
* http://www.apache.org/licenses/LICENSE-2.0
9-
*
9+
*
1010
* Unless required by applicable law or agreed to in writing, software
1111
* distributed under the License is distributed on an "AS IS" BASIS,
1212
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
@@ -83,7 +83,7 @@ PG_KEYWORD("binary_float", BINARY_FLOAT, COL_NAME_KEYWORD, BARE_LABEL)
8383
PG_KEYWORD("binary_float_infinity", BINARY_FLOAT_INFINITY, RESERVED_KEYWORD, BARE_LABEL)
8484
PG_KEYWORD("binary_float_nan", BINARY_FLOAT_NAN, RESERVED_KEYWORD, BARE_LABEL)
8585
PG_KEYWORD("bit", BIT, COL_NAME_KEYWORD, BARE_LABEL)
86-
PG_KEYWORD("body", BODY, UNRESERVED_KEYWORD, BARE_LABEL)
86+
PG_KEYWORD("body", BODY, UNRESERVED_KEYWORD, BARE_LABEL)
8787
PG_KEYWORD("boolean", BOOLEAN_P, COL_NAME_KEYWORD, BARE_LABEL)
8888
PG_KEYWORD("both", BOTH, RESERVED_KEYWORD, BARE_LABEL)
8989
PG_KEYWORD("breadth", BREADTH, UNRESERVED_KEYWORD, BARE_LABEL)
@@ -200,6 +200,7 @@ PG_KEYWORD("expression", EXPRESSION, UNRESERVED_KEYWORD, BARE_LABEL)
200200
PG_KEYWORD("extend", EXTEND, UNRESERVED_KEYWORD, BARE_LABEL)
201201
PG_KEYWORD("extension", EXTENSION, UNRESERVED_KEYWORD, BARE_LABEL)
202202
PG_KEYWORD("external", EXTERNAL, UNRESERVED_KEYWORD, BARE_LABEL)
203+
PG_KEYWORD("extract", EXTRACT, COL_NAME_KEYWORD, BARE_LABEL)
203204
PG_KEYWORD("false", FALSE_P, RESERVED_KEYWORD, BARE_LABEL)
204205
PG_KEYWORD("family", FAMILY, UNRESERVED_KEYWORD, BARE_LABEL)
205206
PG_KEYWORD("fetch", FETCH, RESERVED_KEYWORD, AS_LABEL)
@@ -387,7 +388,7 @@ PG_KEYWORD("overriding", OVERRIDING, UNRESERVED_KEYWORD, BARE_LABEL)
387388
PG_KEYWORD("owned", OWNED, UNRESERVED_KEYWORD, BARE_LABEL)
388389
PG_KEYWORD("owner", OWNER, UNRESERVED_KEYWORD, BARE_LABEL)
389390
PG_KEYWORD("package", PACKAGE, UNRESERVED_KEYWORD, BARE_LABEL)
390-
PG_KEYWORD("packages", PACKAGES, UNRESERVED_KEYWORD, BARE_LABEL)
391+
PG_KEYWORD("packages", PACKAGES, UNRESERVED_KEYWORD, BARE_LABEL)
391392
PG_KEYWORD("parallel", PARALLEL, UNRESERVED_KEYWORD, BARE_LABEL)
392393
PG_KEYWORD("parallel_enable", PARALLEL_ENABLE, UNRESERVED_KEYWORD, BARE_LABEL)
393394
PG_KEYWORD("parameter", PARAMETER, UNRESERVED_KEYWORD, BARE_LABEL)

‎src/oracle_test/regress/expected/interval_1.out‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1516,9 +1516,9 @@ LINE 6: FROM INTERVAL_TBL;
15161516
^
15171517
-- internal overflow test case
15181518
SELECT extract(epoch from interval '1000000000 days');
1519-
ERROR: syntax error at or near "from"
1519+
ERROR: syntax error at or near ")"
15201520
LINE 1: SELECT extract(epoch from interval '1000000000 days');
1521-
^
1521+
^
15221522
--
15231523
-- test infinite intervals
15241524
--

0 commit comments

Comments
 (0)