WITH ... AS ( DELETE ... RETURNING )

[dfcatdivvy]

The following is valid in postgresql but gives an error in JSqlParser

WITH updated_rows AS (
    DELETE FROM documents WHERE something='something'
    RETURNING *
)
SELECT * FROM updated_rows LIMIT 1000;

Error:

net.sf.jsqlparser.JSQLParserException: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "DELETE" "DELETE"
    at line 2, column 5.

Was expecting one of:

    "("
    "VALUE"
    "VALUES"
    "WITH"
    <K_SELECT>

	at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatements(CCJSqlParserUtil.java:327)
	at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatements(CCJSqlParserUtil.java:299)
	at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatements(CCJSqlParserUtil.java:274)
	...

[manticore-projects]

Greetings!

Thank you for reporting, you are right: WITH ... AS ( ... ) has been implemented as WITH ... AS ( SELECT ... ) only (because this the normal use-case). Although the RETURNING clause will make DELETE and INSERT and UPDATE relevant.

A major refactoring was needed.

[manticore-projects]

Working now on it.

[manticore-projects]

Duplicate #1916