Syntax

This section describes the syntax added to PostgreSQL Enhanced Edition on the basis of PostgreSQL 11 open source edition. The following are supported:

• CREATE SEQUENCE
• CREATE/ALTER DATABASE
• CREATE/ALTER VIEW
• CREATE TABLE
• CREATE TABLESPACE
• CLUSTER
• FORALL
• CREATE/DROP DIRECTORY
• ALTER TABLE ADD CONSTRAINT USING INDEX
• Table names or table aliases for target columns in the INSERT INTO statement
• ROWNUM in non-partitioned tables
• CREATE INDEX ON COLUMN_EXPR
• ALTER TABLE MODIFY
• Specifying length units for VARCHAR and CHARACTER data types
• TYPE/NAME/VERSION/VALUE/INTERVAL alias
• Stored procedures
• DATE
• HASH-, RANGE-, and LIST-partitioned table creation
• MERGE

  MERGE [HINT] INTO table_name USING ({subquery | table_name | view_name}) alias ON (condition) merge_update_clause merge_insert_clause;

• Time interval operation:

  INTERVAL YEAR TO MONTH,INTERVAL DAY (l) TO SECOND (P);

• CREATE TRIGGER with BODY:

  CREATE TRIGGER name... {DECLARE ... BEGIN | BEGIN} body END;

• Stored procedure cursor syntax:

  CURSOR cursor_name [ parameter_list ] IS select_statement, TYPE type_name IS REF CURSOR;

• Stored procedure cursor variables:

  SQL%ISOPEN,SQL%FOUND,SQL%NOTFOUND,SQL%ROWCOUNT,cursor%ISOPEN,cursor%FOUND,cursor%NOTFOUND,cursor%ROWCOUNT;

• Scheduled task advanced package:

  DBMS_JOB.SUBMIT,DBMS_JOB.ISUBMIT,DBMS_JOB.REMOVE,DBMS_JOB.BROKEN,DBMS_JOB.CHANGE,DBMS_JOB.WHAT,DBMS_JOB.NEXT_DATE,DBMS_JOB.INTERVAL;

• CREATE USER:

  {DEFAULT COLLATION | DEFAULT TABLESPACE | [LOCAL] TEMPORARY TABLESPACE} Clause;

• Session attribute modification:

  ALTER SESSION SET param_name = value;

• Anonymous blocks
• Cross-mode access to stored procedures
• SQLCODE built-in variables in stored procedures
• Enhanced syntax compatibility in stored procedures: stored procedure names can be used as end tags; FOR VAR IN SELECT-CLAUSE is supported; end tags can be specified for LOOP statements; default value of IN can be specified.
• Subqueries with no alias specified
• NOCYCLE in CREATE SEQUENCE
• Replacing PASSWORD with IDENTIFIED BY in CREATE/ALTER USER
• Specifying table names or alias in UPDATE SET
• (columnname)=(value) in UPDATE SET
• ALTER TABLE support for MODIFY NOT NULL and ENABLE
• Null character string equivalent to NULL
• sequencCURRVAL and sequencNEXTVAL
• Creating users and schemas with same names at the same time
• Deleting FROM from the table record syntax
• XML data type pseudo column COLUMN_VALUE
• OUTER JOIN (+)
• Operators between the data types INTERVAL and number: +, -, >, <, >=, <=, and <>
• Partition table DML operations: SELECT, INSERT, UPDATE, and DELETE
• Composite partitioning of partition tables
• Expressions used as partition boundaries
• Trigger DDL: schema
• Time format: IYY
• CREATE/ALTER MATERIALIZED VIEW
• CREATE TYPE
• CREATE PROFILE
• Enable/disable syntax for column constraints
• Tablespace options specified by partitioned tables
• DROP TABLE tablename [CASCADE CONSTRAINTS] [PURGE]
• Stored procedure dynamic SQL syntax EXECUTE IMMEDIATE. The current edition does not support dynamic execution of anonymous blocks with DECLARE.
• FUNCTION definition
• CONNECT BY queries: LEVEL, CONNECT_BY_ROOT, and CONNECT_BY_ISLEAF pseudo columns; sys_connect_by_path, CONNECT_BY_ROOT, and ORDER SIBLINGS
• TIME data type precision
• Supported for virtual columns: column_name datatype [GENERATED ALWAYS] AS (expression) [VIRTUAL]
• One-dimensional array definition: CREATE OR REPLACE TYPE array_name AS VARRAY (len) OF typename
• One-dimensional array: array_name.extend, array_name.count, array_name.first, array_name.last
• ROLLUP, CUBE, and GROUPING SETS Group By supported for grouping_id([expr1[, expr2[, ...exprn]]]) and group_id()
• Sorting query statements returned by non-grouping fields: SELECT SUM(colname) FROM tbl ORDER BY colname