Created
August 16, 2019 18:24
-
-
Save JoshCheek/5071510f0b693c2b3bf88fbf573b9784 to your computer and use it in GitHub Desktop.
List db tables by the recentness of their `created_at` column.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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