A week ago, one of the commenters of my post from two weeks ago asked about the way to do stuff like \d (“describe table”) or \l (list the databases) etc. from SQL blocks in Org-mode. Well, the answer is – rather expectedly – “this is PostgreSQL, of course it’s possible”. Here’s how psql can teach you how to do it.
Instead of starting psql normally, run it with the -E (--echo-hidden) command-line parameter. (Alternatively, you can say \set ECHO_HIDDEN on.) Then, every backslash command which queries the database displays the SQL spell it uses.
For instance, \d turns out to be
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 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','p','v','m','S','f','')
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
and \l is just
SELECT d.datname as "Name",
pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
d.datcollate as "Collate",
d.datctype as "Ctype",
pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;
Granted, this is not pretty, but at least you can see how it works under the hood.
And by the way, since Org-mode apparently uses psql anyway, you can also say things like
#+begin_src sql :engine postgresql :dbhost localhost :dbuser postgres :dbpassword docker :database postgres :dbport 5432 \d #+end_src
to see the list of tables etc.