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      │ ∅           │

initial integration lua language to psql

It can looks like

(2026-08-06 17:44:32) postgres=# \luacode 
Enter code to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> function x(n)
>>   return n + 10
>> end
>> \.
(2026-08-06 17:45:32) postgres=# \luacode
Enter code to be copied followed by a newline.
End with a backslash and a period on a line by itself, or an EOF signal.
>> print (x(10))
>> \.
20

https://github.com/okbob/lua-psql