Grammar Element Definitions
The following is an alphabetical list of element definitions used in the grammar syntax.
alter-options
::=
alter-option-list1
|
alter-option-list2
alter-option-list1
::=
alter-option
|(
alter-option
[,
alter-option
]...)
alter-option
::=
ADD
[
COLUMN
]
column-definition
|
ADD
table-constraint-definition
|
ALTER
[
COLUMN
]
column-definition
|
DROP
[
COLUMN
]
column-name
|
DROP
CONSTRAINT
constraint-name
|
DROP
PRIMARY KEY
|
MODIFY
[
COLUMN
]
column-definition
alter-option-list2
::=
PSQL_MOVE
[
COLUMN
]
column-name
TO
[ [
PSQL_PHYSICAL
]
PSQL_POSITION
]
new-column-position
|
RENAME COLUMN
column-name
TO
new-column-name
as-or-semicolon
::=
AS
|
;
before-or-after
::=
BEFORE
|
AFTER
call-arguments
::=
positional-argument
[ ,
positional-argument
]...
col-constraint
::=
NOT NULL
|
NOT MODIFIABLE
|
UNIQUE
| PRIMARY KEY
|
REFERENCES
table-name
[ (
column-name
) ] [
referential-actions
]
collation-name
::= '
string
' |
user-defined-name
column-constraint
::= [
CONSTRAINT
constraint-name
]
col-constraint
column-definition
::=
column-name data-type
[
DEFAULT
Expression
] [
column-constraint
[
column-constraint
]... [
CASE
|
COLLATE
collation-name
]
column-name
::=
user-defined-name
commit-statement
::= see COMMIT statement
comparison-operator
::= < | > | <= | >= | = | <> | !=
constraint-name
::=
user-defined-name
correlation-name
::=
user-defined-name
cursor-name
::=
user-defined-name
data-type
::=
data-type-name
[ (
precision
[ ,
scale
] ) ]
data-type-name
::=
see
PSQL Supported Data Types
db-name
::=
user-defined-name
expression
::=
expression
-
expression
|
expression
+
expression
|
expression
*
expression
|
expression
/
expression
|
expression
&
expression
|
expression
|
expression
|
expression
^
expression
| (
expression
)
| -
expression
| +
expression
| ~
expression
| ?
|
literal
|
scalar-function
| { fn
scalar-function
}
| USER
literal
::= '
string
' |
N
'
string
'
|
number
| { d '
date-literal
' }
| { t '
time-literal
' }
| { ts '
timestamp-literal
' }
scalar-function
:: =
See
Scalar Functions
expression-or-subquery
::=
expression
| (
query-specification
)
fetch-orientation
::=
NEXT
group-name
::=
user-defined-name
index-definition
::= (
index-segment-definition
[ ,
index-segment-definition
]... )
index-name
::=
user-defined-name
index-number
::=
user-defined-value
(an integer between 0 and 118)
index-segment-definition
::=
column-name
[
ASC
|
DESC
]
ins-upd-del
::=
INSERT
|
UPDATE
|
DELETE
insert-values
::=
values-clause
|
query-specification
join-definition
::=
table-reference
[
INNER
]
JOIN
table-reference
ON
search-condition
|
table-reference
CROSS JOIN
table-reference
|
outer-join-definition
label-name
::=
user-defined-name
literal
::= '
string
' |
N
'
string
'
|
number
| { d '
date-literal
' }
| { t '
time-literal
' }
| { ts '
timestamp-literal
' }
order-by-expression
::=
expression
[
CASE
|
COLLATE
collation-name
] [
ASC
|
DESC
]
outer-join-definition
::=
table-reference outer-join-type
JOIN
table-reference
ON
search-condition
outer-join-type
::=
LEFT
[
OUTER
]|
RIGHT
[
OUTER
] |
FULL
[
OUTER
]
parameter
::=
parameter-type-name
data-type
[
DEFAULT
proc-expr
| =
proc-expr
]
|
SQLSTATE
parameter-type-name ::= parameter-name
| parameter-type parameter-name
| parameter-name parameter-type
parameter-type
::=
IN
|
OUT
|
INOUT
|
IN_OUT
parameter-name
::= [ : ]
user-defined-name
password
::=
user-defined-name
| '
string
'
positional-argument
::=
expression
precision
::=
integer
predicate
::=
expression
[
NOT
]
BETWEEN
expression
AND
expression
|
expression comparison-operator expression-or-subquery
|
expression
[
NOT
]
IN
(
query-specification
)
|
expression
[
NOT
]
IN
(
value
[ ,
value
]... )
|
expression
[
NOT
]
LIKE
value
|
expression
IS
[
NOT
]
NULL
|
expression comparison-operator
ANY
(
query-specification
)
|
expression comparison-operator
ALL
(
query-specification
)
|
EXISTS
(
query-specification
)
proc-expr
::=
same as normal expression but does not allow IF expression, or scalar functions
proc-search-condition
::=
same as
search-condition
but does not allow expressions with subqueries
proc-stmt
::=
[
label-name
: ]
BEGIN
[
ATOMIC
] [
proc-stmt
[ ;
proc-stmt
]
...
]
END
[
label-name
]
|
CALL
procedure-name
(
proc-expr
[ ,
proc-expr
]
...
)
|
CLOSE
cursor-name
|
DECLARE
cursor-name
CURSOR FOR
select-statement
[
FOR UPDATE
|
FOR READ ONLY
]
|
DECLARE
variable-name
data-type
[
DEFAULT
proc-expr
| =
proc-expr
]
|
DELETE WHERE CURRENT OF
cursor-name
|
delete-statement
|
FETCH
[
fetch-orientation
[
FROM
] ]
cursor-name
[
INTO
variable-name
[ ,
variable-name
] ]
|
IF
proc-search-condition
THEN
proc-stmt
[ ;
proc-stmt
]... [
ELSE
proc-stmt
[ ;
proc-stmt
]... ]
END IF
|
IF
proc-search-condition proc-stmt
[
ELSE
proc-stmt
]
|
insert-statement
|
LEAVE
label-name
| [
label-name
: ]
LOOP
proc-stmt
[ ;
proc-stmt
]...
END
LOOP
[
label-name
]
|
OPEN
cursor-name
|
PRINT
proc-expr
[ , '
string
' ]
|
RETURN
[
proc-expr
]
|
transaction-statement
|
select-statement-with-into
|
select-statement
|
SET
variable-name
=
proc-expr
|
SIGNAL
[
ABORT
]
sqlstate-value
|
START
TRANSACTION
[
tran-name
]
|
update-statement
|
UPDATE SET
column-name
=
proc-expr
[ ,
column-name
=
proc-expr
]...
WHERE CURRENT OF
cursor-name
| [
label-name
: ]
WHILE
proc-search-condition
DO
[
proc-stmt
[ ;
proc-stmt
] ]...
END WHILE
[
label-name
]
| [
label-name
: ]
WHILE
proc-search-condition proc-stmt
|
alter-table-statement
|
create-index-statement
|
create-table-statement
|
create-view-statement
|
drop-index-statement
|
drop-table-statement
|
drop-view-statement
|
grant-statement
|
revoke-statement
|
set-statement
procedure-name
::=
user-defined-name
public-or-user-group-name
::=
PUBLIC
|
user-group-name
query-specification
[ [
UNION
[
ALL
]
query-specification
]...
[
limit-clause
][
ORDER BY
order-by-expression
[ ,
order-by-expression
]... ] [
FOR UPDATE
]
query-specification
::= (
query-specification
)
|
SELECT
[
ALL
|
DISTINCT
] [
top-clause
]
select-list
FROM
table-reference
[ ,
table-reference
]...
[
WHERE
search-condition
]
[
GROUP BY
expression
[ ,
expression
]...
[
HAVING
search-condition
] ]
referencing-alias
::=
OLD
[
AS
]
correlation-name
[
NEW
[
AS
]
correlation-name
]
|
NEW
[
AS
]
correlation-name
[
OLD
[
AS
]
correlation-name ]
referential-actions
::=
referential-update-action
[
referential-delete-action
]
|
referential-delete-action
[
referential-update-action
]
referential-update-action
::=
ON UPDATE RESTRICT
referential-delete-action
::=
ON DELETE CASCADE
|
ON DELETE RESTRICT
release-statement
::= see RELEASE statement
result
::=
user-defined-name data-type
rollback-statement
::= see ROLLBACK WORK statement
savepoint-name
::=
user-defined-name
scalar-function
::=
see Scalar Function list
scale
::= integer
search-condition
::=
search-condition
AND
search-condition
|
search-condition
OR
search-condition
|
NOT
search-condition
| (
search-condition
)
|
predicate
select-item
::=
expression
[ [
AS
]
alias-name
] |
table-name
. *
select-list
::= * |
select-item
[ ,
select-item
]...
set-function
::=
AVG
( [
DISTINCT
|
ALL
]
expression
)
|
COUNT
( < * | [
DISTINCT
|
ALL
]
expression
> )
|
COUNT_BIG
( < * | [
DISTINCT
|
ALL
]
expression
> )
|
MAX
( [
DISTINCT
|
ALL
]
expression
)
|
MIN
( [
DISTINCT
|
ALL
]
expression
)
|
STDEV
( [
DISTINCT
|
ALL
]
expression
)
|
STDEVP
( [
DISTINCT
|
ALL
]
expression
)
|
SUM
( [
DISTINCT
|
ALL
]
expression
)
|
VAR
( [
DISTINCT
|
ALL
]
expression
)
|
VARP
( [
DISTINCT
|
ALL
]
expression
)
sqlstate-value
::= '
string
'
table-constraint-definition
::= [
CONSTRAINT
constraint-name
]
table-constraint
table-constraint
::=
UNIQUE
(
column-name
[ ,
column-name
]... )
|
PRIMARY KEY
(
column-name
[ ,
column-name
]... )
|
FOREIGN KEY
(
column-name
[ ,
column-name
] )
REFERENCES
table-name
[ (
column-name
[ ,
column-name
]... ) ]
[
referential-actions
]
table-element
::=
column-definition
|
table-constraint-definition
table-expression
::=
FROM
table-reference
[ ,
table-reference
]...
[
WHERE
search-condition
]
[
GROUP BY
expression
[ ,
expression
]...
[
HAVING
search-condition
]
table-name
::=
user-defined-name
table-privilege
::=
ALL
|
SELECT
[ (
column-name
[ ,
column-name
]... ) ]
|
UPDATE
[ (
column-name
[ ,
column-name
]... ) ]
|
INSERT
[ (
column-name
[ ,
column-name
]... ) ]
|
DELETE
|
ALTER
|
REFERENCES
table-reference
::= {
OJ
outer-join-definition
}
| [
db-name
.]
table-name
[ [
AS
]
alias-name
]
|
join-definition
| (
join-definition
)
table-subquery
::=
query-specification
[ [
UNION
[
ALL
]
query-specification
]...][
limit-clause
][
ORDER BY
order-by-expression
[ ,
order-by-expression
]...]
limit-clause
::= [
LIMIT
[
offset
,]
row_count
|
row_count
OFFSET
offset
|
ALL
[
OFFSET
offset
] ]
offset
::=
number
| ?
row_count
::=
number
| ?
transaction-statement
::=
commit-statement
|
rollback-statement
|
release-statement
trigger-name
::=
user-defined-name
user_and_password
::=
user-name
[ : ]
password
user-group-name
::=
user-name
|
group-name
user-name
::=
user-defined-name
value
::=
literal
|
USER
|
NULL
| ?
value-list
::= (
value
[ ,
value
]... )
values-clause
::=
DEFAULT VALUES
|
VALUES
(
expression
[ ,
expression
]... )
variable-name
::=
user-defined-name
view-name
::=
user-defined-name
SQL Statement List
SqlStatementList is defined as:
SqlStatementList
statement
';' |
SqlStatementList
';'
statement
::=
statement-label
':'
statement
|
BEGIN ... END
block
|
CALL
statement
|
CLOSE CURSOR
statement
|
COMMIT
statement
|
DECLARE CURSOR
statement
|
DECLARE
variable statement
|
DELETE
statement
|
FETCH
statement
|
IF
statement
|
INSERT
statement
|
LEAVE
statement
|
LOOP
statement
|
OPEN
statement
|
PRINT
statement
|
RELEASE SAVEPOINT
statement
|
RETURN
statement
|
ROLLBACK
statement
|
SAVEPOINT
statement
|
SELECT
statement
|
SET
statement
|
SIGNAL
statement
|
START TRANSACTION
statement
|
UPDATE
statement
|
WHILE
statement
Predicate
A predicate is defined as:
expression
compare-operator
expression
|
expression
[
NOT
]
BETWEEN
expression
AND
expression
|
expression
[
NOT
]
LIKE
string-literal
|
expression
IS
[
NOT
]
NULL
|
NOT
predicate
|
predicate
AND
predicate
|
predicate
OR
predicate
| '('
predicate
')'
compare-operator
::= '=' | '>=' | '>' | '<=' | '<' | '<>' | '!='
| [
NOT
]
IN
value-list
Expression
An expression is defined as:
number
|
string-literal
|
column-name
|
variable-name
|
NULL
|
CONVERT
'('
expression
','
data-type
')'
| '-'
expression
|
expression
'+'
expression
|
expression
'-'
expression
|
expression
'*'
expression
|
expression
'/'
expression
|
expression
'&'
expression
| '~'
expression
|
expression
'|'
expression
|
expression
'^'
expression
|
function-name
'(' [
expression-list
] ')'
| '('
expression
')'
| '{' D
string-literal
'}'
| '{' T
string-literal
'}'
| '{' TS
string-literal
'}'
|
@:IDENTITY
|
@:ROWCOUNT
|
@@IDENTITY
|
@@ROWCOUNT
An expression list is defined as:
expression-list
::=
expression
[ ,
expression
... ]