Postgres support (special functions syntax)

[nene]

Other functions

• COLLATION FOR(expr)
• CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME, LOCALTIMESTAMP
• CURRENT_TIME(nr), CURRENT_TIMESTAMP(nr), LOCALTIME(nr), LOCALTIMESTAMP(nr)
• CURRENT_ROLE, CURRENT_USER, SYSTEM_USER, USER,
• CURRENT_CATALOG, CURRENT_SCHEMA
• EXTRACT(...)
  • EXTRACT({YEAR | MONTH | DAY | HOUR | MINUTE | SECOND} FROM expr)
  • EXTRACT(ident FROM expr)
  • EXTRACT('string' FROM expr)
• NORMALIZE(expr)
• NORMALIZE(expr, NFC | NFD | NFKC | NFKD)
• TREAT(expr AS type)
• NULLIF(expr, expr)
• COALESCE(expr_list)
• GREATEST(expr_list)
• LEAST(expr_list)

String functions

• OVERLAY(expr PLACING expr FROM expr [FOR expr])
• OVERLAY(arg1, arg2, ...)
• POSITION(expr IN expr)
• SUBSTRING(expr FROM expr | FOR expr)
• SUBSTRING(expr FOR expr FROM expr)
• SUBSTRING(expr FOR expr)
• SUBSTRING(expr FROM expr)
• SUBSTRING(expr SIMILAR expr ESCAPE expr)
• SUBSTRING(arg1, arg2, ...)
• TRIM([BOTH | LEADING | TRAILING] {expr FROM expr_list | FROM expr_list | expr_list})

JSON functions

• JSON_OBJECT(...) Support JSON_OBJECT() in PostgreSQL #84
  • json_object('code' VALUE 'P123')
  • json_object('title': 'Jaws')
  • [ FORMAT JSON [ ENCODING UTF8 ] ]
  • [ { NULL | ABSENT } ON NULL ]
  • [ { WITH | WITHOUT } UNIQUE [ KEYS ] ]
  • [ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ]
• json( expression [ FORMAT JSON [ ENCODING UTF8 ]] [ { WITH | WITHOUT } UNIQUE [ KEYS ]] )
• json_serialize ( expression [ FORMAT JSON [ ENCODING UTF8 ] ] [ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ] )
• json_array ( [ { value_expression [ FORMAT JSON ] } [, ...] ] [ { NULL | ABSENT } ON NULL ] [ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ])
• json_array ( [ query_expression ] [ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ])
• expression IS [ NOT ] JSON [ { VALUE | SCALAR | ARRAY | OBJECT } ] [ { WITH | WITHOUT } UNIQUE [ KEYS ] ]
• JSON_EXISTS (...)
  • context_item, path_expression
  • [ PASSING { value AS varname } [, ...]]
  • [{ TRUE | FALSE | UNKNOWN | ERROR } ON ERROR ]
• JSON_QUERY (...)
  • context_item, path_expression
  • [ PASSING { value AS varname } [, ...]]
  • [ RETURNING data_type [ FORMAT JSON [ ENCODING UTF8 ] ] ]
  • [ { WITHOUT | WITH { CONDITIONAL | [UNCONDITIONAL] } } [ ARRAY ] WRAPPER ]
  • [ { KEEP | OMIT } QUOTES [ ON SCALAR STRING ] ]
  • [ { ERROR | NULL | EMPTY { [ ARRAY ] | OBJECT } | DEFAULT expression } ON EMPTY ]
  • [ { ERROR | NULL | EMPTY { [ ARRAY ] | OBJECT } | DEFAULT expression } ON ERROR ]
• JSON_VALUE (...)
  • context_item, path_expression
  • [ PASSING { value AS varname } [, ...]]
  • [ RETURNING data_type ]
  • [ { ERROR | NULL | DEFAULT expression } ON EMPTY ]
  • [ { ERROR | NULL | DEFAULT expression } ON ERROR ]
• JSON_TABLE (...)
  • context_item, path_expression [ AS json_path_name ] [ PASSING { value AS varname } [, ...] ]
  • COLUMNS ( json_table_column [, ...] ), where json_table_column is:
    • name FOR ORDINALITY
    • name type:
      • FORMAT JSON [ENCODING UTF8]
      • PATH path_expression
      • { WITHOUT | WITH { CONDITIONAL | [UNCONDITIONAL] } } [ ARRAY ] WRAPPER
      • { KEEP | OMIT } QUOTES [ ON SCALAR STRING ]
      • { ERROR | NULL | EMPTY { [ARRAY] | OBJECT } | DEFAULT expression } ON EMPTY
      • { ERROR | NULL | EMPTY { [ARRAY] | OBJECT } | DEFAULT expression } ON ERROR
    • name type EXISTS [ PATH path_expression ] [ { ERROR | TRUE | FALSE | UNKNOWN } ON ERROR ]
    • NESTED [ PATH ] path_expression [ AS json_path_name ] COLUMNS ( json_table_column [, ...] )
  • [ { ERROR | EMPTY [ARRAY]} ON ERROR ]

XML functions

• XMLCONCAT(expr_list)
• XMLELEMENT(NAME col_name [, expr_list])
• XMLELEMENT(NAME col_name, XMLATTRIBUTES(expr AS col_name [...]) [, expr_list])
• XMLEXISTS( text PASSING [BY {REF|VALUE}] xml [BY {REF|VALUE}] )
• XMLFOREST( content [ AS name ] [, ...] )
• `XMLPARSE({DOCUMENT | CONTENT} expr [{PRESERVE | STRIP} WHITESPACE])
• XMLPI( NAME name [, expr ] )
• XMLROOT( expr, VERSION {text|NO VALUE} [, STANDALONE {YES|NO|NO VALUE} ] )
• XMLSERIALIZE({DOCUMENT | CONTENT} expr AS type [INDENT | NO INDENT])
• xml IS DOCUMENT
• xml IS NOT DOCUMENT
• XMLTABLE (...)
  • [ XMLNAMESPACES ( namespace_uri AS namespace_name [, ...] ), ]
  • row_expression PASSING [BY {REF|VALUE}] document_expression [BY {REF|VALUE}]
  • COLUMNS name { type [PATH column_expression] [DEFAULT default_expression] [NOT NULL | NULL] | FOR ORDINALITY }