Red Hat SummitDieser Inhalt ist in der von Ihnen ausgewählten Sprache nicht verfügbar.
Chapter 12. BNF for SQL Grammar
12.1. Reserved Keywords
12.2. Non-Reserved Keywords
12.3. Reserved Keywords For Future Use
| ALLOCATE | ARE | ASENSITIVE |
| ASYMETRIC | AUTHORIZATION | BINARY |
| CALLED | CASCADED | CHARACTER |
| CHECK | CLOSE | COLLATE |
| CONNECT | CORRESPONDING | CRITERIA |
| CURRENT_USER | CURSOR | CYCLE |
| DATALINK | DEALLOCATE | DEC |
| DEREF | DESCRIBE | DETERMINISTIC |
| DISCONNECT | DLNEWCOPY | DLPREVIOUSCOPY |
| DLURLCOMPLETE | DLURLCOMPLETEONLY | DLURLCOMPLETEWRITE |
| DLURLPATH | DLURLPATHONLY | DLURLPATHWRITE |
| DLURLSCHEME | DLURLSERVER | DLVALUE |
| DYNAMIC | ELEMENT | EXTERNAL |
| FREE | GET | HAS |
| HOLD | IDENTITY | INDICATOR |
| INPUT | INSENSITIVE | INT |
| INTERVAL | ISOLATION | LARGE |
| LOCALTIME | LOCALTIMESTAMP | MATCH |
| MEMBER | METHOD | MODIFIES |
| MODULE | MULTISET | NATIONAL |
| NATURAL | NCHAR | NCLOB |
| NEW | NUMERIC | OLD |
| OPEN | OUTPUT | OVERLAPS |
| PRECISION | PREPARE | READS |
| RECURSIVE | REFERENCING | RELEASE |
| ROLLBACK | SAVEPOINT | SCROLL |
| SEARCH | SENSITIVE | SESSION_USER |
| SPECIFIC | SPECIFICTYPE | SQL |
| START | STATIC | SUBMULTILIST |
| SYMETRIC | SYSTEM | SYSTEM_USER |
| TIMEZONE_HOUR | TIMEZONE_MINUTE | TRANSLATION |
| TREAT | VALUE | VARYING |
| WHENEVER | WINDOW | XMLBINARY |
| XMLDOCUMENT | XMLITERATE | XMLVALIDATE |
12.4. Tokens
12.5. Production Cross-Reference
12.6. Productions
12.6.1. string ::=
A string literal value. Use '' to escape ' in the string.
Example:
'a string'
'a string''it''s a string'
'it''s a string'
12.6.2. non-reserved identifier ::=
Allows non-reserved keywords to be parsed as identifiers
Example: SELECT COUNT FROM …
12.6.3. basicNonReserved ::=
- INSTEAD
- VIEW
- ENABLED
- DISABLED
- KEY
- TEXTAGG
- COUNT
- COUNT_BIG
- ROW_NUMBER
- RANK
- DENSE_RANK
- SUM
- AVG
- MIN
- MAX
- EVERY
- STDDEV_POP
- STDDEV_SAMP
- VAR_SAMP
- VAR_POP
- DOCUMENT
- CONTENT
- TRIM
- EMPTY
- ORDINALITY
- PATH
- FIRST
- LAST
- NEXT
- SUBSTRING
- EXTRACT
- TO_CHARS
- TO_BYTES
- TIMESTAMPADD
- TIMESTAMPDIFF
- QUERYSTRING
- NAMESPACE
- RESULT
- ACCESSPATTERN
- AUTO_INCREMENT
- WELLFORMED
- SQL_TSI_FRAC_SECOND
- SQL_TSI_SECOND
- SQL_TSI_MINUTE
- SQL_TSI_HOUR
- SQL_TSI_DAY
- SQL_TSI_WEEK
- SQL_TSI_MONTH
- SQL_TSI_QUARTER
- SQL_TSI_YEAR
- TEXTTABLE
- ARRAYTABLE
- JSONTABLE
- SELECTOR
- SKIP
- WIDTH
- PASSING
- NAME
- ENCODING
- COLUMNS
- DELIMITER
- QUOTE
- HEADER
- NULLS
- OBJECTTABLE
- VERSION
- INCLUDING
- EXCLUDING
- XMLDECLARATION
- VARIADIC
- RAISE
- CHAIN
- JSONARRAY_AGG
- JSONOBJECT
- PRESERVE
- UPSERT
- AFTER
- TYPE
- TRANSLATOR
- JAAS
- CONDITION
- MASK
- ACCESS
- CONTROL
- NONE
- DATA
- DATABASE
- PRIVILEGES
- ROLE
- SCHEMA
- USE
- REPOSITORY
- RENAME
- DOMAIN
- USAGE
- POSITION
- CURRENT
- UNBOUNDED
- PRECEDING
- FOLLOWING
- LISTAGG
- EXPLAIN
- ANALYZE
- TEXT
- FORMAT
- YAML
- EPOCH
- QUARTER
- POLICY
12.6.4. Unqualified identifier ::=
Unqualified name of a single entity.
Example:
"tbl"
"tbl"
12.6.5. identifier ::=
Partial or full name of a single entity.
Example:
tbl.col
tbl.col"tbl"."col"
"tbl"."col"
12.6.6. create trigger ::=
- CREATE TRIGGER ( <identifier> )? ON <identifier> ( ( INSTEAD OF ) | AFTER ) ( INSERT | UPDATE | DELETE ) AS <for each row trigger action>
Creates a trigger action on the given target.
Example:
CREATE TRIGGER ON vw INSTEAD OF INSERT AS FOR EACH ROW BEGIN ATOMIC ... END
CREATE TRIGGER ON vw INSTEAD OF INSERT AS FOR EACH ROW BEGIN ATOMIC ... END
12.6.7. alter ::=
- ALTER ( ( VIEW <identifier> AS <query expression> ) | ( PROCEDURE <identifier> AS <statement> ) | ( TRIGGER ( <identifier> )? ON <identifier> ( ( INSTEAD OF ) | AFTER ) ( INSERT | UPDATE | DELETE ) ( ( AS <for each row trigger action> ) | ENABLED | DISABLED ) ) )
Alter the given target.
Example:
ALTER VIEW vw AS SELECT col FROM tbl
ALTER VIEW vw AS SELECT col FROM tbl
12.6.8. for each row trigger action ::=
Defines an action to perform on each row.
Example:
FOR EACH ROW BEGIN ATOMIC ... END
FOR EACH ROW BEGIN ATOMIC ... END
12.6.9. explain ::=
- EXPLAIN ( <lparen> <explain option> ( <comma> <explain option> )* <rparen> )? <directly executable statement>
Returns the query plan for the statement
Example: EXPLAIN select 1
12.6.10. explain option ::=
Option for the explain statement
Example: FORMAT YAML
12.6.11. directly executable statement ::=
A statement that can be executed at runtime.
Example:
SELECT * FROM tbl
SELECT * FROM tbl
12.6.12. drop table ::=
Drop the given table.
Example:
DROP TABLE #temp
DROP TABLE #temp
12.6.13. create temporary table ::=
- CREATE ( LOCAL )? TEMPORARY TABLE <Unqualified identifier> <lparen> <temporary table element> ( <comma> <temporary table element> )* ( <comma> PRIMARY KEY <column list> )? <rparen> ( ON COMMIT PRESERVE ROWS )?
Creates a temporary table.
Example:
CREATE LOCAL TEMPORARY TABLE tmp (col integer)
CREATE LOCAL TEMPORARY TABLE tmp (col integer)
12.6.14. temporary table element ::=
- <identifier> ( <basic data type> | SERIAL ) ( NOT NULL )?
Defines a temporary table column.
Example:
col string NOT NULL
col string NOT NULL
12.6.15. raise error statement ::=
Raises an error with the given message.
Example:
ERROR 'something went wrong'
ERROR 'something went wrong'
12.6.16. raise statement ::=
- RAISE ( SQLWARNING )? <exception reference>
Raises an error or warning with the given message.
Example:
RAISE SQLEXCEPTION 'something went wrong'
RAISE SQLEXCEPTION 'something went wrong'
12.6.17. exception reference ::=
a reference to an exception
Example:
SQLEXCEPTION 'something went wrong' SQLSTATE '00X', 2
SQLEXCEPTION 'something went wrong' SQLSTATE '00X', 2
12.6.18. sql exception ::=
- SQLEXCEPTION <common value expression> ( SQLSTATE <common value expression> ( <comma> <common value expression> )? )? ( CHAIN <exception reference> )?
creates a sql exception or warning with the specified message, state, and code
Example:
SQLEXCEPTION 'something went wrong' SQLSTATE '00X', 2
SQLEXCEPTION 'something went wrong' SQLSTATE '00X', 2
12.6.19. statement ::=
- ( ( <identifier> <colon> )? ( <loop statement> | <while statement> | <compound statement> ) )
- <if statement> | <delimited statement>
A procedure statement.
Example:
IF (x = 5) BEGIN ... END
IF (x = 5) BEGIN ... END
12.6.20. delimited statement ::=
- ( <assignment statement> | <data statement> | <raise error statement> | <raise statement> | <declare statement> | <branching statement> | <return statement> ) <semicolon>
A procedure statement terminated by ;.
Example:
SELECT * FROM tbl;
SELECT * FROM tbl;
12.6.21. compound statement ::=
A procedure statement block contained in BEGIN END.
Example:
BEGIN NOT ATOMIC ... END
BEGIN NOT ATOMIC ... END
12.6.22. branching statement ::=
- ( ( BREAK | CONTINUE ) ( <identifier> )? )
- ( LEAVE <identifier> )
A procedure branching control statement, which typically specifies a label to return control to.
Example:
BREAK x
BREAK x
12.6.23. return statement ::=
- RETURN ( <expression> )?
A return statement.
Example:
RETURN 1
RETURN 1
12.6.24. while statement ::=
A procedure while statement that executes until its condition is false.
Example:
WHILE (var) BEGIN ... END
WHILE (var) BEGIN ... END
12.6.25. loop statement ::=
- LOOP ON <lparen> <query expression> <rparen> AS <identifier> <statement>
A procedure loop statement that executes over the given cursor.
Example:
LOOP ON (SELECT * FROM tbl) AS x BEGIN ... END
LOOP ON (SELECT * FROM tbl) AS x BEGIN ... END
12.6.26. if statement ::=
A procedure loop statement that executes over the given cursor.
Example:
IF (boolVal) BEGIN variables.x = 1 END ELSE BEGIN variables.x = 2 END
IF (boolVal) BEGIN variables.x = 1 END ELSE BEGIN variables.x = 2 END
12.6.27. declare statement ::=
- DECLARE ( <data type> | EXCEPTION ) <identifier> ( <eq> <assignment statement operand> )?
A procedure declaration statement that creates a variable and optionally assigns a value.
Example:
DECLARE STRING x = 'a'
DECLARE STRING x = 'a'
12.6.28. assignment statement ::=
- <identifier> <eq> ( <assignment statement operand> | ( <call statement> ( ( WITH | WITHOUT ) RETURN )? ) )
Assigns a variable a value in a procedure.
Example:
x = 'b'
x = 'b'
12.6.29. assignment statement operand ::=
A value or command that can be used in an assignment. {note}All assignments except for expression are deprecated.{note}
12.6.30. data statement ::=
- ( <directly executable statement> | <dynamic data statement> ) ( ( WITH | WITHOUT ) RETURN )?
A procedure statement that executes a SQL statement. An update statement can have its update count accessed via the ROWCOUNT variable.
12.6.31. dynamic data statement ::=
- ( EXECUTE | EXEC ) ( STRING | IMMEDIATE )? <expression> ( AS <typed element list> ( INTO <identifier> )? )? ( USING <set clause list> )? ( UPDATE ( <unsigned integer> | <star> ) )?
A procedure statement that can execute arbitrary sql.
Example:
EXECUTE IMMEDIATE 'SELECT * FROM tbl' AS x STRING INTO #temp
EXECUTE IMMEDIATE 'SELECT * FROM tbl' AS x STRING INTO #temp
12.6.32. set clause list ::=
- <identifier> <eq> <expression> ( <comma> <identifier> <eq> <expression> )*
A list of value assignments.
Example:
col1 = 'x', col2 = 'y' ...
col1 = 'x', col2 = 'y' ...
12.6.33. typed element list ::=
- <identifier> <basic data type> ( <comma> <identifier> <basic data type> )*
A list of typed elements.
Example:
col1 string, col2 integer ...
col1 string, col2 integer ...
12.6.34. callable statement ::=
- <lbrace> ( <qmark> <eq> )? CALL <identifier> ( <lparen> ( <named parameter list> | ( <expression list> )? ) <rparen> )? <rbrace> ( <option clause> )?
A callable statement defined using JDBC escape syntax.
Example:
{? = CALL proc}
{? = CALL proc}
12.6.35. call statement ::=
- ( ( EXEC | EXECUTE | CALL ) <identifier> <lparen> ( <named parameter list> | ( <expression list> )? ) <rparen> ) ( <option clause> )?
Executes the procedure with the given parameters.
Example:
CALL proc('a', 1)
CALL proc('a', 1)
12.6.36. named parameter list ::=
- ( <identifier> <eq> ( <gt> )? <expression> ( <comma> <identifier> <eq> ( <gt> )? <expression> )* )
A list of named parameters.
Example:
param1 => 'x', param2 => 1
param1 => 'x', param2 => 1
12.6.37. insert statement ::=
- ( INSERT | MERGE | UPSERT ) INTO <identifier> ( <column list> )? <query expression> ( <option clause> )?
Inserts values into the given target.
Example:
INSERT INTO tbl (col1, col2) VALUES ('a', 1)
INSERT INTO tbl (col1, col2) VALUES ('a', 1)
12.6.38. expression list ::=
- <expression> ( <comma> <expression> )*
A list of expressions.
Example:
col1, 'a', ...
col1, 'a', ...
12.6.39. update statement ::=
- UPDATE <identifier> ( ( AS )? <identifier> )? SET <set clause list> ( <where clause> )? ( <option clause> )?
Update values in the given target.
Example:
UPDATE tbl SET (col1 = 'a') WHERE col2 = 1
UPDATE tbl SET (col1 = 'a') WHERE col2 = 1
12.6.40. delete statement ::=
- DELETE FROM <identifier> ( ( AS )? <identifier> )? ( <where clause> )? ( <option clause> )?
Delete rows from the given target.
Example:
DELETE FROM tbl WHERE col2 = 1
DELETE FROM tbl WHERE col2 = 1
12.6.41. query expression ::=
- ( WITH <with list element> ( <comma> <with list element> )* )? <query expression body>
A declarative query for data.
Example:
SELECT * FROM tbl WHERE col2 = 1
SELECT * FROM tbl WHERE col2 = 1
12.6.42. with list element ::=
- <identifier> ( <column list> )? AS <lparen> <query expression> <rparen>
A query expression for use in the enclosing query.
Example:
X (Y, Z) AS (SELECT 1, 2)
X (Y, Z) AS (SELECT 1, 2)
12.6.43. query expression body ::=
- <query term> ( ( UNION | EXCEPT ) ( ALL | DISTINCT )? <query term> )* ( <order by clause> )? ( <limit clause> )? ( <option clause> )?
The body of a query expression, which can optionally be ordered and limited.
Example:
SELECT * FROM tbl ORDER BY col1 LIMIT 1
SELECT * FROM tbl ORDER BY col1 LIMIT 1
12.6.44. query term ::=
- <query primary> ( INTERSECT ( ALL | DISTINCT )? <query primary> )*
Used to establish INTERSECT precedence.
Example:
SELECT * FROM tbl
SELECT * FROM tblSELECT * FROM tbl1 INTERSECT SELECT * FROM tbl2
SELECT * FROM tbl1 INTERSECT SELECT * FROM tbl2
12.6.45. query primary ::=
- <query>
- ( VALUES <lparen> <expression list> <rparen> ( <comma> <lparen> <expression list> <rparen> )* )
- ( TABLE <identifier> )
- ( <lparen> <query expression body> <rparen> )
A declarative source of rows.
Example:
TABLE tbl
TABLE tblSELECT * FROM tbl1
SELECT * FROM tbl1
12.6.46. query ::=
- <select clause> ( <into clause> )? ( <from clause> ( <where clause> )? ( <group by clause> )? ( <having clause> )? )?
A SELECT query.
Example:
SELECT col1, max(col2) FROM tbl GROUP BY col1
SELECT col1, max(col2) FROM tbl GROUP BY col1
12.6.47. into clause ::=
Used to direct the query into a table. {note}This is deprecated. Use INSERT INTO with a query expression instead.{note}
Example:
INTO tbl
INTO tbl
12.6.48. select clause ::=
- SELECT ( ALL | DISTINCT )? ( <star> | ( <select sublist> ( <comma> <select sublist> )* ) )
The columns returned by a query. Can optionally be distinct.
Example:
SELECT *
SELECT *SELECT DISTINCT a, b, c
SELECT DISTINCT a, b, c
12.6.49. select sublist ::=
An element in the select clause
Example:
tbl.*
tbl.*tbl.col AS x
tbl.col AS x
12.6.50. select derived column ::=
- ( <expression> ( ( AS )? <identifier> )? )
A select clause item that selects a single column. {note}This is slightly different than a derived column in that the AS keyword is optional.{note}
Example:
tbl.col AS x
tbl.col AS x
12.6.51. derived column ::=
- ( <expression> ( AS <identifier> )? )
An optionally named expression.
Example:
tbl.col AS x
tbl.col AS x
12.6.52. all in group ::=
A select sublist that can select all columns from the given group.
Example:
tbl.*
tbl.*
12.6.53. ordered aggregate function ::=
- ( XMLAGG | ARRAY_AGG | JSONARRAY_AGG ) <lparen> <expression> ( <order by clause> )? <rparen>
An aggregate function that can optionally be ordered.
Example:
XMLAGG(col1) ORDER BY col2
XMLAGG(col1) ORDER BY col2ARRAY_AGG(col1)
ARRAY_AGG(col1)
12.6.54. text aggreate function ::=
- TEXTAGG <lparen> ( FOR )? <derived column> ( <comma> <derived column> )* ( DELIMITER <character> )? ( ( QUOTE <character> ) | ( NO QUOTE ) )? ( HEADER )? ( ENCODING <identifier> )? ( <order by clause> )? <rparen>
An aggregate function for creating separated value clobs.
Example:
TEXTAGG (col1 as t1, col2 as t2 DELIMITER ',' HEADER)
TEXTAGG (col1 as t1, col2 as t2 DELIMITER ',' HEADER)
12.6.55. standard aggregate function ::=
A standard aggregate function.
Example:
COUNT(*)
COUNT(*)
12.6.56. analytic aggregate function ::=
- ( ROW_NUMBER | RANK | DENSE_RANK | PERCENT_RANK | CUME_DIST ) <lparen> <rparen>
An analytic aggregate function.
Example:
ROW_NUMBER()
ROW_NUMBER()
12.6.57. filter clause ::=
- FILTER <lparen> WHERE <boolean primary> <rparen>
An aggregate filter clause applied prior to accumulating the value.
Example:
FILTER (WHERE col1='a')
FILTER (WHERE col1='a')
12.6.58. from clause ::=
- FROM ( <table reference> ( <comma> <table reference> )* )
A query from clause containing a list of table references.
Example:
FROM a, b
FROM a, bFROM a right outer join b, c, d join e".</p>
FROM a right outer join b, c, d join e".</p>
12.6.59. table reference ::=
- ( <escaped join> <joined table> <rbrace> )
- <joined table>
An optionally escaped joined table.
Example:
a
aa inner join b
a inner join b
12.6.60. joined table ::=
- <table primary> ( <cross join> | <qualified table> )*
A table or join.
Example:
a
aa inner join b
a inner join b
12.6.61. cross join ::=
- ( ( CROSS | UNION ) JOIN <table primary> )
A cross join.
Example:
a CROSS JOIN b
a CROSS JOIN b
12.6.62. qualified table ::=
An INNER or OUTER join.
Example:
a inner join b
a inner join b
12.6.63. table primary ::=
- ( <text table> | <array table> | <json table> | <xml table> | <object table> | <table name> | <table subquery> | ( <lparen> <joined table> <rparen> ) ) ( ( MAKEDEP <make dep options> ) | MAKENOTDEP )? ( ( MAKEIND <make dep options> ) )?
A single source of rows.
Example:
a
a
12.6.64. make dep options ::=
options for the make dep hint
Example:
(min:10000)
(min:10000)
12.6.65. xml serialize ::=
- XMLSERIALIZE <lparen> ( DOCUMENT | CONTENT )? <expression> ( AS ( STRING | VARCHAR | CLOB | VARBINARY | BLOB ) )? ( ENCODING <identifier> )? ( VERSION <string> )? ( ( INCLUDING | EXCLUDING ) XMLDECLARATION )? <rparen>
Serializes an XML value.
Example:
XMLSERIALIZE(col1 AS CLOB)
XMLSERIALIZE(col1 AS CLOB)
12.6.66. array table ::=
- ARRAYTABLE <lparen> ( ROW | ROWS )? <value expression primary> COLUMNS <typed element list> <rparen> ( AS )? <identifier>
The ARRAYTABLE table function creates tabular results from arrays. It can be used as a nested table reference.
Example:
ARRAYTABLE (col1 COLUMNS x STRING) AS y
ARRAYTABLE (col1 COLUMNS x STRING) AS y
12.6.67. json table ::=
- JSONTABLE <lparen> <value expression primary> <comma> <string> ( <comma> ( TRUE | FALSE ) )? COLUMNS <json table column> ( <comma> <json table column> )* <rparen> ( AS )? <identifier>
The JSONTABLE table function creates tabular results from JSON. It can be used as a nested table reference.
Example:
JSONTABLE (col1, '$..book', false COLUMNS x STRING) AS y
JSONTABLE (col1, '$..book', false COLUMNS x STRING) AS y
12.6.68. json table column ::=
- <identifier> ( ( FOR ORDINALITY ) | ( <basic data type> ( PATH <string> )? ) )
json table column.
Example:
col FOR ORDINALITY
col FOR ORDINALITY
12.6.69. text table ::=
- TEXTTABLE <lparen> <common value expression> ( SELECTOR <string> )? COLUMNS <text table column> ( <comma> <text table column> )* ( ( NO ROW DELIMITER ) | ( ROW DELIMITER <character> ) )? ( DELIMITER <character> )? ( ( ESCAPE <character> ) | ( QUOTE <character> ) )? ( HEADER ( <unsigned integer> )? )? ( SKIP <unsigned integer> )? ( NO TRIM )? <rparen> ( AS )? <identifier>
The TEXTTABLE table function creates tabular results from text. It can be used as a nested table reference.
Example:
TEXTTABLE (file COLUMNS x STRING) AS y
TEXTTABLE (file COLUMNS x STRING) AS y
12.6.70. text table column ::=
- <identifier> ( ( FOR ORDINALITY ) | ( ( HEADER <string> )? <basic data type> ( WIDTH <unsigned integer> ( NO TRIM )? )? ( SELECTOR <string> <unsigned integer> )? ) )
A text table column.
Example:
x INTEGER WIDTH 6
x INTEGER WIDTH 6
12.6.71. xml query ::=
- XMLEXISTS <lparen> ( <xml namespaces> <comma> )? <string> ( PASSING <derived column> ( <comma> <derived column> )* )? <rparen>
Executes an XQuery to return an XML result.
Example:
XMLQUERY('<a>...</a>' PASSING doc)
XMLQUERY('<a>...</a>' PASSING doc)
12.6.72. xml query ::=
- XMLQUERY <lparen> ( <xml namespaces> <comma> )? <string> ( PASSING <derived column> ( <comma> <derived column> )* )? ( ( NULL | EMPTY ) ON EMPTY )? <rparen>
Executes an XQuery to return an XML result.
Example:
XMLQUERY('<a>...</a>' PASSING doc)
XMLQUERY('<a>...</a>' PASSING doc)
12.6.73. object table ::=
- OBJECTTABLE <lparen> ( LANGUAGE <string> )? <string> ( PASSING <derived column> ( <comma> <derived column> )* )? COLUMNS <object table column> ( <comma> <object table column> )* <rparen> ( AS )? <identifier>
Returns table results by processing a script.
Example:
OBJECTTABLE('z' PASSING val AS z COLUMNS col OBJECT 'teiid_row') AS X
OBJECTTABLE('z' PASSING val AS z COLUMNS col OBJECT 'teiid_row') AS X
12.6.74. object table column ::=
- <identifier> <basic data type> <string> ( DEFAULT <expression> )?
object table column.
Example:
y integer 'teiid_row_number'
y integer 'teiid_row_number'
12.6.75. xml table ::=
- XMLTABLE <lparen> ( <xml namespaces> <comma> )? <string> ( PASSING <derived column> ( <comma> <derived column> )* )? ( COLUMNS <xml table column> ( <comma> <xml table column> )* )? <rparen> ( AS )? <identifier>
Returns table results by processing an XQuery.
Example:
XMLTABLE('/a/b' PASSING doc COLUMNS col XML PATH '.') AS X
XMLTABLE('/a/b' PASSING doc COLUMNS col XML PATH '.') AS X
12.6.76. xml table column ::=
- <identifier> ( ( FOR ORDINALITY ) | ( <basic data type> ( DEFAULT <expression> )? ( PATH <string> )? ) )
XML table column.
Example:
y FOR ORDINALITY
y FOR ORDINALITY
12.6.77. unsigned integer ::=
An unsigned interger value.
Example:
12345
12345
12.6.78. table subquery ::=
- ( TABLE | LATERAL )? <lparen> ( <query expression> | <call statement> ) <rparen> ( AS )? <identifier>
A table defined by a subquery.
Example:
(SELECT * FROM tbl) AS x
(SELECT * FROM tbl) AS x
12.6.79. table name ::=
- ( <identifier> ( ( AS )? <identifier> )? )
A table named in the FROM clause.
Example:
tbl AS x
tbl AS x
12.6.80. where clause ::=
Specifies a search condition
Example:
WHERE x = 'a'
WHERE x = 'a'
12.6.81. condition ::=
A boolean expression.
12.6.82. boolean value expression ::=
- <boolean term> ( OR <boolean term> )*
An optionally ORed boolean expression.
12.6.83. boolean term ::=
- <boolean factor> ( AND <boolean factor> )*
An optional ANDed boolean factor.
12.6.84. boolean factor ::=
- ( NOT )? <boolean primary>
A boolean factor.
Example:
NOT x = 'a'
NOT x = 'a'
12.6.85. boolean primary ::=
- ( <common value expression> ( <between predicate> | <match predicate> | <like regex predicate> | <in predicate> | <is null predicate> | <quantified comparison predicate> | <comparison predicate> | <is distinct> )? )
- <exists predicate>
- <xml query>
A boolean predicate or simple expression.
Example:
col LIKE 'a%'
col LIKE 'a%'
12.6.86. comparison operator ::=
A comparison operator.
Example:
=
=
12.6.87. is distinct ::=
- IS ( NOT )? DISTINCT FROM <common value expression>
Is Distinct Right Hand Side
Example:
IS DISTINCT FROM expression
IS DISTINCT FROM expression
12.6.88. comparison predicate ::=
A value comparison.
Example:
= 'a'
= 'a'
12.6.89. subquery ::=
- <lparen> ( <query expression> | <call statement> ) <rparen>
A subquery.
Example:
(SELECT * FROM tbl)
(SELECT * FROM tbl)
12.6.90. quantified comparison predicate ::=
- <comparison operator> ( ANY | SOME | ALL ) ( <subquery> | ( <lparen> <expression> <rparen> ) )
A subquery comparison.
Example:
= ANY (SELECT col FROM tbl)
= ANY (SELECT col FROM tbl)
12.6.91. match predicate ::=
Matches based upon a pattern.
Example:
LIKE 'a_'
LIKE 'a_'
12.6.92. like regex predicate ::=
- ( NOT )? LIKE_REGEX <common value expression>
A regular expression match.
Example:
LIKE_REGEX 'a.*b'
LIKE_REGEX 'a.*b'
12.6.93. character ::=
- <string>
A single character.
Example:
'a'
'a'
12.6.94. between predicate ::=
A comparison between two values.
Example:
BETWEEN 1 AND 5
BETWEEN 1 AND 5
12.6.95. is null predicate ::=
A null test.
Example:
IS NOT NULL
IS NOT NULL
12.6.96. in predicate ::=
- ( NOT )? IN ( <subquery> | ( <lparen> <common value expression> ( <comma> <common value expression> )* <rparen> ) )
A comparison with multiple values.
Example:
IN (1, 5)
IN (1, 5)
12.6.97. exists predicate ::=
A test if rows exist.
Example:
EXISTS (SELECT col FROM tbl)
EXISTS (SELECT col FROM tbl)
12.6.98. group by clause ::=
- GROUP BY ( ROLLUP <lparen> <expression list> <rparen> | <expression list> )
Defines the grouping columns
Example:
GROUP BY col1, col2
GROUP BY col1, col2
12.6.99. having clause ::=
Search condition applied after grouping.
Example:
HAVING max(col1) = 5
HAVING max(col1) = 5
12.6.100. order by clause ::=
- ORDER BY <sort specification> ( <comma> <sort specification> )*
Specifices row ordering.
Example:
ORDER BY x, y DESC
ORDER BY x, y DESC
12.6.101. sort specification ::=
Defines how to sort on a particular expression
Example:
col1 NULLS FIRST
col1 NULLS FIRST
12.6.102. sort key ::=
A sort expression.
Example:
col1
col1
12.6.103. integer parameter ::=
A literal integer or parameter reference to an integer.
Example:
?
?
12.6.104. limit clause ::=
- ( LIMIT <integer parameter> ( ( <comma> <integer parameter> ) | ( OFFSET <integer parameter> ) )? )
- ( OFFSET <integer parameter> ( ROW | ROWS ) ( <fetch clause> )? )
- <fetch clause>
Limits and/or offsets the resultant rows.
Example:
LIMIT 2
LIMIT 2
12.6.105. fetch clause ::=
ANSI limit.
Example:
FETCH FIRST 1 ROWS ONLY
FETCH FIRST 1 ROWS ONLY
12.6.106. option clause ::=
- OPTION ( MAKEDEP <identifier> <make dep options> ( <comma> <identifier> <make dep options> )* | MAKEIND <identifier> <make dep options> ( <comma> <identifier> <make dep options> )* | MAKENOTDEP <identifier> ( <comma> <identifier> )* | NOCACHE ( <identifier> ( <comma> <identifier> )* )? )*
Specifies query options.
Example:
OPTION MAKEDEP tbl
OPTION MAKEDEP tbl
12.6.107. expression ::=
A value.
Example:
col1
col1
12.6.108. common value expression ::=
- ( <numeric value expression> ( ( <double_amp_op> | <concat_op> ) <numeric value expression> )* )
Establishes the precedence of concat.
Example:
'a' || 'b'
'a' || 'b'
12.6.109. numeric value expression ::=
- ( <term> ( <plus or minus> <term> )* )
Example:
1 + 2
1 + 2
12.6.110. plus or minus ::=
The + or - operator.
Example:
+
+
12.6.111. term ::=
- ( <value expression primary> ( <star or slash> <value expression primary> )* )
A numeric term
Example:
1 * 2
1 * 2
12.6.112. star or slash ::=
The * or / operator.
Example:
/
/
12.6.113. value expression primary ::=
- <non numeric literal>
- ( <plus or minus> )? ( <unsigned numeric literal> | ( <unsigned value expression primary> ( <lsbrace> <numeric value expression> <rsbrace> )* ) )
A simple value expression.
Example:
+col1
+col1
12.6.114. parameter reference ::=
- <qmark>
- ( <dollar> <unsigned integer> )
A parameter reference to be bound later.
Example:
?
?
12.6.115. unescapedFunction ::=
- ( ( <text aggreate function> | <standard aggregate function> | <ordered aggregate function> ) ( <filter clause> )? ( <window specification> )? ) | ( <analytic aggregate function> ( <filter clause> )? <window specification> ) | ( <function> ( <window specification> )? )
- ( XMLCAST <lparen> <expression> AS <data type> <rparen> )
12.6.116. nested expression ::=
- ( <lparen> ( <expression> ( <comma> <expression> )* )? ( <comma> )? <rparen> )
An expression nested in parens
Example:
(1)
(1)
12.6.117. unsigned value expression primary ::=
An unsigned simple value expression.
Example:
col1
col1
12.6.118. ARRAY expression constructor ::=
- ARRAY ( ( <lsbrace> ( <expression> ( <comma> <expression> )* )? <rsbrace> ) | ( <lparen> <query expression> <rparen> ) )
Creates and array of the given expressions.
Example:
ARRAY[1,2]
ARRAY[1,2]
12.6.119. window specification ::=
- OVER <lparen> ( PARTITION BY <expression list> )? ( <order by clause> )? ( <window frame> )? <rparen>
The window specification for an analytical or windowed aggregate function.
Example:
OVER (PARTION BY col1)
OVER (PARTION BY col1)
12.6.120. window frame ::=
- ( RANGE | ROWS ) ( ( BETWEEN <window frame bound> AND <window frame bound> ) | <window frame bound> )
Defines the mode, start, and optionally end of the window frame
Example:
RANGE UNBOUNDED PRECEDING
RANGE UNBOUNDED PRECEDING
12.6.121. window frame bound ::=
Defines the start or end of a window frame
Example:
CURRENT ROW
CURRENT ROW
12.6.122. case expression ::=
- CASE <expression> ( WHEN <expression> THEN <expression> )+ ( ELSE <expression> )? END
If/then/else chain using a common search predicand.
Example:
CASE col1 WHEN 'a' THEN 1 ELSE 2
CASE col1 WHEN 'a' THEN 1 ELSE 2
12.6.123. searched case expression ::=
- CASE ( WHEN <condition> THEN <expression> )+ ( ELSE <expression> )? END
If/then/else chain using multiple search conditions.
Example:
CASE WHEN x = 'a' THEN 1 WHEN y = 'b' THEN 2
CASE WHEN x = 'a' THEN 1 WHEN y = 'b' THEN 2
12.6.124. function ::=
- ( CONVERT <lparen> <expression> <comma> <data type> <rparen> )
- ( CAST <lparen> <expression> AS <data type> <rparen> )
- ( SUBSTRING <lparen> <expression> ( ( FROM <expression> ( FOR <expression> )? ) | ( <comma> <expression list> ) ) <rparen> )
- ( EXTRACT <lparen> ( YEAR | MONTH | DAY | HOUR | MINUTE | SECOND | QUARTER | EPOCH ) FROM <expression> <rparen> )
- ( TRIM <lparen> ( ( ( ( LEADING | TRAILING | BOTH ) ( <expression> )? ) | <expression> ) FROM )? <expression> <rparen> )
- ( ( TO_CHARS | TO_BYTES ) <lparen> <expression> <comma> <string> ( <comma> <expression> )? <rparen> )
- ( ( TIMESTAMPADD | TIMESTAMPDIFF ) <lparen> <time interval> <comma> <expression> <comma> <expression> <rparen> )
- <querystring function>
- ( ( LEFT | RIGHT | CHAR | USER | YEAR | MONTH | HOUR | MINUTE | SECOND | XMLCONCAT | XMLCOMMENT | XMLTEXT ) <lparen> ( <expression list> )? <rparen> )
- ( ( TRANSLATE | INSERT ) <lparen> ( <expression list> )? <rparen> )
- <xml parse>
- <xml element>
- ( XMLPI <lparen> ( ( NAME )? <identifier> ) ( <comma> <expression> )? <rparen> )
- <xml forest>
- <json object>
- <xml serialize>
- <xml query>
- ( POSITION <lparen> <common value expression> IN <common value expression> <rparen> )
- ( LISTAGG <lparen> <expression> ( <comma> <string> )? <rparen> WITHIN GROUP <lparen> <order by clause> <rparen> )
- ( <identifier> <lparen> ( ALL | DISTINCT )? ( <expression list> )? ( <order by clause> )? <rparen> ( <filter clause> )? )
- ( CURRENT_DATE ( <lparen> <rparen> )? )
- ( ( CURRENT_TIMESTAMP | CURRENT_TIME ) ( <lparen> <unsigned integer> <rparen> )? )
Calls a scalar function.
Example:
func('1', col1)
func('1', col1)
12.6.125. xml parse ::=
- XMLPARSE <lparen> ( DOCUMENT | CONTENT ) <expression> ( WELLFORMED )? <rparen>
Parses the given value as XML.
Example:
XMLPARSE(DOCUMENT doc WELLFORMED)
XMLPARSE(DOCUMENT doc WELLFORMED)
12.6.126. querystring function ::=
- QUERYSTRING <lparen> <expression> ( <comma> <derived column> )* <rparen>
Produces a URL query string from the given arguments.
Example:
QUERYSTRING('path', col1 AS opt, col2 AS val)
QUERYSTRING('path', col1 AS opt, col2 AS val)
12.6.127. xml element ::=
- XMLELEMENT <lparen> ( ( NAME )? <identifier> ) ( <comma> <xml namespaces> )? ( <comma> <xml attributes> )? ( <comma> <expression> )* <rparen>
Creates an XML element.
Example:
XMLELEMENT(NAME "root", child)
XMLELEMENT(NAME "root", child)
12.6.128. xml attributes ::=
- XMLATTRIBUTES <lparen> <derived column> ( <comma> <derived column> )* <rparen>
Creates attributes for the containing element.
Example:
XMLATTRIBUTES(col1 AS attr1, col2 AS attr2)
XMLATTRIBUTES(col1 AS attr1, col2 AS attr2)
12.6.129. json object ::=
Produces a JSON object containing name value pairs.
Example:
JSONOBJECT(col1 AS val1, col2 AS val2)
JSONOBJECT(col1 AS val1, col2 AS val2)
12.6.130. derived column list ::=
- <derived column> ( <comma> <derived column> )*
a list of name value pairs
Example:
col1 AS val1, col2 AS val2
col1 AS val1, col2 AS val2
12.6.131. xml forest ::=
- XMLFOREST <lparen> ( <xml namespaces> <comma> )? <derived column list> <rparen>
Produces an element for each derived column.
Example:
XMLFOREST(col1 AS ELEM1, col2 AS ELEM2)
XMLFOREST(col1 AS ELEM1, col2 AS ELEM2)
12.6.132. xml namespaces ::=
- XMLNAMESPACES <lparen> <xml namespace element> ( <comma> <xml namespace element> )* <rparen>
Defines XML namespace URI/prefix combinations
Example:
XMLNAMESPACES('http://foo' AS foo)
XMLNAMESPACES('http://foo' AS foo)
12.6.133. xml namespace element ::=
An xml namespace
Example:
NO DEFAULT
NO DEFAULT
12.6.134. simple data type ::=
- ( STRING ( <lparen> <unsigned integer> <rparen> )? )
- ( VARCHAR ( <lparen> <unsigned integer> <rparen> )? )
- BOOLEAN
- BYTE
- TINYINT
- SHORT
- SMALLINT
- ( CHAR ( <lparen> <unsigned integer> <rparen> )? )
- INTEGER
- LONG
- BIGINT
- ( BIGINTEGER ( <lparen> <unsigned integer> <rparen> )? )
- FLOAT
- REAL
- DOUBLE
- ( BIGDECIMAL ( <lparen> <unsigned integer> ( <comma> <unsigned integer> )? <rparen> )? )
- ( DECIMAL ( <lparen> <unsigned integer> ( <comma> <unsigned integer> )? <rparen> )? )
- DATE
- TIME
- ( TIMESTAMP ( <lparen> <unsigned integer> <rparen> )? )
- ( OBJECT ( <lparen> <unsigned integer> <rparen> )? )
- ( BLOB ( <lparen> <unsigned integer> <rparen> )? )
- ( CLOB ( <lparen> <unsigned integer> <rparen> )? )
- JSON
- ( VARBINARY ( <lparen> <unsigned integer> <rparen> )? )
- GEOMETRY
- GEOGRAPHY
- XML
A non-collection data type.
Example:
STRING
STRING
12.6.135. basic data type ::=
- <simple data type> ( <lsbrace> <rsbrace> )*
A data type.
Example:
STRING[]
STRING[]
12.6.136. data type ::=
- <basic data type>
- ( ( <identifier> | <basicNonReserved> ) ( <lsbrace> <rsbrace> )* )
A data type.
Example:
STRING[]
STRING[]
12.6.137. time interval ::=
A time interval keyword.
Example:
SQL_TSI_HOUR
SQL_TSI_HOUR
12.6.138. non numeric literal ::=
An escaped or simple non numeric literal.
Example:
'a'
'a'
12.6.139. unsigned numeric literal ::=
An unsigned numeric literal value.
Example:
1.234
1.234
12.6.140. ddl statement ::=
- <create table> ( <create table> | <create procedure> )?
- <option namespace>
- <alterStatement>
- <create trigger>
- <create a domain or type alias>
- <create server>
- <create role >
- <drop role >
- <GRANT>
- <Revoke GRANT>
- <CREATE POLICY>
- <DROP POLICY>
- <drop server>
- <drop table>
- <Import foreign schema>
- <Import another Database>
- <create database>
- <use database>
- <drop schema>
- <set schema>
- <create schema>
- <create procedure> ( <ddl statement> )?
- <create data wrapper>
- <Drop data wrapper>
- <drop procedure>
A data definition statement.
Example:
CREATE FOREIGN TABLE X (Y STRING)
CREATE FOREIGN TABLE X (Y STRING)
12.6.141. option namespace ::=
- SET NAMESPACE <string> AS <identifier>
A namespace used to shorten the full name of an option key.
Example:
SET NAMESPACE 'http://foo' AS foo
SET NAMESPACE 'http://foo' AS foo
12.6.142. create database ::=
- CREATE DATABASE <identifier> ( VERSION <string> )? ( <options clause> )?
create a new database
Example:
CREATE DATABASE foo OPTIONS(x 'y')
CREATE DATABASE foo OPTIONS(x 'y')
12.6.143. use database ::=
- USE DATABASE <identifier> ( VERSION <string> )?
database into working context
Example:
USE DATABASE foo
USE DATABASE foo
12.6.144. create schema ::=
- CREATE ( VIRTUAL | FOREIGN )? SCHEMA <identifier> ( SERVER <identifier list> )? ( <options clause> )?
create a schema in database
Example:
CREATE VIRTUAL SCHEMA foo SERVER (s1,s2,s3);
CREATE VIRTUAL SCHEMA foo SERVER (s1,s2,s3);
12.6.145. drop schema ::=
- DROP ( VIRTUAL | FOREIGN )? SCHEMA <identifier>
drop a schema in database
Example:
DROP SCHEMA foo
DROP SCHEMA foo
12.6.146. set schema ::=
set the schema for subsequent ddl statements
Example:
SET SCHEMA foo
SET SCHEMA foo
12.6.147. create a domain or type alias ::=
- CREATE DOMAIN ( <identifier> | <basicNonReserved> ) ( AS )? <data type> ( NOT NULL )?
creates a named type with optional constraints
Example:
CREATE DOMAIN my_type AS INTEGER NOT NULL
CREATE DOMAIN my_type AS INTEGER NOT NULL
12.6.148. create data wrapper ::=
- CREATE FOREIGN ( ( DATA WRAPPER ) | TRANSLATOR ) <Unqualified identifier> ( ( TYPE | HANDLER ) <identifier> )? ( <options clause> )?
Defines a translator; use the options to override the translator properties.
Example:
CREATE FOREIGN DATA WRAPPER wrapper OPTIONS (x true)
CREATE FOREIGN DATA WRAPPER wrapper OPTIONS (x true)
12.6.149. Drop data wrapper ::=
- DROP FOREIGN ( ( DATA WRAPPER ) | TRANSLATOR ) <identifier>
Deletes a translator
Example:
DROP FOREIGN DATA WRAPPER wrapper
DROP FOREIGN DATA WRAPPER wrapper
12.6.150. create role ::=
- CREATE ROLE <Unqualified identifier> ( WITH <with role> )?
Defines data role for the database
Example:
CREATE ROLE lowly WITH FOREIGN ROLE "role"
CREATE ROLE lowly WITH FOREIGN ROLE "role"
12.6.151. with role ::=
- ( ANY AUTHENTICATED )
- ( ( JAAS | FOREIGN ) ROLE <identifier list> )
12.6.152. drop role ::=
Removes data role for the database
Example:
DROP ROLE <data-role>
DROP ROLE <data-role>
12.6.153. CREATE POLICY ::=
CREATE row level policy
Example:
CREATE POLICY pname ON tbl FOR SELECT,INSERT TO role USING col = user();
CREATE POLICY pname ON tbl FOR SELECT,INSERT TO role USING col = user();
12.6.154. DROP POLICY ::=
- DROP POLICY <identifier> ON ( <identifier> | ( PROCEDURE <identifier> ) ) TO <identifier>
DROP row level policy
Example:
----DROP POLICY pname ON tbl TO role ----
----DROP POLICY pname ON tbl TO role
----
12.6.155. GRANT ::=
- GRANT ( ( ( <grant type> ( <comma> <grant type> )* )? ON ( TABLE <identifier> ( CONDITION ( ( NOT )? CONSTRAINT )? <string> )? | FUNCTION <identifier> | PROCEDURE <identifier> ( CONDITION ( ( NOT )? CONSTRAINT )? <string> )? | SCHEMA <identifier> | COLUMN <identifier> ( MASK ( ORDER <unsigned integer> )? <string> )? ) ) | ( ALL PRIVILEGES ) | ( TEMPORARY TABLE ) | ( USAGE ON LANGUAGE <identifier> ) ) TO <identifier>
Defines GRANT for a role
Example:
GRANT SELECT ON TABLE x.y TO role
GRANT SELECT ON TABLE x.y TO role
12.6.156. Revoke GRANT ::=
- REVOKE ( ( ( <grant type> ( <comma> <grant type> )* )? ON ( TABLE <identifier> ( CONDITION )? | FUNCTION <identifier> | PROCEDURE <identifier> ( CONDITION )? | SCHEMA <identifier> | COLUMN <identifier> ( MASK )? ) ) | ( ALL PRIVILEGES ) | ( TEMPORARY TABLE ) | ( USAGE ON LANGUAGE <identifier> ) ) FROM <identifier>
Revokes GRANT for a role
Example:
REVOKE SELECT ON TABLE x.y TO role
REVOKE SELECT ON TABLE x.y TO role
12.6.157. create server ::=
- CREATE SERVER <Unqualified identifier> ( TYPE <string> )? ( VERSION <string> )? FOREIGN ( ( DATA WRAPPER ) | TRANSLATOR ) <Unqualified identifier> ( <options clause> )?
Defines a connection to a source
Example:
CREATE SERVER "h2-connector" FOREIGN DATA WRAPPER h2 OPTIONS ("resource-name" 'java:/accounts-ds');
CREATE SERVER "h2-connector" FOREIGN DATA WRAPPER h2 OPTIONS ("resource-name" 'java:/accounts-ds');
12.6.158. drop server ::=
Defines dropping connection to foreign source
Example:
DROP SERVER server_name
DROP SERVER server_name
12.6.159. create procedure ::=
- CREATE ( VIRTUAL | FOREIGN )? ( PROCEDURE | FUNCTION ) <Unqualified identifier> ( <lparen> ( <procedure parameter> ( <comma> <procedure parameter> )* )? <rparen> ( RETURNS ( <options clause> )? ( ( ( TABLE )? <lparen> <procedure result column> ( <comma> <procedure result column> )* <rparen> ) | <data type> ) )? ( <options clause> )? ( AS <statement> )? )
Defines a procedure or function invocation.
Example:
CREATE FOREIGN PROCEDURE proc (param STRING) RETURNS STRING
CREATE FOREIGN PROCEDURE proc (param STRING) RETURNS STRING
12.6.160. drop procedure ::=
Drops a table or view.
Example:
DROP FOREIGN TABLE table-name
DROP FOREIGN TABLE table-name
12.6.161. procedure parameter ::=
- ( IN | OUT | INOUT | VARIADIC )? <identifier> <data type> ( NOT NULL )? ( RESULT )? ( DEFAULT <expression> )? ( <options clause> )?
A procedure or function parameter
Example:
OUT x INTEGER
OUT x INTEGER
12.6.162. procedure result column ::=
- <identifier> <data type> ( NOT NULL )? ( <options clause> )?
A procedure result column.
Example:
x INTEGER
x INTEGER
12.6.163. create table ::=
Defines a table or view.
Example:
CREATE VIEW vw AS SELECT 1
CREATE VIEW vw AS SELECT 1
12.6.164. create foreign or global temporary table ::=
- ( ( FOREIGN TABLE ) | ( GLOBAL TEMPORARY TABLE ) ) <Unqualified identifier> <create table body>
Defines a foreign or global temporary table.
Example:
FOREIGN TABLE ft (col integer)
FOREIGN TABLE ft (col integer)
12.6.165. create view ::=
- ( VIRTUAL )? VIEW <Unqualified identifier> ( <create view body> | ( <options clause> )? ) AS <query expression>
Defines a view.
Example:
VIEW vw AS SELECT 1
VIEW vw AS SELECT 1
12.6.166. drop table ::=
Drops a table or view.
Example:
DROP VIEW name
DROP VIEW name
12.6.167. create foreign temp table ::=
Defines a foreign temp table
Example:
CREATE FOREIGN TEMPORARY TABLE t (x string) ON z
CREATE FOREIGN TEMPORARY TABLE t (x string) ON z
12.6.168. create table body ::=
- <lparen> <table element> ( <comma> ( <table constraint> | <table element> ) )* <rparen> ( <options clause> )?
Defines a table.
Example:
(x string) OPTIONS (CARDINALITY 100)
(x string) OPTIONS (CARDINALITY 100)
12.6.169. create view body ::=
- <lparen> <view element> ( <comma> ( <table constraint> | <view element> ) )* <rparen> ( <options clause> )?
Defines a view.
Example:
(x) OPTIONS (CARDINALITY 100)
(x) OPTIONS (CARDINALITY 100)
12.6.170. table constraint ::=
- ( CONSTRAINT <identifier> )? ( <primary key> | <other constraints> | <foreign key> ) ( <options clause> )?
Defines a constraint on a table or view.
Example:
FOREIGN KEY (a, b) REFERENCES tbl (x, y)
FOREIGN KEY (a, b) REFERENCES tbl (x, y)
12.6.171. foreign key ::=
- FOREIGN KEY <column list> REFERENCES <identifier> ( <column list> )?
Defines the foreign key referential constraint.
Example:
FOREIGN KEY (a, b) REFERENCES tbl (x, y)
FOREIGN KEY (a, b) REFERENCES tbl (x, y)
12.6.172. primary key ::=
Defines the primary key.
Example:
PRIMARY KEY (a, b)
PRIMARY KEY (a, b)
12.6.173. other constraints ::=
- ( ( UNIQUE | ACCESSPATTERN ) <column list> )
- ( INDEX <lparen> <expression list> <rparen> )
Defines ACCESSPATTERN and UNIQUE constraints and INDEXes.
Example:
UNIQUE (a)
UNIQUE (a)
12.6.174. column list ::=
- <lparen> <identifier> ( <comma> <identifier> )* <rparen>
A list of column names.
Example:
(a, b)
(a, b)
12.6.175. table element ::=
- <identifier> ( SERIAL | ( <data type> ( NOT NULL )? ( AUTO_INCREMENT )? ) ) <post create column>
Defines a table column.
Example:
x INTEGER NOT NULL
x INTEGER NOT NULL
12.6.176. view element ::=
- <identifier> ( SERIAL | ( <data type> ( NOT NULL )? ( AUTO_INCREMENT )? ) )? <post create column>
Defines a view column with optional type.
Example:
x INTEGER NOT NULL
x INTEGER NOT NULL
12.6.177. post create column ::=
- ( <inline constraint> )? ( DEFAULT <expression> )? ( <options clause> )?
Common options trailing a column
Example:
PRIMARY KEY
PRIMARY KEY
12.6.178. inline constraint ::=
Defines a constraint on a single column
Example:
x INTEGER PRIMARY KEY
x INTEGER PRIMARY KEY
12.6.179. options clause ::=
- OPTIONS <lparen> <option pair> ( <comma> <option pair> )* <rparen>
A list of statement options.
Example:
OPTIONS ('x' 'y', 'a' 'b')
OPTIONS ('x' 'y', 'a' 'b')
12.6.180. option pair ::=
- <identifier> ( <non numeric literal> | ( <plus or minus> )? <unsigned numeric literal> )
An option key/value pair.
Example:
'key' 'value'
'key' 'value'
12.6.181. alter option pair ::=
- <identifier> ( <non numeric literal> | ( <plus or minus> )? <unsigned numeric literal> )
Alter An option key/value pair.
Example:
'key' 'value'
'key' 'value'
12.6.182. alterStatement ::=
- ALTER ( <ALTER TABLE > | <ALTER PROCEDURE > | <ALTER TRIGGER > | <ALTER SERVER > | <ALTER DATA WRAPPER > | <ALTER DATABASE > )
12.6.183. ALTER TABLE ::=
- ( ( ( VIRTUAL )? VIEW <identifier> ) | ( ( FOREIGN )? TABLE <identifier> ) ) ( ( AS <query expression> ) | <ADD column> | <ADD constraint> | <alter options list> | <DROP column> | ( ALTER COLUMN <alter column options> ) | ( RENAME ( <RENAME Table> | ( COLUMN <rename column options> ) ) ) )
alters options of database
Example:
ALTER TABLE foo ADD COLUMN x xml
ALTER TABLE foo ADD COLUMN x xml
12.6.184. RENAME Table ::=
- TO <identifier>
alters table name
Example:
ALTER TABLE foo RENAME TO BAR;
ALTER TABLE foo RENAME TO BAR;
12.6.185. ADD constraint ::=
alters table and adds a constraint
Example:
ADD PRIMARY KEY (ID)
ADD PRIMARY KEY (ID)
12.6.186. ADD column ::=
alters table and adds a column
Example:
ADD COLUMN bar type OPTIONS (ADD updatable true)
ADD COLUMN bar type OPTIONS (ADD updatable true)
12.6.187. DROP column ::=
alters table and adds a column
Example:
DROP COLUMN bar
DROP COLUMN bar
12.6.188. alter column options ::=
- <identifier> ( ( TYPE ( SERIAL | ( <data type> ( NOT NULL )? ( AUTO_INCREMENT )? ) ) ) | <alter child options list> )
alters a set of column options
Example:
ALTER COLUMN bar OPTIONS (ADD updatable true)
ALTER COLUMN bar OPTIONS (ADD updatable true)
12.6.189. rename column options ::=
- <identifier> TO <identifier>
renames either a table column or procedure’s parameter name
Example:
RENAME COLUMN bar TO foo
RENAME COLUMN bar TO foo
12.6.190. ALTER PROCEDURE ::=
- ( VIRTUAL | FOREIGN )? PROCEDURE <identifier> ( ( AS <statement> ) | <alter options list> | ( ALTER PARAMETER <alter column options> ) | ( RENAME PARAMETER <rename column options> ) )
alters options of database
Example:
ALTER PROCEDURE foo OPTIONS (ADD x y)
ALTER PROCEDURE foo OPTIONS (ADD x y)
12.6.191. ALTER TRIGGER ::=
alters options of table triggers
Example:
ALTER TRIGGER ON vw INSTEAD OF INSERT ENABLED
ALTER TRIGGER ON vw INSTEAD OF INSERT ENABLED
12.6.192. ALTER SERVER ::=
alters options of database
Example:
ALTER SERVER foo OPTIONS (ADD x y)
ALTER SERVER foo OPTIONS (ADD x y)
12.6.193. ALTER DATA WRAPPER ::=
- ( ( DATA WRAPPER ) | TRANSLATOR ) <identifier> <alter options list>
alters options of data wrapper
Example:
ALTER DATA WRAPPER foo OPTIONS (ADD x y)
ALTER DATA WRAPPER foo OPTIONS (ADD x y)
12.6.194. ALTER DATABASE ::=
alters options of database
Example:
ALTER DATABASE foo OPTIONS (ADD x y)
ALTER DATABASE foo OPTIONS (ADD x y)
12.6.195. alter options list ::=
- OPTIONS <lparen> ( <add set option> | <drop option> ) ( <comma> ( <add set option> | <drop option> ) )* <rparen>
a list of alterations to options
Example:
OPTIONS (ADD updatable true)
OPTIONS (ADD updatable true)
12.6.196. drop option ::=
drop option
Example:
DROP updatable
DROP updatable
12.6.197. add set option ::=
- ( ADD | SET ) <alter option pair>
add or set an option pair
Example:
ADD updatable true
ADD updatable true
12.6.198. alter child options list ::=
- OPTIONS <lparen> ( <add set child option> | <drop option> ) ( <comma> ( <add set child option> | <drop option> ) )* <rparen>
a list of alterations to options
Example:
OPTIONS (ADD updatable true)
OPTIONS (ADD updatable true)
12.6.199. drop option ::=
drop option
Example:
DROP updatable
DROP updatable
12.6.200. add set child option ::=
- ( ADD | SET ) <alter child option pair>
add or set an option pair
Example:
ADD updatable true
ADD updatable true
12.6.201. alter child option pair ::=
- <identifier> ( <non numeric literal> | ( <plus or minus> )? <unsigned numeric literal> )
Alter An option key/value pair.
Example:
'key' 'value'
'key' 'value'
12.6.202. Import foreign schema ::=
- IMPORT ( FOREIGN SCHEMA <identifier> )? FROM ( SERVER | REPOSITORY ) <identifier> INTO <identifier> ( <options clause> )?
imports schema metadata from server
Example:
IMPORT FOREIGN SCHEMA foo FROM SERVER bar
IMPORT FOREIGN SCHEMA foo FROM SERVER bar
12.6.203. Import another Database ::=
imports another database into current database
Example:
IMPORT DATABASE vdb VERSION '1.2.3' WITH ACCESS CONTROL]
IMPORT DATABASE vdb VERSION '1.2.3' WITH ACCESS CONTROL]
12.6.204. identifier list ::=
- <identifier> ( <comma> <identifier> )*
12.6.205. grant type ::=
'%20d='M201.5%20305.5c-13.8%200-24.9-11.1-24.9-24.6%200-13.8%2011.1-24.9%2024.9-24.9%2013.6%200%2024.6%2011.1%2024.6%2024.9%200%2013.6-11.1%2024.6-24.6%2024.6zM504%20256c0%20137-111%20248-248%20248S8%20393%208%20256%20119%208%20256%208s248%20111%20248%20248zm-132.3-41.2c-9.4%200-17.7%203.9-23.8%2010-22.4-15.5-52.6-25.5-86.1-26.6l17.4-78.3%2055.4%2012.5c0%2013.6%2011.1%2024.6%2024.6%2024.6%2013.8%200%2024.9-11.3%2024.9-24.9s-11.1-24.9-24.9-24.9c-9.7%200-18%205.8-22.1%2013.8l-61.2-13.6c-3-.8-6.1%201.4-6.9%204.4l-19.1%2086.4c-33.2%201.4-63.1%2011.3-85.5%2026.8-6.1-6.4-14.7-10.2-24.1-10.2-34.9%200-46.3%2046.9-14.4%2062.8-1.1%205-1.7%2010.2-1.7%2015.5%200%2052.6%2059.2%2095.2%20132%2095.2%2073.1%200%20132.3-42.6%20132.3-95.2%200-5.3-.6-10.8-1.9-15.8%2031.3-16%2019.8-62.5-14.9-62.5zM302.8%20331c-18.2%2018.2-76.1%2017.9-93.6%200-2.2-2.2-6.1-2.2-8.3%200-2.5%202.5-2.5%206.4%200%208.6%2022.8%2022.8%2087.3%2022.8%20110.2%200%202.5-2.2%202.5-6.1%200-8.6-2.2-2.2-6.1-2.2-8.3%200zm7.7-75c-13.6%200-24.6%2011.1-24.6%2024.9%200%2013.6%2011.1%2024.6%2024.6%2024.6%2013.8%200%2024.9-11.1%2024.9-24.6%200-13.8-11-24.9-24.9-24.9z'/%3e%3c/svg%3e){kind=small}