Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
List db tables by the recentness of their `created_at` column.
# example: ruby recentness.rb myapp_development | column -t
dbname = ARGV.fetch(0)
require 'pg'
puts "schema\ttable\tlast-insert"
PG.connect(dbname: dbname).exec(<<~SQL).each { |row| puts row.values.join "\t" }
create function pg_temp.recentness(table_schema text, table_name text, out last_insert timestamp)
as $fn$ begin
execute format('select max(created_at) from %I.%I', table_schema, table_name)
into last_insert;
end $fn$ language plpgsql;
with tables as (
select t.table_schema, t.table_name, pg_temp.recentness(t.table_schema, t.table_name) last_insert
from information_schema.tables t
inner join information_schema.columns c
on (c.table_schema = t.table_schema and t.table_name = c.table_name and c.column_name = 'created_at')
)
select * from tables order by last_insert nulls first;
SQL
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.