Getting Affected Rows by UPDATE statement in RAW plpgsql
What you want is not currently possible in the form that you describe, but I think you can do what you want with UPDATE ... RETURNING
. See UPDATE ... RETURNING
in the manual.
UPDATE <target_table>
SET Proprerty0 = Value0
WHERE <predicate>
RETURNING Property0;
It's hard to be sure, since the example you've provided is so abstract as to be somewhat meaningless.
You can also use a wCTE, which allows more complex cases:
WITH updated_rows AS (
UPDATE <target_table>
SET Proprerty0 = Value0
WHERE <predicate>
RETURNING row_id, Property0
)
SELECT row_id, some_computed_value_from_property
FROM updated_rows;
See common table expressions (WITH
queries) and depesz's article on wCTEs.
UPDATE based on some added detail in the question, here's a demo using UPDATE ... RETURNING
:
CREATE TABLE upret_demo(
id serial primary key,
somecol text not null,
last_updated timestamptz
);
INSERT INTO upret_demo (somecol, last_updated) VALUES ('blah',current_timestamp);
UPDATE upret_demo
SET
somecol = 'newvalue',
last_updated = current_timestamp
WHERE last_updated = '2012-12-03 19:36:15.045159+08' -- Change to your timestamp
RETURNING
somecol || '_computed' AS a,
'totally_new_computed_column' AS b;
Output when run the 1st time:
a | b
-------------------+-----------------------------
newvalue_computed | totally_new_computed_column
(1 row)
When run again, it'll have no effect and return no rows.
If you have more complex calculations to do in the result set, you can use a wCTE so you can JOIN on the results of the update and do other complex things.
WITH upd_row AS (
UPDATE upret_demo SET
somecol = 'newvalue',
last_updated = current_timestamp
WHERE last_updated = '2012-12-03 19:36:15.045159+08'
RETURNING id, somecol, last_updated
)
SELECT
'row_'||id||'_'||somecol||', updated '||last_updated AS calc1,
repeat('x',4) AS calc2
FROM upd_row;
In other words: Use UPDATE ... RETURNING
, either directly to produce the calculated rows, or in a writeable CTE for more complex cases.