postgresql.formatter.ts

import { DialectOptions } from '../../dialect.js';
import { expandPhrases } from '../../expandPhrases.js';
import { functions } from './postgresql.functions.js';
import { dataTypes, keywords } from './postgresql.keywords.js';

const reservedSelect = expandPhrases(['SELECT [ALL | DISTINCT]']);

const reservedClauses = expandPhrases([
  // queries
  'WITH [RECURSIVE]',
  'FROM',
  'WHERE',
  'GROUP BY [ALL | DISTINCT]',
  'HAVING',
  'WINDOW',
  'PARTITION BY',
  'ORDER BY',
  'LIMIT',
  'OFFSET',
  'FETCH {FIRST | NEXT}',
  'FOR {UPDATE | NO KEY UPDATE | SHARE | KEY SHARE} [OF]',
  // Data manipulation
  // - insert:
  'INSERT INTO',
  'VALUES',
  'DEFAULT VALUES',
  // - update:
  'SET',
  // other
  'RETURNING',
]);

const standardOnelineClauses = expandPhrases([
  'CREATE [GLOBAL | LOCAL] [TEMPORARY | TEMP | UNLOGGED] TABLE [IF NOT EXISTS]',
]);

const tabularOnelineClauses = expandPhrases([
  // - create
  'CREATE [OR REPLACE] [TEMP | TEMPORARY] [RECURSIVE] VIEW',
  'CREATE [MATERIALIZED] VIEW [IF NOT EXISTS]',
  // - update:
  'UPDATE [ONLY]',
  'WHERE CURRENT OF',
  // - insert:
  'ON CONFLICT',
  // - delete:
  'DELETE FROM [ONLY]',
  // - drop table:
  'DROP TABLE [IF EXISTS]',
  // - alter table:
  'ALTER TABLE [IF EXISTS] [ONLY]',
  'ALTER TABLE ALL IN TABLESPACE',
  'RENAME [COLUMN]',
  'RENAME TO',
  'ADD [COLUMN] [IF NOT EXISTS]',
  'DROP [COLUMN] [IF EXISTS]',
  'ALTER [COLUMN]',
  'SET DATA TYPE', // for alter column
  '{SET | DROP} DEFAULT', // for alter column
  '{SET | DROP} NOT NULL', // for alter column
  // - truncate:
  'TRUNCATE [TABLE] [ONLY]',
  // other
  'SET SCHEMA',
  'AFTER',
  // https://www.postgresql.org/docs/14/sql-commands.html
  'ABORT',
  'ALTER AGGREGATE',
  'ALTER COLLATION',
  'ALTER CONVERSION',
  'ALTER DATABASE',
  'ALTER DEFAULT PRIVILEGES',
  'ALTER DOMAIN',
  'ALTER EVENT TRIGGER',
  'ALTER EXTENSION',
  'ALTER FOREIGN DATA WRAPPER',
  'ALTER FOREIGN TABLE',
  'ALTER FUNCTION',
  'ALTER GROUP',
  'ALTER INDEX',
  'ALTER LANGUAGE',
  'ALTER LARGE OBJECT',
  'ALTER MATERIALIZED VIEW',
  'ALTER OPERATOR',
  'ALTER OPERATOR CLASS',
  'ALTER OPERATOR FAMILY',
  'ALTER POLICY',
  'ALTER PROCEDURE',
  'ALTER PUBLICATION',
  'ALTER ROLE',
  'ALTER ROUTINE',
  'ALTER RULE',
  'ALTER SCHEMA',
  'ALTER SEQUENCE',
  'ALTER SERVER',
  'ALTER STATISTICS',
  'ALTER SUBSCRIPTION',
  'ALTER SYSTEM',
  'ALTER TABLESPACE',
  'ALTER TEXT SEARCH CONFIGURATION',
  'ALTER TEXT SEARCH DICTIONARY',
  'ALTER TEXT SEARCH PARSER',
  'ALTER TEXT SEARCH TEMPLATE',
  'ALTER TRIGGER',
  'ALTER TYPE',
  'ALTER USER',
  'ALTER USER MAPPING',
  'ALTER VIEW',
  'ANALYZE',
  'BEGIN',
  'CALL',
  'CHECKPOINT',
  'CLOSE',
  'CLUSTER',
  'COMMIT',
  'COMMIT PREPARED',
  'COPY',
  'CREATE ACCESS METHOD',
  'CREATE [OR REPLACE] AGGREGATE',
  'CREATE CAST',
  'CREATE COLLATION',
  'CREATE [DEFAULT] CONVERSION',
  'CREATE DATABASE',
  'CREATE DOMAIN',
  'CREATE EVENT TRIGGER',
  'CREATE EXTENSION',
  'CREATE FOREIGN DATA WRAPPER',
  'CREATE FOREIGN TABLE',
  'CREATE [OR REPLACE] FUNCTION',
  'CREATE GROUP',
  'CREATE [UNIQUE] INDEX',
  'CREATE [OR REPLACE] [TRUSTED] [PROCEDURAL] LANGUAGE',
  'CREATE OPERATOR',
  'CREATE OPERATOR CLASS',
  'CREATE OPERATOR FAMILY',
  'CREATE POLICY',
  'CREATE [OR REPLACE] PROCEDURE',
  'CREATE PUBLICATION',
  'CREATE ROLE',
  'CREATE [OR REPLACE] RULE',
  'CREATE SCHEMA [AUTHORIZATION]',
  'CREATE [TEMPORARY | TEMP | UNLOGGED] SEQUENCE',
  'CREATE SERVER',
  'CREATE STATISTICS',
  'CREATE SUBSCRIPTION',
  'CREATE TABLESPACE',
  'CREATE TEXT SEARCH CONFIGURATION',
  'CREATE TEXT SEARCH DICTIONARY',
  'CREATE TEXT SEARCH PARSER',
  'CREATE TEXT SEARCH TEMPLATE',
  'CREATE [OR REPLACE] TRANSFORM',
  'CREATE [OR REPLACE] [CONSTRAINT] TRIGGER',
  'CREATE TYPE',
  'CREATE USER',
  'CREATE USER MAPPING',
  'DEALLOCATE',
  'DECLARE',
  'DISCARD',
  'DROP ACCESS METHOD',
  'DROP AGGREGATE',
  'DROP CAST',
  'DROP COLLATION',
  'DROP CONVERSION',
  'DROP DATABASE',
  'DROP DOMAIN',
  'DROP EVENT TRIGGER',
  'DROP EXTENSION',
  'DROP FOREIGN DATA WRAPPER',
  'DROP FOREIGN TABLE',
  'DROP FUNCTION',
  'DROP GROUP',
  'DROP IDENTITY',
  'DROP INDEX',
  'DROP LANGUAGE',
  'DROP MATERIALIZED VIEW [IF EXISTS]',
  'DROP OPERATOR',
  'DROP OPERATOR CLASS',
  'DROP OPERATOR FAMILY',
  'DROP OWNED',
  'DROP POLICY',
  'DROP PROCEDURE',
  'DROP PUBLICATION',
  'DROP ROLE',
  'DROP ROUTINE',
  'DROP RULE',
  'DROP SCHEMA',
  'DROP SEQUENCE',
  'DROP SERVER',
  'DROP STATISTICS',
  'DROP SUBSCRIPTION',
  'DROP TABLESPACE',
  'DROP TEXT SEARCH CONFIGURATION',
  'DROP TEXT SEARCH DICTIONARY',
  'DROP TEXT SEARCH PARSER',
  'DROP TEXT SEARCH TEMPLATE',
  'DROP TRANSFORM',
  'DROP TRIGGER',
  'DROP TYPE',
  'DROP USER',
  'DROP USER MAPPING',
  'DROP VIEW',
  'EXECUTE',
  'EXPLAIN',
  'FETCH',
  'GRANT',
  'IMPORT FOREIGN SCHEMA',
  'LISTEN',
  'LOAD',
  'LOCK',
  'MOVE',
  'NOTIFY',
  'OVERRIDING SYSTEM VALUE',
  'PREPARE',
  'PREPARE TRANSACTION',
  'REASSIGN OWNED',
  'REFRESH MATERIALIZED VIEW',
  'REINDEX',
  'RELEASE SAVEPOINT',
  'RESET [ALL|ROLE|SESSION AUTHORIZATION]',
  'REVOKE',
  'ROLLBACK',
  'ROLLBACK PREPARED',
  'ROLLBACK TO SAVEPOINT',
  'SAVEPOINT',
  'SECURITY LABEL',
  'SELECT INTO',
  'SET CONSTRAINTS',
  'SET ROLE',
  'SET SESSION AUTHORIZATION',
  'SET TRANSACTION',
  'SHOW',
  'START TRANSACTION',
  'UNLISTEN',
  'VACUUM',
]);

