nene/sql-parser-cst

Postgres support (DML)

nene opened this issue · 4 comments

nene commented

DML

  • INSERT INTO
    • WITH clause
    • AS alias
    • OVERRIDING { SYSTEM | USER } VALUE
    • DEFAULT VALUES
    • DEFAULT in values list
    • insert a query
    • ON CONFLICT clause
      • ON CONFLICT (col1, col2, ...)
      • ON CONFLICT ((expr1), (expr2))
      • ON CONFLICT (func1(), func2())
      • ON CONFLICT (col1 {ASC | DESC})
      • ON CONFLICT (col1 NULLS {FIRST | LAST})
      • ON CONFLICT (col1 COLLATE myCollation)
      • ON CONFLICT (col1 opclass) where opclass is an identifier like int4_ops
      • ON CONFLICT (col1) WHERE index_predicate
      • ON CONFLICT ON CONSTRAINT name
      • ... DO NOTHING
      • ... UPDATE SET col1 = expr, col2 = DEFAULT
      • ... UPDATE SET (col1, col2) = (expr, DEFAULT)
      • ... UPDATE SET (col1, col2) = ROW (expr, DEFAULT)
      • ... UPDATE SET ... WHERE condition
    • RETURNING col1, col2, ...
    • RETURNING *
    • RETURNING output_expression [[AS] output_name]
  • UPDATE
    • WITH clause
    • UPDATE [ONLY] tablename
    • tablename *
    • [AS] alias
    • SET col = DEFAULT
    • multi-column assignment: (col1, col2) = (1, 2)
    • SET (col1, col2) = ROW (expr1, expr2)
    • SET (col1, col2) = ROW (DEFAULT, ...)
    • sub-select assignment:col = (SELECT ...)
    • FROM
    • WHERE
    • WHERE CURRENT OF cursor
    • RETURNING *
    • RETURNING output_expression [[AS] output_name]
  • DELETE FROM
    • WITH clause
    • ONLY
    • tablename *
    • [AS] alias
    • USING from_item, ...
    • WHERE
    • WHERE CURRENT OF cursor
    • RETURNING *
    • RETURNING output_expression [[AS] output_name]
  • MERGE INTO
    • WITH clause
    • MERGE INTO [ONLY]
    • tablename *
    • AS alias
    • USING [ONLY] tablename [*] ON join_condition
    • USING (query) [[AS] alias] ON join_condition
    • WHEN [NOT] MATCHED [AND condition] THEN action
    • merge actions:
      • DO NOTHING
      • DELETE
      • UPDATE SET ...
      • INSERT [( column_name [, ...] )] VALUES ( ... )
      • INSERT ... DEFAULT VALUES
      • INSERT OVERRIDING { SYSTEM | USER } VALUE ...
  • TRUNCATE
    • TRUNCATE [TABLE]
    • TRUNCATE [ONLY]
    • multi-table truncate
    • tablename *
    • RESTART IDENTITY | CONTINUE IDENTITY
    • CASCADE | RESTRICT
jming422 commented

Hi again, I found a bit here that isn't working as expected -- I know PostgreSQL is still experimental, so hopefully this info helps with the implementation effort!

I've found that INSERT statement CTEs don't work if the CTE is another INSERT, e.g.

// works
const q = psql`
  WITH stuff AS (SELECT foo FROM bar)
  INSERT INTO thing
  SELECT foo FROM stuff;
`;

// works
const q2 = psql`INSERT INTO bar (foo) SELECT '...' FROM somewhere RETURNING id;`;

// doesn't work
const q3 = psql`
  WITH stuff AS (INSERT INTO bar (foo) SELECT '...' FROM somewhere RETURNING id)
  INSERT INTO thing
  SELECT id FROM stuff;
`;

The error message looks like this:

❯ npx prettier --check --config .prettierrc.yml "js-packages/myproject-backend/**/*.{js,json,yml,md}"
Checking formatting...
js-packages/myproject-backend/src/test.jsFormattedSyntaxError: Syntax Error: Unexpected ")"
Was expecting to see: "!=", "%", "&", "'", "(", "*", "+", ",", "-", "->", "->>", ".", "/", "<", "<<", "<=", "<=>", "<>", "=", "==", ">", ">=", ">>", "AND", "AS", "BETWEEN", "COLLATE", "DIV", "GLOB", "IN", "IS", "ISNULL", "LIKE", "MATCH", "MOD", "NOT", "NOTNULL", "OR", "REGEXP", "RLIKE", "|", "||", identifier, or whitespace
--> /Users/jming/Documents/myproject/js-packages/myproject-backend/src/test.js:1:78
  |
1 | WITH stuff AS (INSERT INTO bar (foo) SELECT '...' FROM somewhere RETURNING id)
  |                                                                              ^
    at parse (/Users/jming/Documents/myproject/node_modules/sql-parser-cst/lib/main.js:40:19)
    at Object.parse (/Users/jming/Documents/myproject/node_modules/prettier-plugin-sql-cst/dist/index.js:41:76)
    at parse4 (file:///Users/jming/Documents/myproject/node_modules/prettier/index.mjs:22117:24)
    at async textToDoc (file:///Users/jming/Documents/myproject/node_modules/prettier/index.mjs:22224:19)
    at async jr (file:///Users/jming/Documents/myproject/node_modules/prettier-plugin-embed/dist/index.js:1402:15)
    at async file:///Users/jming/Documents/myproject/node_modules/prettier-plugin-embed/dist/index.js:2356:17
    at async printEmbeddedLanguages (file:///Users/jming/Documents/myproject/node_modules/prettier/index.mjs:22168:20)
    at async printAstToDoc (file:///Users/jming/Documents/myproject/node_modules/prettier/index.mjs:22257:3)
    at async coreFormat (file:///Users/jming/Documents/myproject/node_modules/prettier/index.mjs:22611:14)
    at async formatWithCursor (file:///Users/jming/Documents/myproject/node_modules/prettier/index.mjs:22809:14)
All matched files use Prettier code style!
nene commented
nene commented

@jming422, so I came back to fix this problem and discovered that it's in fact all working just as expected.

It appears that you have simply not configured the prettier plugin to use the "postgresql" parser, instead likely using the default "sqlite" parser, which doesn't support this syntax.

jming422 commented

Ah, you're right, apologies for the misguided bug report! I'm also trying to configure prettier-plugin-embed using this option which isn't behaving like I expected. Thanks for pointing me in the right direction