全テーブルのレコード件数を一度に取得するSQLを作成するSQL

select 'table1' as table_name, count(*) as count from table1 union all
select 'table2' as table_name, count(*) as count from table2 union all
select 'table3' as table_name, count(*) as count from table3 union all
select 'table4' as table_name, count(*) as count from table4 union all
select 'table5' as table_name, count(*) as count from table5

上記のようなSQLをつくるSQL

with
  t as (
    select
      'select '''||tablename||''' as table_name, count(*) as count from '||tablename as query,
      casewhen lead(tablename) over (order by tablename) is not null then ' union all' else '' end as u
    from
      pg_tables
    where
      tableowner = 'foo'
  )
select
  query || u
from
  t
;