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

Здесь я собрал небольшой список запросов, которые помогают получить информацию об различных объектах в кластере. Да и вообще буду постепенно пополнять список по мере нахождения полезных запросов. Все запросы были проверены на сервере версии 8.3.

Получить список всех БД:

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;

Получить список табличных пространств (tablespaces):

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);

Этот же запрос можно использовать для получения списка индексов, представлений и последовательностей. Для этого в выражении WHERE c.relkind IN ('r') символ "r" заменяете на:

• i для получения списка индексов
• v для получения списка представлений
• S для получения списка последовательностей

Получить список функций (так же будет выведен список аргументов, тело функции и др. полезная информация):

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);