Instantly update created record using WITH clause
I am trying to update a post created with a sentence WITH
in the same request, for example:
WITH
document AS (
INSERT INTO documents (name) VALUES ($1)
RETURNING *
)
UPDATE documents
SET documents.description = $2
FROM document
WHERE documents.id = document.id;
I have also tried
WITH
new_document AS (
INSERT INTO documents (name) VALUES ($1)
RETURNING *
),
updated_document AS (
UPDATE documents
SET documents.description = $2
WHERE documents.id = (SELECT id FROM new_document)
RETURNING *
)
SELECT * FROM updated_document;
But it doesn't work. I know this example is stupid because I can just create a new document with a name and description at the same time, but I need to do it this way in my application; the example is specially simplified.
Edit
I created a Gist with a real world example. I know I can embed the update logic document_id
in the body of the function. I believe. But imagine being urls
able to match not only documents, but many other objects such as documents, stories, users, articles, tags, etc. And so, embedding links to all of these objects in the body of the function is tedious, since (due to overload function) I would have to create features such as get_url_for_document(id uuid)
, get_url_for_story(id uuid)
, get_url_for_user(id uuid)
, etc. etc.
source to share
Do it in two separate statements or inside a function. You cannot do it as if you were trying because of the way WITH is implemented.
From the documentation :
The subqueries in WITH run concurrently with each other and with the main query. Therefore, when using data modification in WITH, the order in which the specified updates will actually occur is unpredictable. All statements are executed with the same snapshot (see Chapter 13), so they cannot "see" other effects on the target tables.
source to share
What you are trying to do is not possible.
From the manual: https://www.postgresql.org/docs/current/static/queries-with.html
WITH t AS (
UPDATE products SET price = price * 1.05
RETURNING *
)
SELECT * FROM products;
the outer SELECT will return the original prices before the UPDATE action, and in
WITH t AS (
UPDATE products SET price = price * 1.05
RETURNING *
)
SELECT * FROM t;
outer SELECT will return updated data.
In this example, your update is trying to act on rows that do not exist. This cannot be fixed with the WITH clause.
I would be interested to understand why your code made you do it this way.
source to share