const reservedSetOperations = expandPhrases([
  'UNION [ALL | DISTINCT]',
  'EXCEPT [ALL | DISTINCT]',
  'INTERSECT [ALL | DISTINCT]',
]);

const reservedJoins = expandPhrases([
  'JOIN',
  '{LEFT | RIGHT | FULL} [OUTER] JOIN',
  '{INNER | CROSS} JOIN',
  'NATURAL [INNER] JOIN',
  'NATURAL {LEFT | RIGHT | FULL} [OUTER] JOIN',
]);

const reservedPhrases = expandPhrases([
  'PRIMARY KEY',
  'GENERATED {ALWAYS | BY DEFAULT} AS IDENTITY',
  'ON {UPDATE | DELETE} [NO ACTION | RESTRICT | CASCADE | SET NULL | SET DEFAULT]',
  'DO {NOTHING | UPDATE}',
  'AS MATERIALIZED',
  '{ROWS | RANGE | GROUPS} BETWEEN',
  // https://www.postgresql.org/docs/current/datatype-datetime.html
  '[TIMESTAMP | TIME] {WITH | WITHOUT} TIME ZONE',
  // comparison operator
  'IS [NOT] DISTINCT FROM',
  'NULLS {FIRST | LAST}',
  'WITH ORDINALITY',
]);

