PostgreSQL RETURNING Data From Modified Rows

[Jekshmek]

Postgresql RETURNING

INSERT INTO users (firstname, lastname) VALUES ('Joe', 'Cool') RETURNING id;

UPDATE products SET price = price * 1.10
  WHERE price <= 99.99
  RETURNING name, price AS new_price;

DELETE FROM products
  WHERE obsoletion_date = 'today'
  RETURNING *;

Err(ParserError("Expected end of statement, found: RETURNING"))

[nickolay]

This seems to be truly Postgres-specific; an optional last clause of INSERT, DELETE, or UPDATE. Of these we currently implement only INSERT..VALUES and DELETE.

Adding this for INSERT..VALUES and DELETE should be easy: you can reuse parse_select_list and add the result to SQLStatement::SQLInsert and SQLDelete: { ..., returning: Vec<SQLSelectItem> }.

@andygrove mentioned that it's fine to add dialect-specific constructs to the main parser until a better solution emerges, so I think a PR with tests would be accepted.

The INSERT .. SELECT .. RETURNING will require larger changes, as the keyword "RETURNING" is reserved in Postgres - can't be used as a table/column name - so that in INSERT INTO foo SELECT * FROM bar RETURNING id "RETURNING" is recognized as a keyword and not as a table alias. We have RESERVED_FOR_TABLE_ALIAS / RESERVED_FOR_COLUMN_ALIAS to support that, but they're not dialect-specific at the moment.

The least fun part is that Postgres allows INSERT/UPDATE/DELETE to be used in place of SELECT, as in this example from their documentation:

WITH upd AS (
  UPDATE employees SET sales_count = sales_count + 1 WHERE id =
    (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation')
    RETURNING *
)
INSERT INTO employees_log SELECT *, current_timestamp FROM upd;

We could follow Postgres' lead and change the Cte's member from SQLQuery to a new enum for INSERT/DELETE/UPDATE as well as SELECT statements. But this feels like something that really ought to be specific to Postgres dialect...

[teenjuna]

Hello there! Is this still being considered? 😅