Полезные запросы к PostgreSQL

SELECT d.datname AS "Name",
  r.rolname AS "Owner",
  pg_catalog.pg_encoding_to_char(d.encoding) AS "Encoding",
  t.spcname AS "Tablespace",
  pg_catalog.shobj_description(d.oid, 'pg_database') AS "Description"
FROM pg_catalog.pg_database d
  JOIN pg_catalog.pg_roles r ON d.datdba = r.oid
  JOIN pg_catalog.pg_tablespace t ON d.dattablespace = t.oid;

SELECT r.rolname AS "Role name",
  CASE WHEN r.rolsuper THEN 'yes' ELSE 'no' END AS "Superuser",
  CASE WHEN r.rolcreaterole THEN 'yes' ELSE 'no' END AS "Create role",
  CASE WHEN r.rolcreatedb THEN 'yes' ELSE 'no' END AS "Create DB",
  CASE WHEN r.rolconnlimit < 0 THEN CAST('no limit' AS pg_catalog.text)
       ELSE CAST(r.rolconnlimit AS pg_catalog.text) END AS "Connections",
  ARRAY(SELECT b.rolname FROM pg_catalog.pg_auth_members m
        JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
        WHERE m.member = r.oid) AS "Member of",
  pg_catalog.shobj_description(r.oid, 'pg_authid') AS "Description"
FROM pg_catalog.pg_roles r;

SELECT spcname AS "Name",
  pg_catalog.pg_get_userbyid(spcowner) AS "Owner",
  spclocation AS "Location",
  spcacl AS "Access privileges",
  pg_catalog.shobj_description(oid, 'pg_tablespace') AS "Description"
FROM pg_catalog.pg_tablespace;

SELECT n.nspname AS "Schema",
  c.relname AS "Name",
  CASE c.relkind WHEN 'r' THEN 'table'
                 WHEN 'v' THEN 'view'
                 WHEN 'i' THEN 'index'
                 WHEN 'S' THEN 'sequence'
                 WHEN 's' THEN 'special'
                 END AS "Type",
  r.rolname AS "Owner",
  pg_catalog.obj_description(c.oid, 'pg_class') AS "Description"
FROM pg_catalog.pg_class c
  JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
  LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace;

SELECT n.nspname AS "Schema",
  c.relname AS "Name",
  CASE c.relkind WHEN 'r' THEN 'table'
                 WHEN 'v' THEN 'view'
                 WHEN 'i' THEN 'index'
                 WHEN 'S' THEN 'sequence'
                 WHEN 's' THEN 'special'
                 END AS "Type",
  r.rolname AS "Owner",
  pg_catalog.obj_description(c.oid, 'pg_class') AS "Description"
FROM pg_catalog.pg_class c
  JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
  LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r')
  AND n.nspname <> 'pg_catalog'
  AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid);

SELECT n.nspname AS "Schema",
  p.proname AS "Name",
  CASE WHEN p.proretset THEN 'setof ' ELSE '' END
    || pg_catalog.format_type(p.prorettype, NULL) AS "Result data type",
  CASE WHEN proallargtypes IS NOT NULL
    THEN pg_catalog.array_to_string(
      ARRAY(SELECT CASE
              WHEN p.proargmodes[s.i] = 'i' THEN ''
              WHEN p.proargmodes[s.i] = 'o' THEN 'OUT '
              WHEN p.proargmodes[s.i] = 'b' THEN 'INOUT '
            END || CASE
              WHEN COALESCE(p.proargnames[s.i], '') = '' THEN ''
              ELSE p.proargnames[s.i] || ' '
              END || pg_catalog.format_type(p.proallargtypes[s.i], NULL)
            FROM
              pg_catalog.generate_series(1, pg_catalog.array_upper(p.proallargtypes, 1)) AS s(i)
      ), ', '
    ) ELSE pg_catalog.array_to_string(
      ARRAY(SELECT CASE
              WHEN COALESCE(p.proargnames[s.i+1], '') = '' THEN ''
              ELSE p.proargnames[s.i+1] || ' '
            END || pg_catalog.format_type(p.proargtypes[s.i], NULL)
            FROM
              pg_catalog.generate_series(0, pg_catalog.array_upper(p.proargtypes, 1)) AS s(i)
      ), ', '
    )
  END AS "Argument data types",
  CASE WHEN p.provolatile = 'i' THEN 'immutable'
       WHEN p.provolatile = 's' THEN 'stable'
       WHEN p.provolatile = 'v' THEN 'volatile'
  END AS "Volatility",
  r.rolname AS "Owner",
  l.lanname AS "Language",
  p.prosrc AS "Source code",
  pg_catalog.obj_description(p.oid, 'pg_proc') AS "Description"
FROM pg_catalog.pg_proc p
  LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
  LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang
  JOIN pg_catalog.pg_roles r ON r.oid = p.proowner
WHERE p.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype
  AND (p.proargtypes[0] IS NULL
    OR p.proargtypes[0] <> 'pg_catalog.cstring'::pg_catalog.regtype)
  AND NOT p.proisagg
  AND pg_catalog.pg_function_is_visible(p.oid)
  AND r.rolname <> 'pgsql';

SELECT n.nspname AS "Schema",
  p.proname AS "Name",
  pg_catalog.format_type(p.prorettype, NULL) AS "Result data type",
  CASE WHEN p.pronargs = 0
       THEN CAST('*' AS pg_catalog.text)
       ELSE pg_catalog.array_to_string(ARRAY(
         SELECT pg_catalog.format_type(p.proargtypes[s.i], NULL)
         FROM pg_catalog.generate_series(0, pg_catalog.array_upper(p.proargtypes, 1)) AS s(i)
       ), ', ')
  END AS "Argument data types",
  pg_catalog.obj_description(p.oid, 'pg_proc') AS "Description"
FROM pg_catalog.pg_proc p
  LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE p.proisagg AND pg_catalog.pg_function_is_visible(p.oid);