Getting Affected Rows by UPDATE statement in RAW plpgsql

Question

This has been asked multiple times here and here, but none of the answers are suitable in my case because I do not want to execute my update statement in a PL/PgSQL function and use GET DIAGNOSTICS integer_var = ROW_COUNT.

I have to do this in raw SQL.

For instance, in MS SQL SERVER we have @@ROWCOUNT which could be used like the following :

UPDATE <target_table> 
SET Proprerty0 = Value0
WHERE <predicate>;
SELECT <computed_value_columns> 
 FROM <target> 
 WHERE @@ROWCOUNT > 0;

In one roundtrip to the database I know if the update was successfull and get the calculated values back.

What could be used instead of '@@ROWCOUNT' ? Can someone confirm that this is in fact impossible at this time ?

Thanks in advance.

EDIT 1 : I confirm that I need to use raw SQL (I wrote "raw plpgsql" in the original description).

In an attempt to make my question clearer please consider that the update statement affects only one row and think about optimistic concurrency:

1. The client did a SELECT Statement at first.

2. He builds the UPDATE and knows which database computed columns are to be included in the SELECT clause. Among other things, the predicate includes a timestamp that is computed each time the rows is updated.

3. So, if we have 1 row returned then everything is OK. If no row is returned then we know that there was a previous update and the client may need to refresh the data before trying to update clause again. This is why we need to know how many rows where affected by the update statement before returning computed columns. No row should be returned if the update fails.

Ilonailonka

Please always mention your PostgreSQL version in questions. – Debility

So you don't want to do in PL/pgSQL but in SQL. Otherwise you could use GET DIAGNOSTICS – Jerk

@CraigRinger : See corresponding tag. – Ilonailonka

Re version: My mistake. I forget that people use version-specific tags just to say "here's the version I'm using". – Debility

I've elaborated on my answer based on your edits. It looks like UPDATE ... RETURNING does what you need. – Debility

Answer 1

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.

Debility

The writable CTE works as of version 9.2. Writable CTE's are great! I don't understand why it took so long before they were implemented and most DBMS brands still don't support it, I can't work without it anymore. – Fellatio

Answer 2

Generally the answer to this question depends on the type of the driver used.

PQcmdTuples() function does what is needed, if the application uses libpq. Other libraries on top of libpq need to have some wrapper on top of this function.

For JDBC the Statement.executeUpdate() method seems to the job.

ODBC provides SQLRowCount() function for the similar purpose.

Photoconductivity