pgsql.js 29 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506
  1. /*
  2. Language: PostgreSQL and PL/pgSQL
  3. Author: Egor Rogov (e.rogov@postgrespro.ru)
  4. Website: https://www.postgresql.org/docs/11/sql.html
  5. Description:
  6. This language incorporates both PostgreSQL SQL dialect and PL/pgSQL language.
  7. It is based on PostgreSQL version 11. Some notes:
  8. - Text in double-dollar-strings is _always_ interpreted as some programming code. Text
  9. in ordinary quotes is _never_ interpreted that way and highlighted just as a string.
  10. - There are quite a bit "special cases". That's because many keywords are not strictly
  11. they are keywords in some contexts and ordinary identifiers in others. Only some
  12. of such cases are handled; you still can get some of your identifiers highlighted
  13. wrong way.
  14. - Function names deliberately are not highlighted. There is no way to tell function
  15. call from other constructs, hence we can't highlight _all_ function names. And
  16. some names highlighted while others not looks ugly.
  17. */
  18. function pgsql(hljs) {
  19. var COMMENT_MODE = hljs.COMMENT('--', '$');
  20. var UNQUOTED_IDENT = '[a-zA-Z_][a-zA-Z_0-9$]*';
  21. var DOLLAR_STRING = '\\$([a-zA-Z_]?|[a-zA-Z_][a-zA-Z_0-9]*)\\$';
  22. var LABEL = '<<\\s*' + UNQUOTED_IDENT + '\\s*>>';
  23. var SQL_KW =
  24. // https://www.postgresql.org/docs/11/static/sql-keywords-appendix.html
  25. // https://www.postgresql.org/docs/11/static/sql-commands.html
  26. // SQL commands (starting words)
  27. 'ABORT ALTER ANALYZE BEGIN CALL CHECKPOINT|10 CLOSE CLUSTER COMMENT COMMIT COPY CREATE DEALLOCATE DECLARE ' +
  28. 'DELETE DISCARD DO DROP END EXECUTE EXPLAIN FETCH GRANT IMPORT INSERT LISTEN LOAD LOCK MOVE NOTIFY ' +
  29. 'PREPARE REASSIGN|10 REFRESH REINDEX RELEASE RESET REVOKE ROLLBACK SAVEPOINT SECURITY SELECT SET SHOW ' +
  30. 'START TRUNCATE UNLISTEN|10 UPDATE VACUUM|10 VALUES ' +
  31. // SQL commands (others)
  32. 'AGGREGATE COLLATION CONVERSION|10 DATABASE DEFAULT PRIVILEGES DOMAIN TRIGGER EXTENSION FOREIGN ' +
  33. 'WRAPPER|10 TABLE FUNCTION GROUP LANGUAGE LARGE OBJECT MATERIALIZED VIEW OPERATOR CLASS ' +
  34. 'FAMILY POLICY PUBLICATION|10 ROLE RULE SCHEMA SEQUENCE SERVER STATISTICS SUBSCRIPTION SYSTEM ' +
  35. 'TABLESPACE CONFIGURATION DICTIONARY PARSER TEMPLATE TYPE USER MAPPING PREPARED ACCESS ' +
  36. 'METHOD CAST AS TRANSFORM TRANSACTION OWNED TO INTO SESSION AUTHORIZATION ' +
  37. 'INDEX PROCEDURE ASSERTION ' +
  38. // additional reserved key words
  39. 'ALL ANALYSE AND ANY ARRAY ASC ASYMMETRIC|10 BOTH CASE CHECK ' +
  40. 'COLLATE COLUMN CONCURRENTLY|10 CONSTRAINT CROSS ' +
  41. 'DEFERRABLE RANGE ' +
  42. 'DESC DISTINCT ELSE EXCEPT FOR FREEZE|10 FROM FULL HAVING ' +
  43. 'ILIKE IN INITIALLY INNER INTERSECT IS ISNULL JOIN LATERAL LEADING LIKE LIMIT ' +
  44. 'NATURAL NOT NOTNULL NULL OFFSET ON ONLY OR ORDER OUTER OVERLAPS PLACING PRIMARY ' +
  45. 'REFERENCES RETURNING SIMILAR SOME SYMMETRIC TABLESAMPLE THEN ' +
  46. 'TRAILING UNION UNIQUE USING VARIADIC|10 VERBOSE WHEN WHERE WINDOW WITH ' +
  47. // some of non-reserved (which are used in clauses or as PL/pgSQL keyword)
  48. 'BY RETURNS INOUT OUT SETOF|10 IF STRICT CURRENT CONTINUE OWNER LOCATION OVER PARTITION WITHIN ' +
  49. 'BETWEEN ESCAPE EXTERNAL INVOKER DEFINER WORK RENAME VERSION CONNECTION CONNECT ' +
  50. 'TABLES TEMP TEMPORARY FUNCTIONS SEQUENCES TYPES SCHEMAS OPTION CASCADE RESTRICT ADD ADMIN ' +
  51. 'EXISTS VALID VALIDATE ENABLE DISABLE REPLICA|10 ALWAYS PASSING COLUMNS PATH ' +
  52. 'REF VALUE OVERRIDING IMMUTABLE STABLE VOLATILE BEFORE AFTER EACH ROW PROCEDURAL ' +
  53. 'ROUTINE NO HANDLER VALIDATOR OPTIONS STORAGE OIDS|10 WITHOUT INHERIT DEPENDS CALLED ' +
  54. 'INPUT LEAKPROOF|10 COST ROWS NOWAIT SEARCH UNTIL ENCRYPTED|10 PASSWORD CONFLICT|10 ' +
  55. 'INSTEAD INHERITS CHARACTERISTICS WRITE CURSOR ALSO STATEMENT SHARE EXCLUSIVE INLINE ' +
  56. 'ISOLATION REPEATABLE READ COMMITTED SERIALIZABLE UNCOMMITTED LOCAL GLOBAL SQL PROCEDURES ' +
  57. 'RECURSIVE SNAPSHOT ROLLUP CUBE TRUSTED|10 INCLUDE FOLLOWING PRECEDING UNBOUNDED RANGE GROUPS ' +
  58. 'UNENCRYPTED|10 SYSID FORMAT DELIMITER HEADER QUOTE ENCODING FILTER OFF ' +
  59. // some parameters of VACUUM/ANALYZE/EXPLAIN
  60. 'FORCE_QUOTE FORCE_NOT_NULL FORCE_NULL COSTS BUFFERS TIMING SUMMARY DISABLE_PAGE_SKIPPING ' +
  61. //
  62. 'RESTART CYCLE GENERATED IDENTITY DEFERRED IMMEDIATE LEVEL LOGGED UNLOGGED ' +
  63. 'OF NOTHING NONE EXCLUDE ATTRIBUTE ' +
  64. // from GRANT (not keywords actually)
  65. 'USAGE ROUTINES ' +
  66. // actually literals, but look better this way (due to IS TRUE, IS FALSE, ISNULL etc)
  67. 'TRUE FALSE NAN INFINITY ';
  68. var ROLE_ATTRS = // only those not in keywrods already
  69. 'SUPERUSER NOSUPERUSER CREATEDB NOCREATEDB CREATEROLE NOCREATEROLE INHERIT NOINHERIT ' +
  70. 'LOGIN NOLOGIN REPLICATION NOREPLICATION BYPASSRLS NOBYPASSRLS ';
  71. var PLPGSQL_KW =
  72. 'ALIAS BEGIN CONSTANT DECLARE END EXCEPTION RETURN PERFORM|10 RAISE GET DIAGNOSTICS ' +
  73. 'STACKED|10 FOREACH LOOP ELSIF EXIT WHILE REVERSE SLICE DEBUG LOG INFO NOTICE WARNING ASSERT ' +
  74. 'OPEN ';
  75. var TYPES =
  76. // https://www.postgresql.org/docs/11/static/datatype.html
  77. 'BIGINT INT8 BIGSERIAL SERIAL8 BIT VARYING VARBIT BOOLEAN BOOL BOX BYTEA CHARACTER CHAR VARCHAR ' +
  78. 'CIDR CIRCLE DATE DOUBLE PRECISION FLOAT8 FLOAT INET INTEGER INT INT4 INTERVAL JSON JSONB LINE LSEG|10 ' +
  79. 'MACADDR MACADDR8 MONEY NUMERIC DEC DECIMAL PATH POINT POLYGON REAL FLOAT4 SMALLINT INT2 ' +
  80. 'SMALLSERIAL|10 SERIAL2|10 SERIAL|10 SERIAL4|10 TEXT TIME ZONE TIMETZ|10 TIMESTAMP TIMESTAMPTZ|10 TSQUERY|10 TSVECTOR|10 ' +
  81. 'TXID_SNAPSHOT|10 UUID XML NATIONAL NCHAR ' +
  82. 'INT4RANGE|10 INT8RANGE|10 NUMRANGE|10 TSRANGE|10 TSTZRANGE|10 DATERANGE|10 ' +
  83. // pseudotypes
  84. 'ANYELEMENT ANYARRAY ANYNONARRAY ANYENUM ANYRANGE CSTRING INTERNAL ' +
  85. 'RECORD PG_DDL_COMMAND VOID UNKNOWN OPAQUE REFCURSOR ' +
  86. // spec. type
  87. 'NAME ' +
  88. // OID-types
  89. 'OID REGPROC|10 REGPROCEDURE|10 REGOPER|10 REGOPERATOR|10 REGCLASS|10 REGTYPE|10 REGROLE|10 ' +
  90. 'REGNAMESPACE|10 REGCONFIG|10 REGDICTIONARY|10 ';// +
  91. var TYPES_RE =
  92. TYPES.trim()
  93. .split(' ')
  94. .map( function(val) { return val.split('|')[0]; } )
  95. .join('|');
  96. var SQL_BI =
  97. 'CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURRENT_CATALOG|10 CURRENT_DATE LOCALTIME LOCALTIMESTAMP ' +
  98. 'CURRENT_ROLE|10 CURRENT_SCHEMA|10 SESSION_USER PUBLIC ';
  99. var PLPGSQL_BI =
  100. 'FOUND NEW OLD TG_NAME|10 TG_WHEN|10 TG_LEVEL|10 TG_OP|10 TG_RELID|10 TG_RELNAME|10 ' +
  101. 'TG_TABLE_NAME|10 TG_TABLE_SCHEMA|10 TG_NARGS|10 TG_ARGV|10 TG_EVENT|10 TG_TAG|10 ' +
  102. // get diagnostics
  103. 'ROW_COUNT RESULT_OID|10 PG_CONTEXT|10 RETURNED_SQLSTATE COLUMN_NAME CONSTRAINT_NAME ' +
  104. 'PG_DATATYPE_NAME|10 MESSAGE_TEXT TABLE_NAME SCHEMA_NAME PG_EXCEPTION_DETAIL|10 ' +
  105. 'PG_EXCEPTION_HINT|10 PG_EXCEPTION_CONTEXT|10 ';
  106. var PLPGSQL_EXCEPTIONS =
  107. // exceptions https://www.postgresql.org/docs/current/static/errcodes-appendix.html
  108. 'SQLSTATE SQLERRM|10 ' +
  109. 'SUCCESSFUL_COMPLETION WARNING DYNAMIC_RESULT_SETS_RETURNED IMPLICIT_ZERO_BIT_PADDING ' +
  110. 'NULL_VALUE_ELIMINATED_IN_SET_FUNCTION PRIVILEGE_NOT_GRANTED PRIVILEGE_NOT_REVOKED ' +
  111. 'STRING_DATA_RIGHT_TRUNCATION DEPRECATED_FEATURE NO_DATA NO_ADDITIONAL_DYNAMIC_RESULT_SETS_RETURNED ' +
  112. 'SQL_STATEMENT_NOT_YET_COMPLETE CONNECTION_EXCEPTION CONNECTION_DOES_NOT_EXIST CONNECTION_FAILURE ' +
  113. 'SQLCLIENT_UNABLE_TO_ESTABLISH_SQLCONNECTION SQLSERVER_REJECTED_ESTABLISHMENT_OF_SQLCONNECTION ' +
  114. 'TRANSACTION_RESOLUTION_UNKNOWN PROTOCOL_VIOLATION TRIGGERED_ACTION_EXCEPTION FEATURE_NOT_SUPPORTED ' +
  115. 'INVALID_TRANSACTION_INITIATION LOCATOR_EXCEPTION INVALID_LOCATOR_SPECIFICATION INVALID_GRANTOR ' +
  116. 'INVALID_GRANT_OPERATION INVALID_ROLE_SPECIFICATION DIAGNOSTICS_EXCEPTION ' +
  117. 'STACKED_DIAGNOSTICS_ACCESSED_WITHOUT_ACTIVE_HANDLER CASE_NOT_FOUND CARDINALITY_VIOLATION ' +
  118. 'DATA_EXCEPTION ARRAY_SUBSCRIPT_ERROR CHARACTER_NOT_IN_REPERTOIRE DATETIME_FIELD_OVERFLOW ' +
  119. 'DIVISION_BY_ZERO ERROR_IN_ASSIGNMENT ESCAPE_CHARACTER_CONFLICT INDICATOR_OVERFLOW ' +
  120. 'INTERVAL_FIELD_OVERFLOW INVALID_ARGUMENT_FOR_LOGARITHM INVALID_ARGUMENT_FOR_NTILE_FUNCTION ' +
  121. 'INVALID_ARGUMENT_FOR_NTH_VALUE_FUNCTION INVALID_ARGUMENT_FOR_POWER_FUNCTION ' +
  122. 'INVALID_ARGUMENT_FOR_WIDTH_BUCKET_FUNCTION INVALID_CHARACTER_VALUE_FOR_CAST ' +
  123. 'INVALID_DATETIME_FORMAT INVALID_ESCAPE_CHARACTER INVALID_ESCAPE_OCTET INVALID_ESCAPE_SEQUENCE ' +
  124. 'NONSTANDARD_USE_OF_ESCAPE_CHARACTER INVALID_INDICATOR_PARAMETER_VALUE INVALID_PARAMETER_VALUE ' +
  125. 'INVALID_REGULAR_EXPRESSION INVALID_ROW_COUNT_IN_LIMIT_CLAUSE ' +
  126. 'INVALID_ROW_COUNT_IN_RESULT_OFFSET_CLAUSE INVALID_TABLESAMPLE_ARGUMENT INVALID_TABLESAMPLE_REPEAT ' +
  127. 'INVALID_TIME_ZONE_DISPLACEMENT_VALUE INVALID_USE_OF_ESCAPE_CHARACTER MOST_SPECIFIC_TYPE_MISMATCH ' +
  128. 'NULL_VALUE_NOT_ALLOWED NULL_VALUE_NO_INDICATOR_PARAMETER NUMERIC_VALUE_OUT_OF_RANGE ' +
  129. 'SEQUENCE_GENERATOR_LIMIT_EXCEEDED STRING_DATA_LENGTH_MISMATCH STRING_DATA_RIGHT_TRUNCATION ' +
  130. 'SUBSTRING_ERROR TRIM_ERROR UNTERMINATED_C_STRING ZERO_LENGTH_CHARACTER_STRING ' +
  131. 'FLOATING_POINT_EXCEPTION INVALID_TEXT_REPRESENTATION INVALID_BINARY_REPRESENTATION ' +
  132. 'BAD_COPY_FILE_FORMAT UNTRANSLATABLE_CHARACTER NOT_AN_XML_DOCUMENT INVALID_XML_DOCUMENT ' +
  133. 'INVALID_XML_CONTENT INVALID_XML_COMMENT INVALID_XML_PROCESSING_INSTRUCTION ' +
  134. 'INTEGRITY_CONSTRAINT_VIOLATION RESTRICT_VIOLATION NOT_NULL_VIOLATION FOREIGN_KEY_VIOLATION ' +
  135. 'UNIQUE_VIOLATION CHECK_VIOLATION EXCLUSION_VIOLATION INVALID_CURSOR_STATE ' +
  136. 'INVALID_TRANSACTION_STATE ACTIVE_SQL_TRANSACTION BRANCH_TRANSACTION_ALREADY_ACTIVE ' +
  137. 'HELD_CURSOR_REQUIRES_SAME_ISOLATION_LEVEL INAPPROPRIATE_ACCESS_MODE_FOR_BRANCH_TRANSACTION ' +
  138. 'INAPPROPRIATE_ISOLATION_LEVEL_FOR_BRANCH_TRANSACTION ' +
  139. 'NO_ACTIVE_SQL_TRANSACTION_FOR_BRANCH_TRANSACTION READ_ONLY_SQL_TRANSACTION ' +
  140. 'SCHEMA_AND_DATA_STATEMENT_MIXING_NOT_SUPPORTED NO_ACTIVE_SQL_TRANSACTION ' +
  141. 'IN_FAILED_SQL_TRANSACTION IDLE_IN_TRANSACTION_SESSION_TIMEOUT INVALID_SQL_STATEMENT_NAME ' +
  142. 'TRIGGERED_DATA_CHANGE_VIOLATION INVALID_AUTHORIZATION_SPECIFICATION INVALID_PASSWORD ' +
  143. 'DEPENDENT_PRIVILEGE_DESCRIPTORS_STILL_EXIST DEPENDENT_OBJECTS_STILL_EXIST ' +
  144. 'INVALID_TRANSACTION_TERMINATION SQL_ROUTINE_EXCEPTION FUNCTION_EXECUTED_NO_RETURN_STATEMENT ' +
  145. 'MODIFYING_SQL_DATA_NOT_PERMITTED PROHIBITED_SQL_STATEMENT_ATTEMPTED ' +
  146. 'READING_SQL_DATA_NOT_PERMITTED INVALID_CURSOR_NAME EXTERNAL_ROUTINE_EXCEPTION ' +
  147. 'CONTAINING_SQL_NOT_PERMITTED MODIFYING_SQL_DATA_NOT_PERMITTED ' +
  148. 'PROHIBITED_SQL_STATEMENT_ATTEMPTED READING_SQL_DATA_NOT_PERMITTED ' +
  149. 'EXTERNAL_ROUTINE_INVOCATION_EXCEPTION INVALID_SQLSTATE_RETURNED NULL_VALUE_NOT_ALLOWED ' +
  150. 'TRIGGER_PROTOCOL_VIOLATED SRF_PROTOCOL_VIOLATED EVENT_TRIGGER_PROTOCOL_VIOLATED ' +
  151. 'SAVEPOINT_EXCEPTION INVALID_SAVEPOINT_SPECIFICATION INVALID_CATALOG_NAME ' +
  152. 'INVALID_SCHEMA_NAME TRANSACTION_ROLLBACK TRANSACTION_INTEGRITY_CONSTRAINT_VIOLATION ' +
  153. 'SERIALIZATION_FAILURE STATEMENT_COMPLETION_UNKNOWN DEADLOCK_DETECTED ' +
  154. 'SYNTAX_ERROR_OR_ACCESS_RULE_VIOLATION SYNTAX_ERROR INSUFFICIENT_PRIVILEGE CANNOT_COERCE ' +
  155. 'GROUPING_ERROR WINDOWING_ERROR INVALID_RECURSION INVALID_FOREIGN_KEY INVALID_NAME ' +
  156. 'NAME_TOO_LONG RESERVED_NAME DATATYPE_MISMATCH INDETERMINATE_DATATYPE COLLATION_MISMATCH ' +
  157. 'INDETERMINATE_COLLATION WRONG_OBJECT_TYPE GENERATED_ALWAYS UNDEFINED_COLUMN ' +
  158. 'UNDEFINED_FUNCTION UNDEFINED_TABLE UNDEFINED_PARAMETER UNDEFINED_OBJECT ' +
  159. 'DUPLICATE_COLUMN DUPLICATE_CURSOR DUPLICATE_DATABASE DUPLICATE_FUNCTION ' +
  160. 'DUPLICATE_PREPARED_STATEMENT DUPLICATE_SCHEMA DUPLICATE_TABLE DUPLICATE_ALIAS ' +
  161. 'DUPLICATE_OBJECT AMBIGUOUS_COLUMN AMBIGUOUS_FUNCTION AMBIGUOUS_PARAMETER AMBIGUOUS_ALIAS ' +
  162. 'INVALID_COLUMN_REFERENCE INVALID_COLUMN_DEFINITION INVALID_CURSOR_DEFINITION ' +
  163. 'INVALID_DATABASE_DEFINITION INVALID_FUNCTION_DEFINITION ' +
  164. 'INVALID_PREPARED_STATEMENT_DEFINITION INVALID_SCHEMA_DEFINITION INVALID_TABLE_DEFINITION ' +
  165. 'INVALID_OBJECT_DEFINITION WITH_CHECK_OPTION_VIOLATION INSUFFICIENT_RESOURCES DISK_FULL ' +
  166. 'OUT_OF_MEMORY TOO_MANY_CONNECTIONS CONFIGURATION_LIMIT_EXCEEDED PROGRAM_LIMIT_EXCEEDED ' +
  167. 'STATEMENT_TOO_COMPLEX TOO_MANY_COLUMNS TOO_MANY_ARGUMENTS OBJECT_NOT_IN_PREREQUISITE_STATE ' +
  168. 'OBJECT_IN_USE CANT_CHANGE_RUNTIME_PARAM LOCK_NOT_AVAILABLE OPERATOR_INTERVENTION ' +
  169. 'QUERY_CANCELED ADMIN_SHUTDOWN CRASH_SHUTDOWN CANNOT_CONNECT_NOW DATABASE_DROPPED ' +
  170. 'SYSTEM_ERROR IO_ERROR UNDEFINED_FILE DUPLICATE_FILE SNAPSHOT_TOO_OLD CONFIG_FILE_ERROR ' +
  171. 'LOCK_FILE_EXISTS FDW_ERROR FDW_COLUMN_NAME_NOT_FOUND FDW_DYNAMIC_PARAMETER_VALUE_NEEDED ' +
  172. 'FDW_FUNCTION_SEQUENCE_ERROR FDW_INCONSISTENT_DESCRIPTOR_INFORMATION ' +
  173. 'FDW_INVALID_ATTRIBUTE_VALUE FDW_INVALID_COLUMN_NAME FDW_INVALID_COLUMN_NUMBER ' +
  174. 'FDW_INVALID_DATA_TYPE FDW_INVALID_DATA_TYPE_DESCRIPTORS ' +
  175. 'FDW_INVALID_DESCRIPTOR_FIELD_IDENTIFIER FDW_INVALID_HANDLE FDW_INVALID_OPTION_INDEX ' +
  176. 'FDW_INVALID_OPTION_NAME FDW_INVALID_STRING_LENGTH_OR_BUFFER_LENGTH ' +
  177. 'FDW_INVALID_STRING_FORMAT FDW_INVALID_USE_OF_NULL_POINTER FDW_TOO_MANY_HANDLES ' +
  178. 'FDW_OUT_OF_MEMORY FDW_NO_SCHEMAS FDW_OPTION_NAME_NOT_FOUND FDW_REPLY_HANDLE ' +
  179. 'FDW_SCHEMA_NOT_FOUND FDW_TABLE_NOT_FOUND FDW_UNABLE_TO_CREATE_EXECUTION ' +
  180. 'FDW_UNABLE_TO_CREATE_REPLY FDW_UNABLE_TO_ESTABLISH_CONNECTION PLPGSQL_ERROR ' +
  181. 'RAISE_EXCEPTION NO_DATA_FOUND TOO_MANY_ROWS ASSERT_FAILURE INTERNAL_ERROR DATA_CORRUPTED ' +
  182. 'INDEX_CORRUPTED ';
  183. var FUNCTIONS =
  184. // https://www.postgresql.org/docs/11/static/functions-aggregate.html
  185. 'ARRAY_AGG AVG BIT_AND BIT_OR BOOL_AND BOOL_OR COUNT EVERY JSON_AGG JSONB_AGG JSON_OBJECT_AGG ' +
  186. 'JSONB_OBJECT_AGG MAX MIN MODE STRING_AGG SUM XMLAGG ' +
  187. 'CORR COVAR_POP COVAR_SAMP REGR_AVGX REGR_AVGY REGR_COUNT REGR_INTERCEPT REGR_R2 REGR_SLOPE ' +
  188. 'REGR_SXX REGR_SXY REGR_SYY STDDEV STDDEV_POP STDDEV_SAMP VARIANCE VAR_POP VAR_SAMP ' +
  189. 'PERCENTILE_CONT PERCENTILE_DISC ' +
  190. // https://www.postgresql.org/docs/11/static/functions-window.html
  191. 'ROW_NUMBER RANK DENSE_RANK PERCENT_RANK CUME_DIST NTILE LAG LEAD FIRST_VALUE LAST_VALUE NTH_VALUE ' +
  192. // https://www.postgresql.org/docs/11/static/functions-comparison.html
  193. 'NUM_NONNULLS NUM_NULLS ' +
  194. // https://www.postgresql.org/docs/11/static/functions-math.html
  195. 'ABS CBRT CEIL CEILING DEGREES DIV EXP FLOOR LN LOG MOD PI POWER RADIANS ROUND SCALE SIGN SQRT ' +
  196. 'TRUNC WIDTH_BUCKET ' +
  197. 'RANDOM SETSEED ' +
  198. 'ACOS ACOSD ASIN ASIND ATAN ATAND ATAN2 ATAN2D COS COSD COT COTD SIN SIND TAN TAND ' +
  199. // https://www.postgresql.org/docs/11/static/functions-string.html
  200. 'BIT_LENGTH CHAR_LENGTH CHARACTER_LENGTH LOWER OCTET_LENGTH OVERLAY POSITION SUBSTRING TREAT TRIM UPPER ' +
  201. 'ASCII BTRIM CHR CONCAT CONCAT_WS CONVERT CONVERT_FROM CONVERT_TO DECODE ENCODE INITCAP ' +
  202. 'LEFT LENGTH LPAD LTRIM MD5 PARSE_IDENT PG_CLIENT_ENCODING QUOTE_IDENT|10 QUOTE_LITERAL|10 ' +
  203. 'QUOTE_NULLABLE|10 REGEXP_MATCH REGEXP_MATCHES REGEXP_REPLACE REGEXP_SPLIT_TO_ARRAY ' +
  204. 'REGEXP_SPLIT_TO_TABLE REPEAT REPLACE REVERSE RIGHT RPAD RTRIM SPLIT_PART STRPOS SUBSTR ' +
  205. 'TO_ASCII TO_HEX TRANSLATE ' +
  206. // https://www.postgresql.org/docs/11/static/functions-binarystring.html
  207. 'OCTET_LENGTH GET_BIT GET_BYTE SET_BIT SET_BYTE ' +
  208. // https://www.postgresql.org/docs/11/static/functions-formatting.html
  209. 'TO_CHAR TO_DATE TO_NUMBER TO_TIMESTAMP ' +
  210. // https://www.postgresql.org/docs/11/static/functions-datetime.html
  211. 'AGE CLOCK_TIMESTAMP|10 DATE_PART DATE_TRUNC ISFINITE JUSTIFY_DAYS JUSTIFY_HOURS JUSTIFY_INTERVAL ' +
  212. 'MAKE_DATE MAKE_INTERVAL|10 MAKE_TIME MAKE_TIMESTAMP|10 MAKE_TIMESTAMPTZ|10 NOW STATEMENT_TIMESTAMP|10 ' +
  213. 'TIMEOFDAY TRANSACTION_TIMESTAMP|10 ' +
  214. // https://www.postgresql.org/docs/11/static/functions-enum.html
  215. 'ENUM_FIRST ENUM_LAST ENUM_RANGE ' +
  216. // https://www.postgresql.org/docs/11/static/functions-geometry.html
  217. 'AREA CENTER DIAMETER HEIGHT ISCLOSED ISOPEN NPOINTS PCLOSE POPEN RADIUS WIDTH ' +
  218. 'BOX BOUND_BOX CIRCLE LINE LSEG PATH POLYGON ' +
  219. // https://www.postgresql.org/docs/11/static/functions-net.html
  220. 'ABBREV BROADCAST HOST HOSTMASK MASKLEN NETMASK NETWORK SET_MASKLEN TEXT INET_SAME_FAMILY ' +
  221. 'INET_MERGE MACADDR8_SET7BIT ' +
  222. // https://www.postgresql.org/docs/11/static/functions-textsearch.html
  223. 'ARRAY_TO_TSVECTOR GET_CURRENT_TS_CONFIG NUMNODE PLAINTO_TSQUERY PHRASETO_TSQUERY WEBSEARCH_TO_TSQUERY ' +
  224. 'QUERYTREE SETWEIGHT STRIP TO_TSQUERY TO_TSVECTOR JSON_TO_TSVECTOR JSONB_TO_TSVECTOR TS_DELETE ' +
  225. 'TS_FILTER TS_HEADLINE TS_RANK TS_RANK_CD TS_REWRITE TSQUERY_PHRASE TSVECTOR_TO_ARRAY ' +
  226. 'TSVECTOR_UPDATE_TRIGGER TSVECTOR_UPDATE_TRIGGER_COLUMN ' +
  227. // https://www.postgresql.org/docs/11/static/functions-xml.html
  228. 'XMLCOMMENT XMLCONCAT XMLELEMENT XMLFOREST XMLPI XMLROOT ' +
  229. 'XMLEXISTS XML_IS_WELL_FORMED XML_IS_WELL_FORMED_DOCUMENT XML_IS_WELL_FORMED_CONTENT ' +
  230. 'XPATH XPATH_EXISTS XMLTABLE XMLNAMESPACES ' +
  231. 'TABLE_TO_XML TABLE_TO_XMLSCHEMA TABLE_TO_XML_AND_XMLSCHEMA ' +
  232. 'QUERY_TO_XML QUERY_TO_XMLSCHEMA QUERY_TO_XML_AND_XMLSCHEMA ' +
  233. 'CURSOR_TO_XML CURSOR_TO_XMLSCHEMA ' +
  234. 'SCHEMA_TO_XML SCHEMA_TO_XMLSCHEMA SCHEMA_TO_XML_AND_XMLSCHEMA ' +
  235. 'DATABASE_TO_XML DATABASE_TO_XMLSCHEMA DATABASE_TO_XML_AND_XMLSCHEMA ' +
  236. 'XMLATTRIBUTES ' +
  237. // https://www.postgresql.org/docs/11/static/functions-json.html
  238. 'TO_JSON TO_JSONB ARRAY_TO_JSON ROW_TO_JSON JSON_BUILD_ARRAY JSONB_BUILD_ARRAY JSON_BUILD_OBJECT ' +
  239. 'JSONB_BUILD_OBJECT JSON_OBJECT JSONB_OBJECT JSON_ARRAY_LENGTH JSONB_ARRAY_LENGTH JSON_EACH ' +
  240. 'JSONB_EACH JSON_EACH_TEXT JSONB_EACH_TEXT JSON_EXTRACT_PATH JSONB_EXTRACT_PATH ' +
  241. 'JSON_OBJECT_KEYS JSONB_OBJECT_KEYS JSON_POPULATE_RECORD JSONB_POPULATE_RECORD JSON_POPULATE_RECORDSET ' +
  242. 'JSONB_POPULATE_RECORDSET JSON_ARRAY_ELEMENTS JSONB_ARRAY_ELEMENTS JSON_ARRAY_ELEMENTS_TEXT ' +
  243. 'JSONB_ARRAY_ELEMENTS_TEXT JSON_TYPEOF JSONB_TYPEOF JSON_TO_RECORD JSONB_TO_RECORD JSON_TO_RECORDSET ' +
  244. 'JSONB_TO_RECORDSET JSON_STRIP_NULLS JSONB_STRIP_NULLS JSONB_SET JSONB_INSERT JSONB_PRETTY ' +
  245. // https://www.postgresql.org/docs/11/static/functions-sequence.html
  246. 'CURRVAL LASTVAL NEXTVAL SETVAL ' +
  247. // https://www.postgresql.org/docs/11/static/functions-conditional.html
  248. 'COALESCE NULLIF GREATEST LEAST ' +
  249. // https://www.postgresql.org/docs/11/static/functions-array.html
  250. 'ARRAY_APPEND ARRAY_CAT ARRAY_NDIMS ARRAY_DIMS ARRAY_FILL ARRAY_LENGTH ARRAY_LOWER ARRAY_POSITION ' +
  251. 'ARRAY_POSITIONS ARRAY_PREPEND ARRAY_REMOVE ARRAY_REPLACE ARRAY_TO_STRING ARRAY_UPPER CARDINALITY ' +
  252. 'STRING_TO_ARRAY UNNEST ' +
  253. // https://www.postgresql.org/docs/11/static/functions-range.html
  254. 'ISEMPTY LOWER_INC UPPER_INC LOWER_INF UPPER_INF RANGE_MERGE ' +
  255. // https://www.postgresql.org/docs/11/static/functions-srf.html
  256. 'GENERATE_SERIES GENERATE_SUBSCRIPTS ' +
  257. // https://www.postgresql.org/docs/11/static/functions-info.html
  258. 'CURRENT_DATABASE CURRENT_QUERY CURRENT_SCHEMA|10 CURRENT_SCHEMAS|10 INET_CLIENT_ADDR INET_CLIENT_PORT ' +
  259. 'INET_SERVER_ADDR INET_SERVER_PORT ROW_SECURITY_ACTIVE FORMAT_TYPE ' +
  260. 'TO_REGCLASS TO_REGPROC TO_REGPROCEDURE TO_REGOPER TO_REGOPERATOR TO_REGTYPE TO_REGNAMESPACE TO_REGROLE ' +
  261. 'COL_DESCRIPTION OBJ_DESCRIPTION SHOBJ_DESCRIPTION ' +
  262. 'TXID_CURRENT TXID_CURRENT_IF_ASSIGNED TXID_CURRENT_SNAPSHOT TXID_SNAPSHOT_XIP TXID_SNAPSHOT_XMAX ' +
  263. 'TXID_SNAPSHOT_XMIN TXID_VISIBLE_IN_SNAPSHOT TXID_STATUS ' +
  264. // https://www.postgresql.org/docs/11/static/functions-admin.html
  265. 'CURRENT_SETTING SET_CONFIG BRIN_SUMMARIZE_NEW_VALUES BRIN_SUMMARIZE_RANGE BRIN_DESUMMARIZE_RANGE ' +
  266. 'GIN_CLEAN_PENDING_LIST ' +
  267. // https://www.postgresql.org/docs/11/static/functions-trigger.html
  268. 'SUPPRESS_REDUNDANT_UPDATES_TRIGGER ' +
  269. // ihttps://www.postgresql.org/docs/devel/static/lo-funcs.html
  270. 'LO_FROM_BYTEA LO_PUT LO_GET LO_CREAT LO_CREATE LO_UNLINK LO_IMPORT LO_EXPORT LOREAD LOWRITE ' +
  271. //
  272. 'GROUPING CAST ';
  273. var FUNCTIONS_RE =
  274. FUNCTIONS.trim()
  275. .split(' ')
  276. .map( function(val) { return val.split('|')[0]; } )
  277. .join('|');
  278. return {
  279. name: 'PostgreSQL',
  280. aliases: ['postgres','postgresql'],
  281. case_insensitive: true,
  282. keywords: {
  283. keyword:
  284. SQL_KW + PLPGSQL_KW + ROLE_ATTRS,
  285. built_in:
  286. SQL_BI + PLPGSQL_BI + PLPGSQL_EXCEPTIONS,
  287. },
  288. // Forbid some cunstructs from other languages to improve autodetect. In fact
  289. // "[a-z]:" is legal (as part of array slice), but improbabal.
  290. illegal: /:==|\W\s*\(\*|(^|\s)\$[a-z]|{{|[a-z]:\s*$|\.\.\.|TO:|DO:/,
  291. contains: [
  292. // special handling of some words, which are reserved only in some contexts
  293. {
  294. className: 'keyword',
  295. variants: [
  296. { begin: /\bTEXT\s*SEARCH\b/ },
  297. { begin: /\b(PRIMARY|FOREIGN|FOR(\s+NO)?)\s+KEY\b/ },
  298. { begin: /\bPARALLEL\s+(UNSAFE|RESTRICTED|SAFE)\b/ },
  299. { begin: /\bSTORAGE\s+(PLAIN|EXTERNAL|EXTENDED|MAIN)\b/ },
  300. { begin: /\bMATCH\s+(FULL|PARTIAL|SIMPLE)\b/ },
  301. { begin: /\bNULLS\s+(FIRST|LAST)\b/ },
  302. { begin: /\bEVENT\s+TRIGGER\b/ },
  303. { begin: /\b(MAPPING|OR)\s+REPLACE\b/ },
  304. { begin: /\b(FROM|TO)\s+(PROGRAM|STDIN|STDOUT)\b/ },
  305. { begin: /\b(SHARE|EXCLUSIVE)\s+MODE\b/ },
  306. { begin: /\b(LEFT|RIGHT)\s+(OUTER\s+)?JOIN\b/ },
  307. { begin: /\b(FETCH|MOVE)\s+(NEXT|PRIOR|FIRST|LAST|ABSOLUTE|RELATIVE|FORWARD|BACKWARD)\b/ },
  308. { begin: /\bPRESERVE\s+ROWS\b/ },
  309. { begin: /\bDISCARD\s+PLANS\b/ },
  310. { begin: /\bREFERENCING\s+(OLD|NEW)\b/ },
  311. { begin: /\bSKIP\s+LOCKED\b/ },
  312. { begin: /\bGROUPING\s+SETS\b/ },
  313. { begin: /\b(BINARY|INSENSITIVE|SCROLL|NO\s+SCROLL)\s+(CURSOR|FOR)\b/ },
  314. { begin: /\b(WITH|WITHOUT)\s+HOLD\b/ },
  315. { begin: /\bWITH\s+(CASCADED|LOCAL)\s+CHECK\s+OPTION\b/ },
  316. { begin: /\bEXCLUDE\s+(TIES|NO\s+OTHERS)\b/ },
  317. { begin: /\bFORMAT\s+(TEXT|XML|JSON|YAML)\b/ },
  318. { begin: /\bSET\s+((SESSION|LOCAL)\s+)?NAMES\b/ },
  319. { begin: /\bIS\s+(NOT\s+)?UNKNOWN\b/ },
  320. { begin: /\bSECURITY\s+LABEL\b/ },
  321. { begin: /\bSTANDALONE\s+(YES|NO|NO\s+VALUE)\b/ },
  322. { begin: /\bWITH\s+(NO\s+)?DATA\b/ },
  323. { begin: /\b(FOREIGN|SET)\s+DATA\b/ },
  324. { begin: /\bSET\s+(CATALOG|CONSTRAINTS)\b/ },
  325. { begin: /\b(WITH|FOR)\s+ORDINALITY\b/ },
  326. { begin: /\bIS\s+(NOT\s+)?DOCUMENT\b/ },
  327. { begin: /\bXML\s+OPTION\s+(DOCUMENT|CONTENT)\b/ },
  328. { begin: /\b(STRIP|PRESERVE)\s+WHITESPACE\b/ },
  329. { begin: /\bNO\s+(ACTION|MAXVALUE|MINVALUE)\b/ },
  330. { begin: /\bPARTITION\s+BY\s+(RANGE|LIST|HASH)\b/ },
  331. { begin: /\bAT\s+TIME\s+ZONE\b/ },
  332. { begin: /\bGRANTED\s+BY\b/ },
  333. { begin: /\bRETURN\s+(QUERY|NEXT)\b/ },
  334. { begin: /\b(ATTACH|DETACH)\s+PARTITION\b/ },
  335. { begin: /\bFORCE\s+ROW\s+LEVEL\s+SECURITY\b/ },
  336. { begin: /\b(INCLUDING|EXCLUDING)\s+(COMMENTS|CONSTRAINTS|DEFAULTS|IDENTITY|INDEXES|STATISTICS|STORAGE|ALL)\b/ },
  337. { begin: /\bAS\s+(ASSIGNMENT|IMPLICIT|PERMISSIVE|RESTRICTIVE|ENUM|RANGE)\b/ }
  338. ]
  339. },
  340. // functions named as keywords, followed by '('
  341. {
  342. begin: /\b(FORMAT|FAMILY|VERSION)\s*\(/,
  343. //keywords: { built_in: 'FORMAT FAMILY VERSION' }
  344. },
  345. // INCLUDE ( ... ) in index_parameters in CREATE TABLE
  346. {
  347. begin: /\bINCLUDE\s*\(/,
  348. keywords: 'INCLUDE'
  349. },
  350. // not highlight RANGE if not in frame_clause (not 100% correct, but seems satisfactory)
  351. {
  352. begin: /\bRANGE(?!\s*(BETWEEN|UNBOUNDED|CURRENT|[-0-9]+))/
  353. },
  354. // disable highlighting in commands CREATE AGGREGATE/COLLATION/DATABASE/OPERTOR/TEXT SEARCH .../TYPE
  355. // and in PL/pgSQL RAISE ... USING
  356. {
  357. begin: /\b(VERSION|OWNER|TEMPLATE|TABLESPACE|CONNECTION\s+LIMIT|PROCEDURE|RESTRICT|JOIN|PARSER|COPY|START|END|COLLATION|INPUT|ANALYZE|STORAGE|LIKE|DEFAULT|DELIMITER|ENCODING|COLUMN|CONSTRAINT|TABLE|SCHEMA)\s*=/
  358. },
  359. // PG_smth; HAS_some_PRIVILEGE
  360. {
  361. //className: 'built_in',
  362. begin: /\b(PG_\w+?|HAS_[A-Z_]+_PRIVILEGE)\b/,
  363. relevance: 10
  364. },
  365. // extract
  366. {
  367. begin: /\bEXTRACT\s*\(/,
  368. end: /\bFROM\b/,
  369. returnEnd: true,
  370. keywords: {
  371. //built_in: 'EXTRACT',
  372. type: 'CENTURY DAY DECADE DOW DOY EPOCH HOUR ISODOW ISOYEAR MICROSECONDS ' +
  373. 'MILLENNIUM MILLISECONDS MINUTE MONTH QUARTER SECOND TIMEZONE TIMEZONE_HOUR ' +
  374. 'TIMEZONE_MINUTE WEEK YEAR'
  375. }
  376. },
  377. // xmlelement, xmlpi - special NAME
  378. {
  379. begin: /\b(XMLELEMENT|XMLPI)\s*\(\s*NAME/,
  380. keywords: {
  381. //built_in: 'XMLELEMENT XMLPI',
  382. keyword: 'NAME'
  383. }
  384. },
  385. // xmlparse, xmlserialize
  386. {
  387. begin: /\b(XMLPARSE|XMLSERIALIZE)\s*\(\s*(DOCUMENT|CONTENT)/,
  388. keywords: {
  389. //built_in: 'XMLPARSE XMLSERIALIZE',
  390. keyword: 'DOCUMENT CONTENT'
  391. }
  392. },
  393. // Sequences. We actually skip everything between CACHE|INCREMENT|MAXVALUE|MINVALUE and
  394. // nearest following numeric constant. Without with trick we find a lot of "keywords"
  395. // in 'avrasm' autodetection test...
  396. {
  397. beginKeywords: 'CACHE INCREMENT MAXVALUE MINVALUE',
  398. end: hljs.C_NUMBER_RE,
  399. returnEnd: true,
  400. keywords: 'BY CACHE INCREMENT MAXVALUE MINVALUE'
  401. },
  402. // WITH|WITHOUT TIME ZONE as part of datatype
  403. {
  404. className: 'type',
  405. begin: /\b(WITH|WITHOUT)\s+TIME\s+ZONE\b/
  406. },
  407. // INTERVAL optional fields
  408. {
  409. className: 'type',
  410. begin: /\bINTERVAL\s+(YEAR|MONTH|DAY|HOUR|MINUTE|SECOND)(\s+TO\s+(MONTH|HOUR|MINUTE|SECOND))?\b/
  411. },
  412. // Pseudo-types which allowed only as return type
  413. {
  414. begin: /\bRETURNS\s+(LANGUAGE_HANDLER|TRIGGER|EVENT_TRIGGER|FDW_HANDLER|INDEX_AM_HANDLER|TSM_HANDLER)\b/,
  415. keywords: {
  416. keyword: 'RETURNS',
  417. type: 'LANGUAGE_HANDLER TRIGGER EVENT_TRIGGER FDW_HANDLER INDEX_AM_HANDLER TSM_HANDLER'
  418. }
  419. },
  420. // Known functions - only when followed by '('
  421. {
  422. begin: '\\b(' + FUNCTIONS_RE + ')\\s*\\('
  423. //keywords: { built_in: FUNCTIONS }
  424. },
  425. // Types
  426. {
  427. begin: '\\.(' + TYPES_RE + ')\\b' // prevent highlight as type, say, 'oid' in 'pgclass.oid'
  428. },
  429. {
  430. begin: '\\b(' + TYPES_RE + ')\\s+PATH\\b', // in XMLTABLE
  431. keywords: {
  432. keyword: 'PATH', // hopefully no one would use PATH type in XMLTABLE...
  433. type: TYPES.replace('PATH ','')
  434. }
  435. },
  436. {
  437. className: 'type',
  438. begin: '\\b(' + TYPES_RE + ')\\b'
  439. },
  440. // Strings, see https://www.postgresql.org/docs/11/static/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS
  441. {
  442. className: 'string',
  443. begin: '\'', end: '\'',
  444. contains: [{begin: '\'\''}]
  445. },
  446. {
  447. className: 'string',
  448. begin: '(e|E|u&|U&)\'', end: '\'',
  449. contains: [{begin: '\\\\.'}],
  450. relevance: 10
  451. },
  452. hljs.END_SAME_AS_BEGIN({
  453. begin: DOLLAR_STRING,
  454. end: DOLLAR_STRING,
  455. contains: [
  456. {
  457. // actually we want them all except SQL; listed are those with known implementations
  458. // and XML + JSON just in case
  459. subLanguage: ['pgsql','perl','python','tcl','r','lua','java','php','ruby','bash','scheme','xml','json'],
  460. endsWithParent: true
  461. }
  462. ]
  463. }),
  464. // identifiers in quotes
  465. {
  466. begin: '"', end: '"',
  467. contains: [{begin: '""'}]
  468. },
  469. // numbers
  470. hljs.C_NUMBER_MODE,
  471. // comments
  472. hljs.C_BLOCK_COMMENT_MODE,
  473. COMMENT_MODE,
  474. // PL/pgSQL staff
  475. // %ROWTYPE, %TYPE, $n
  476. {
  477. className: 'meta',
  478. variants: [
  479. {begin: '%(ROW)?TYPE', relevance: 10}, // %TYPE, %ROWTYPE
  480. {begin: '\\$\\d+'}, // $n
  481. {begin: '^#\\w', end: '$'} // #compiler option
  482. ]
  483. },
  484. // <<labeles>>
  485. {
  486. className: 'symbol',
  487. begin: LABEL,
  488. relevance: 10
  489. }
  490. ]
  491. };
  492. }
  493. module.exports = pgsql;