Postgres support (DML) #47
Comments
|
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: |
|
Thanks for reporting.
Should be a simple thing to fix. Simply the result of DML statements not
being supported in CTE-s by other SQL dialects covered by this parser.
…On Fri, Feb 23, 2024, 22:54 Jonathan Ming ***@***.***> wrote:
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.
// worksconst q = psql` WITH stuff AS (SELECT foo FROM bar) INSERT INTO thing SELECT foo FROM stuff;`;
// worksconst q2 = psql`INSERT INTO bar (foo) SELECT '...' FROM somewhere RETURNING id;`;
// doesn't workconst 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!
—
Reply to this email directly, view it on GitHub
<#47 (comment)>,
or unsubscribe
<https://github.com/notifications/unsubscribe-auth/AAA43OOHXPARGRP6BVY7NQLYVD6V7AVCNFSM6AAAAABBFOFQAKVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSNRRHE3TKNZRGQ>
.
You are receiving this because you authored the thread.Message ID:
***@***.***>
[ { ***@***.***": "http://schema.org", ***@***.***": "EmailMessage",
"potentialAction": { ***@***.***": "ViewAction", "target": "
#47 (comment)",
"url": "
#47 (comment)",
"name": "View Issue" }, "description": "View this Issue on GitHub",
"publisher": { ***@***.***": "Organization", "name": "GitHub", "url": "
https://github.com" } } ]
|
|
@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. |
|
Ah, you're right, apologies for the misguided bug report! I'm also trying to configure |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
DML
AS aliasOVERRIDING { SYSTEM | USER } VALUEDEFAULT VALUESDEFAULTin values listON CONFLICTclauseON 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 likeint4_opsON CONFLICT (col1) WHERE index_predicateON 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 conditionRETURNING col1, col2, ...RETURNING *RETURNING output_expression [[AS] output_name]UPDATE [ONLY] tablenametablename *[AS] aliasSET col = DEFAULT(col1, col2) = (1, 2)SET (col1, col2) = ROW (expr1, expr2)SET (col1, col2) = ROW (DEFAULT, ...)col = (SELECT ...)FROMWHEREWHERE CURRENT OF cursorRETURNING *RETURNING output_expression [[AS] output_name]ONLYtablename *[AS] aliasUSING from_item, ...WHEREWHERE CURRENT OF cursorRETURNING *RETURNING output_expression [[AS] output_name]MERGE INTO [ONLY]tablename *AS aliasUSING [ONLY] tablename [*] ON join_conditionUSING (query) [[AS] alias] ON join_conditionWHEN [NOT] MATCHED [AND condition] THEN actionDO NOTHINGDELETEUPDATE SET ...INSERT [( column_name [, ...] )] VALUES ( ... )INSERT ... DEFAULT VALUESINSERT OVERRIDING { SYSTEM | USER } VALUE ...TRUNCATE [TABLE]TRUNCATE [ONLY]tablename *RESTART IDENTITY | CONTINUE IDENTITYCASCADE | RESTRICTThe text was updated successfully, but these errors were encountered: