calculating derivation from pg_stat_database and watch statement

The one from important Postgres's stats view is pg_stat_database. We can see in this view lot of important cumullative metrics. But sometimes, we can prefer to see these metric related to last seconds. It is not hard to calculate it:

CREATE OR REPLACE FUNCTION public.tps(text)
RETURNS
   TABLE(xact_commit integer,
         xact_rollback integer,
         tup_returned integer,
         tup_fetched integer,
         tup_inserted integer,
         tup_updated integer,
         tup_deleted integer)
AS $$
DECLARE
  csdb pg_stat_database;
  psdb pg_stat_database;
  pts timestamp with time zone;
  sec numeric;
BEGIN
  SELECT * FROM pg_stat_database WHERE datname = $1 INTO csdb;

  psdb := current_setting('tps.vector', true)::pg_stat_database;
  pts := current_setting('tps.ts', true)::timestamp with time zone;

  sec := EXTRACT(epoch FROM (current_timestamp - pts));

  xact_commit := (csdb.xact_commit - coalesce(psdb.xact_commit, 0)) / sec;
  xact_rollback := (csdb.xact_rollback - coalesce(psdb.xact_rollback, 0)) / sec;
  tup_returned := (csdb.tup_returned - coalesce(psdb.tup_returned, 0)) / sec;
  tup_fetched := (csdb.tup_fetched - coalesce(psdb.tup_fetched, 0)) / sec;
  tup_inserted := (csdb.tup_inserted - coalesce(psdb.tup_inserted, 0)) / sec;
  tup_deleted := (csdb.tup_deleted - coalesce(psdb.tup_deleted, 0)) / sec;
  tup_updated := (csdb.tup_updated - coalesce(psdb.tup_updated, 0)) / sec;

  PERFORM set_config('tps.vector', csdb::text, false);
  PERFORM set_config('tps.ts', current_timestamp::text, false);

  RETURN NEXT;
END
$$ LANGUAGE plpgsql;

An usage is very simple:

postgres=# SELECT * FROM tps('postgres');
┌─────────────┬───────────────┬──────────────┬─────────────┬──────────────┬─────────────┬─────────────┐
│ xact_commit │ xact_rollback │ tup_returned │ tup_fetched │ tup_inserted │ tup_updated │ tup_deleted │
╞═════════════╪═══════════════╪══════════════╪═════════════╪══════════════╪═════════════╪═════════════╡
│           0 │             0 │            0 │           0 │            0 │           0 │           0 │
└─────────────┴───────────────┴──────────────┴─────────────┴──────────────┴─────────────┴─────────────┘
(1 row)

postgres=# \watch 2
                                Ne 27. června 2021, 05:57:41 (every 2s)

┌─────────────┬───────────────┬──────────────┬─────────────┬──────────────┬─────────────┬─────────────┐
│ xact_commit │ xact_rollback │ tup_returned │ tup_fetched │ tup_inserted │ tup_updated │ tup_deleted │
╞═════════════╪═══════════════╪══════════════╪═════════════╪══════════════╪═════════════╪═════════════╡
│           1 │             0 │          406 │          19 │            0 │           0 │           0 │
└─────────────┴───────────────┴──────────────┴─────────────┴──────────────┴─────────────┴─────────────┘
(1 row)

                                Ne 27. června 2021, 05:57:43 (every 2s)

┌─────────────┬───────────────┬──────────────┬─────────────┬──────────────┬─────────────┬─────────────┐
│ xact_commit │ xact_rollback │ tup_returned │ tup_fetched │ tup_inserted │ tup_updated │ tup_deleted │
╞═════════════╪═══════════════╪══════════════╪═════════════╪══════════════╪═════════════╪═════════════╡
│           0 │             0 │           32 │          32 │            0 │           0 │           0 │
└─────────────┴───────────────┴──────────────┴─────────────┴──────────────┴─────────────┴─────────────┘
(1 row)