// https://www.postgresql.org/docs/14/index.html
export const postgresql: DialectOptions = {
  name: 'postgresql',
  tokenizerOptions: {
    reservedSelect,
    reservedClauses: [...reservedClauses, ...standardOnelineClauses, ...tabularOnelineClauses],
    reservedSetOperations,
    reservedJoins,
    reservedPhrases,
    reservedKeywords: keywords,
    reservedDataTypes: dataTypes,
    reservedFunctionNames: functions,
    nestedBlockComments: true,
    extraParens: ['[]'],
    stringTypes: [
      '$$',
      { quote: "''-qq", prefixes: ['U&'] },
      { quote: "''-qq-bs", prefixes: ['E'], requirePrefix: true },
      { quote: "''-raw", prefixes: ['B', 'X'], requirePrefix: true },
    ],
    identTypes: [{ quote: '""-qq', prefixes: ['U&'] }],
    identChars: { rest: '$' },
    paramTypes: { numbered: ['$'] },
    operators: [
      // Arithmetic
      '%',
      '^',
      '|/',
      '||/',
      '@',
      // Assignment
      ':=',
      // Bitwise
      '&',
      '|',
      '#',
      '~',
      '<<',
      '>>',
      // Byte comparison
      '~>~',
      '~<~',
      '~>=~',
      '~<=~',
      // Geometric
      '@-@',
      '@@',
      '##',
      '<->',
      '&&',
      '&<',
      '&>',
      '<<|',
      '&<|',
      '|>>',
      '|&>',
      '<^',
      '^>',
      '?#',
      '?-',
      '?|',
      '?-|',
      '?||',
      '@>',
      '<@',
      '~=',
      // JSON
      '?',
      '@?',
      '?&',
      '->',
      '->>',
      '#>',
      '#>>',
      '#-',
      // Named function params
      '=>',
      // Network address
      '>>=',
      '<<=',
      // Pattern matching
      '~~',
      '~~*',
      '!~~',
      '!~~*',
      // POSIX RegExp
      '~',
      '~*',
      '!~',
      '!~*',
      // Range/multirange
      '-|-',
      // String concatenation
      '||',
      // Text search
      '@@@',
      '!!',
      '^@',
      // Trigram/trigraph
      '<%',
      '%>',
      '<<%',
      '%>>',
      '<<->',
      '<->>',
      '<<<->',
      '<->>>',
      // Type cast
      '::',
      ':',
      // Custom operators defined by pgvector extension
      // https://github.com/pgvector/pgvector#querying
      '<#>',
      '<=>',
      '<+>',
      '<~>',
      '<%>',
    ],
    operatorKeyword: true,
  },
  formatOptions: {
    alwaysDenseOperators: ['::', ':'],
    onelineClauses: [...standardOnelineClauses, ...tabularOnelineClauses],
    tabularOnelineClauses,
  },
};