Syntax of xmltable function
Hi
I am working on xmltable function. This function is used for simple data
extraction from XML documents. In this moment I am working on prototype's
parser.
The SQL/XML syntax is:
XMLTABLE (
xmlquery
PASSING xmldocument
[COLUMNS columns_list] )
columns_list := column_list ',' colname
colname := colname datatype [DEFAULT value] [PATH text]
The DEFAULT clause is before PATH clause.
I am able to implement the default clause with c_expr - It is enough for
almost all usage.
What is question?
A Oracle db uses little bit different syntax - the DEFAULT and PATH clause
are in different order. This is little bit better for Postgres too - now I
can use a_expr in DEFAULT clause - what can be little bit better for other
than text types.
What should be preferrable? ANSI SQL syntax or Oracle syntax? This is not
often task - I didn't find any example with DEFAULT clause for Oracle.
DB2 and Sybase uses exact ANSI SQL syntax.
Regards
Pavel
Pavel Stehule <pavel.stehule@gmail.com> writes:
The SQL/XML syntax is:
XMLTABLE (
xmlquery
PASSING xmldocument
[COLUMNS columns_list] )
columns_list := column_list ',' colname
colname := colname datatype [DEFAULT value] [PATH text]
The DEFAULT clause is before PATH clause.
A Oracle db uses little bit different syntax - the DEFAULT and PATH clause
are in different order. This is little bit better for Postgres too - now I
can use a_expr in DEFAULT clause - what can be little bit better for other
than text types.
What should be preferrable? ANSI SQL syntax or Oracle syntax? This is not
often task - I didn't find any example with DEFAULT clause for Oracle.
ISTM that the ordering of those two clauses is totally arbitrary, and
that if you're going to worry about Oracle at all, it'd be best to allow
both orderings.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
2016-07-31 17:53 GMT+02:00 Tom Lane <tgl@sss.pgh.pa.us>:
Pavel Stehule <pavel.stehule@gmail.com> writes:
The SQL/XML syntax is:
XMLTABLE (
xmlquery
PASSING xmldocument
[COLUMNS columns_list] )columns_list := column_list ',' colname
colname := colname datatype [DEFAULT value] [PATH text]
The DEFAULT clause is before PATH clause.
A Oracle db uses little bit different syntax - the DEFAULT and PATH
clause
are in different order. This is little bit better for Postgres too - now
I
can use a_expr in DEFAULT clause - what can be little bit better for
other
than text types.
What should be preferrable? ANSI SQL syntax or Oracle syntax? This is not
often task - I didn't find any example with DEFAULT clause for Oracle.ISTM that the ordering of those two clauses is totally arbitrary, and
that if you're going to worry about Oracle at all, it'd be best to allow
both orderings.
ok. I checked it - and it is possible.
Regards
Pavel
Show quoted text
regards, tom lane