integration Lua to psql II
Ten years ago, I attempted to enhance the
\dt+ command to sort results by size.
There were perhaps a hundred discussions, yet no consensus was reached on a new syntax. Eventually, I created the pspg tool,
which allows results to be sorted by any column based on the vertical cursor position.
Now, I have prepared a set of patches that integrates Lua into psql. Thanks to these modifications, anyone
can write their own \dt command with the desired behavior:
\if :{?LUA_RELEASE}
\echo :LUA_RELEASE
\luacode
psql.registerCommand ( {
name = "my.dt",
help_syntax = "\\my.dt[+] [PATTERN] [-OPTION]",
help_desc = "list tables possibly sorted by size",
handler = function(ss, ab, cmd, verbose)
local filter = " AND n.nspname <> 'pg_catalog'\n" ..
" AND n.nspname !~ '^pg_toast'\n" ..
" AND n.nspname <> 'information_schema'\n" ..
" AND pg_catalog.pg_table_is_visible(c.oid)\n"
local sort = "ORDER BY 1, 2";
local opt = psql.scanSlashOption(ss, psql.OT_NORMAL, false)
if opt == "-help" then
print "my.dt[+] [PATTERN] [-OPTION] list tables, possibly sorted"
print ""
print "Options:"
print " -asc-size sorted by size in ascending order"
print " -desc-size sorted by size in descending order"
return psql.PSQL_CMD_SKIP_LINE;
end
if opt and string.sub(opt,1,1) ~= "-" then
local schema, tablename, dot
if opt == "*" then
filter = " AND pg_catalog.pg_table_is_visible(c.oid)\n";
else
dot = string.find(opt, "%.")
if dot then
schema = string.sub(opt, 1, dot - 1)
tablename = string.sub(opt, dot + 1)
else
tablename = opt;
end
if schema then
if schema ~= "*" then
filter = " AND n.nspname = '" .. psql.connect():escape(schema) .. "'\n"
else
filter = ""
end
else
filter = " AND pg_catalog.pg_table_is_visible(c.oid)\n"
end
if tablename then
if tablename ~= "*" then
filter = filter .. " AND c.relname = '" .. psql.connect():escape(tablename) .. "'\n"
end
end
end
opt = psql.scanSlashOption(ss, psql.OT_NORMAL, false);
end
if opt == "-asc-size" then
sort = "ORDER BY pg_catalog.pg_table_size(c.oid) ASC"
elseif opt == "-desc-size" then
sort = "ORDER BY pg_catalog.pg_table_size(c.oid) DESC"
end
local query = [[
SELECT n.nspname AS "Schema",
c.relname AS "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 't' THEN 'TOAST' END AS "Type",
pg_catalog.pg_get_userbyid(c.relowner) AS "Owner" ]]
if verbose then
query = query .. ",\n" ..
[[
pg_size_pretty(pg_catalog.pg_table_size(c.oid)) AS "Size",
pg_catalog.obj_description(c.oid, 'pg_class') AS "Description" ]]
end
query = query .. "\n" .. [[
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','m')]] .. "\n"
query = query .. filter
query = query .. sort
psql.printQuery(psql.exec(query))
return psql.PSQL_CMD_SKIP_LINE;
end } )
\.
\endif
Usage:
(2026-08-26 22:24:29) postgres=# \my.dt+ pg_catalog.* -desc-size ┌────────────┬─────────────────────────────────┬───────┬──────────┬────────────┬─────────────┐ │ Schema │ Name │ Type │ Owner │ Size │ Description │ ╞════════════╪═════════════════════════════════╪═══════╪══════════╪════════════╪═════════════╡ │ pg_catalog │ pg_proc │ table │ postgres │ 912 kB │ ∅ │ │ pg_catalog │ pg_rewrite │ table │ postgres │ 688 kB │ ∅ │ │ pg_catalog │ pg_attribute │ table │ postgres │ 520 kB │ ∅ │ │ pg_catalog │ pg_description │ table │ postgres │ 408 kB │ ∅ │ │ pg_catalog │ pg_collation │ table │ postgres │ 320 kB │ ∅ │ │ pg_catalog │ pg_statistic │ table │ postgres │ 272 kB │ ∅ │ │ pg_catalog │ pg_type │ table │ postgres │ 168 kB │ ∅ │ │ pg_catalog │ pg_class │ table │ postgres │ 160 kB │ ∅ │ │ pg_catalog │ pg_depend │ table │ postgres │ 152 kB │ ∅ │ │ pg_catalog │ pg_operator │ table │ postgres │ 144 kB │ ∅ │ │ pg_catalog │ pg_amop │ table │ postgres │ 88 kB │ ∅ │ │ pg_catalog │ pg_constraint │ table │ postgres │ 80 kB │ ∅ │ │ pg_catalog │ pg_amproc │ table │ postgres │ 72 kB │ ∅ │ │ pg_catalog │ pg_index │ table │ postgres │ 72 kB │ ∅ │ │ pg_catalog │ pg_aggregate │ table │ postgres │ 64 kB │ ∅ │ │ pg_catalog │ pg_ts_config_map │ table │ postgres │ 64 kB │ ∅ │ │ pg_catalog │ pg_init_privs │ table │ postgres │ 64 kB │ ∅ │ │ pg_catalog │ pg_opclass │ table │ postgres │ 56 kB │ ∅ │
posted by Pavel Stěhule @ 1:34 PM
0 Comments
