Postgres: Get numbers of affected rows in statement trigger

Question

I just find out that statement triggers are executed also if no rows were affected. Interesting - but anyway - is there a way to get no of rows affected, or at least if any row was affected?

Trigger is after insert, statement.

Thanks!

Answer 1

You could use the following:

SELECT count(*)
FROM mytable
WHERE xmin::text::bigint = txid_current();

That will count all the rows created or updated in the same transaction as the trigger. Of course, if there are more modifications of the same table in the same transaction, that will not return the desired result.

You can even create an index to speed up that query:

CREATE INDEX ON mytable ((xmin::text::bigint));