postgresql - How do I show the num records affected in a rolled-back transaction in Postgres pgAdmin?

Question

In SQL Server when I would use Mgmt Studio to make changes to data I would often wrap the queries in a transaction to semi-verify the data being changed was as expected.

Example:

BEGIN TRANSACTION;
UPDATE mytable SET col1 = 'blah' WHERE col2 = x and col3 = y;
ROLLBACK;

The Messages tab would display

(1 row affected)

I would see that "1 row affected", which agreed with what I expected, then I would just change ROLLBACK to COMMIT and run it again to make the changes stick.

Is there a typical approach to do this in pgAdmin? When I run the query in the transaction, it only displays in the Messages tab the time it took to execute:

Query returned successfully in 141 msec.

As a clunky work-around I can use a plpgsql script with the DIAGNOSTICS like this:

BEGIN TRANSACTION;
DO $$
DECLARE rowCount int;
BEGIN
   UPDATE mytable SET col1 = 'blah' WHERE col2 = x and col3 = y;
   GET DIAGNOSTICS rowCount = row_count;
   RAISE NOTICE 'num rows affected = %', rowCount;
END; $$;
ROLLBACK;

The Messages tab then shows the following output:

NOTICE:  num rows affected = 1
ROLLBACK

Query returned successfully in 125 msec.

Is there any better approach than that ugly script?

question from:https://stackoverflow.com/questions/66052442/how-do-i-show-the-num-records-affected-in-a-rolled-back-transaction-in-postgres

Answer 1

Waitting for answers