Postgresql – How to control how Pentaho DI formats integers coming from a text file

You could use UNION in the CTE. There is still some duplication of code but less than before:

with etl (storenumber, filename, last_extracted) as
(
    ( select distinct on (storenumber)
          storenumber, 
          'storefinancialdate',
          extracteddate + extractedtime::time 
      from storefinancialdate
      order by
          storenumber,
          extracteddate desc, extractedtime desc
    )
  union all
    ( select distinct on (storenumber)
          storenumber, 
          'storeproduct',
          extracteddate + extractedtime::time 
      from storeproduct
      order by
          storenumber,
          extracteddate desc, extractedtime desc
    )
  -- union all ...
)
insert into star.stat_etl 
    (storenumber, filename, last_extracted, last_loaded)
select 
    storenumber,
    filename,
    last_extracted,
    transaction_timestamp()
from etl
  on conflict (storenumber, filename) do update
    set last_extracted = excluded.last_extracted,
        last_loaded    = excluded.last_loaded ;

No, it doesn't work like that.

The transaction logs don't contain queries. They contain the changes to disk blocks - heap and indexes - along with records of transaction ID allocations, commits, etc. It's all very low level, and it makes absolutely no sense to try to apply it to another unrelated PostgreSQL instance.

You can use the transaction logs (WAL) for replication, per the PostgreSQL manual.

PostgreSQL also has the beginnings of logical replication functionality. Currently that's mainly accessible via 3rd party tools like pglogical. There are trigger-based alternatives like Londiste too.