(redirected from Homepage)

Strona domowa

Witam na mojej prywatnej stronie internetowej!

[If this is all Polish to you, click here: English]

Uwaga: z oczywistych powodów nie mogę zagwarantować swojej nieomylności, choć staram się o zgodność tego, co piszę, z Prawdą. Jest również oczywiste, że nie gwarantuję takiej zgodności w przypadku komentarzy. Umieszczenie linku do strony spoza niniejszego serwisu nie musi oznaczać, że podzielam poglądy autora tej strony, a jedynie, że uważam ją za wartościową z takich czy innych powodów.

Marcin ‘mbork’ Borkowski

2020-03-28 psql as a PostgreSQL teacher

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.

CategoryEnglish, CategoryBlog, CategoryPostgreSQL

Comments on this page

More...