Track updated rows across multiple tables with Postgres

example.sql

CREATE SEQUENCE sync_id;

CREATE FUNCTION bump_sync_id() RETURNS trigger AS $$
begin
  NEW.sync_id := nextval('sync_id');
  RETURN NEW;
END;
$$ LANGUAGE PLPGSQL;

CREATE TABLE dogs (id SERIAL, name VARCHAR NOT NULL, sync_id INTEGER NOT NULL);
CREATE TRIGGER bump_sync_id BEFORE INSERT OR UPDATE ON dogs FOR EACH ROW EXECUTE PROCEDURE bump_sync_id();

CREATE TABLE cats (id SERIAL, name VARCHAR NOT NULL, sync_id INTEGER NOT NULL);
CREATE TRIGGER bump_sync_id BEFORE INSERT OR UPDATE ON cats FOR EACH ROW EXECUTE PROCEDURE bump_sync_id();

CREATE VIEW dogs_and_cats AS
  (SELECT 'dogs' AS table_name, * FROM dogs)
  UNION (SELECT 'cats' AS table_name, * FROM cats)
  ORDER BY sync_id ASC;

INSERT INTO dogs (name) VALUES ('aaa'); -- 1
INSERT INTO cats (name) VALUES ('bbb'); -- 2
INSERT INTO dogs (name) VALUES ('ccc'); -- 3

select * from dogs_and_cats;
-- See: output-1.txt

UPDATE dogs SET name='ddd' WHERE name='aaa'; -- 4
UPDATE cats SET name='eee' WHERE name='bbb'; -- 5

select * from dogs_and_cats WHERE sync_id > 3;
-- See: output-2.txt

output-1.txt

 table_name | id | name | sync_id 
------------+----+------+---------
 dogs       |  1 | aaa  |       1
 cats       |  1 | bbb  |       2
 dogs       |  2 | ccc  |       3
(3 rows)

output-2.txt

 table_name | id | name | sync_id 
------------+----+------+---------
 dogs       |  1 | ddd  |       4
 cats       |  1 | eee  |       5
(2 rows)