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

Здесь я собрал небольшой список запросов, которые помогают получить информацию об различных объектах в кластере. Да и вообще буду постепенно пополнять список по мере нахождения полезных запросов. Все запросы были проверены на сервере версии 8.3. Получить список всех БД:
  1. SELECT d.datname AS "Name",
  2.   r.rolname AS "Owner",
  3.   pg_catalog.pg_encoding_to_char(d.encoding) AS "Encoding",
  4.   t.spcname AS "Tablespace",
  5.   pg_catalog.shobj_description(d.oid, 'pg_database') AS "Description"
  6. FROM pg_catalog.pg_database d
  7.   JOIN pg_catalog.pg_roles r ON d.datdba = r.oid
  8.   JOIN pg_catalog.pg_tablespace t ON d.dattablespace = t.oid;
Получить список всех ролей:
  1. SELECT r.rolname AS "Role name",
  2.   CASE WHEN r.rolsuper THEN 'yes' ELSE 'no' END AS "Superuser",
  3.   CASE WHEN r.rolcreaterole THEN 'yes' ELSE 'no' END AS "Create role",
  4.   CASE WHEN r.rolcreatedb THEN 'yes' ELSE 'no' END AS "Create DB",
  5.   CASE WHEN r.rolconnlimit < 0 THEN CAST('no limit' AS pg_catalog.text)
  6.        ELSE CAST(r.rolconnlimit AS pg_catalog.text) END AS "Connections",
  7.   ARRAY(SELECT b.rolname FROM pg_catalog.pg_auth_members m
  8.         JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
  9.         WHERE m.member = r.oid) AS "Member of",
  10.   pg_catalog.shobj_description(r.oid, 'pg_authid') AS "Description"
  11. FROM pg_catalog.pg_roles r;
Получить список табличных пространств (tablespaces):
  1. SELECT spcname AS "Name",
  2.   pg_catalog.pg_get_userbyid(spcowner) AS "Owner",
  3.   spclocation AS "Location",
  4.   spcacl AS "Access privileges",
  5.   pg_catalog.shobj_description(oid, 'pg_tablespace') AS "Description"
  6. FROM pg_catalog.pg_tablespace;
Получить список всех таблиц:
  1. SELECT n.nspname AS "Schema",
  2.   c.relname AS "Name",
  3.   CASE c.relkind WHEN 'r' THEN 'table'
  4.                  WHEN 'v' THEN 'view'
  5.                  WHEN 'i' THEN 'index'
  6.                  WHEN 'S' THEN 'sequence'
  7.                  WHEN 's' THEN 'special'
  8.                  END AS "Type",
  9.   r.rolname AS "Owner",
  10.   pg_catalog.obj_description(c.oid, 'pg_class') AS "Description"
  11. FROM pg_catalog.pg_class c
  12.   JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
  13.   LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace;
Получить список таблиц, принадлежащих текущему пользователю:
  1. SELECT n.nspname AS "Schema",
  2.   c.relname AS "Name",
  3.   CASE c.relkind WHEN 'r' THEN 'table'
  4.                  WHEN 'v' THEN 'view'
  5.                  WHEN 'i' THEN 'index'
  6.                  WHEN 'S' THEN 'sequence'
  7.                  WHEN 's' THEN 'special'
  8.                  END AS "Type",
  9.   r.rolname AS "Owner",
  10.   pg_catalog.obj_description(c.oid, 'pg_class') AS "Description"
  11. FROM pg_catalog.pg_class c
  12.   JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
  13.   LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
  14. WHERE c.relkind IN ('r')
  15.   AND n.nspname <> 'pg_catalog'
  16.   AND n.nspname !~ '^pg_toast'
  17.   AND pg_catalog.pg_table_is_visible(c.oid);
Этот же запрос можно использовать для получения списка индексов, представлений и последовательностей. Для этого в выражении WHERE c.relkind IN ('r') символ "r" заменяете на:
  • i для получения списка индексов
  • v для получения списка представлений
  • S для получения списка последовательностей
Получить список функций (так же будет выведен список аргументов, тело функции и др. полезная информация):
  1. SELECT n.nspname AS "Schema",
  2.   p.proname AS "Name",
  3.   CASE WHEN p.proretset THEN 'setof ' ELSE '' END
  4.     || pg_catalog.format_type(p.prorettype, NULL) AS "Result data type",
  5.   CASE WHEN proallargtypes IS NOT NULL
  6.     THEN pg_catalog.array_to_string(
  7.       ARRAY(SELECT CASE
  8.               WHEN p.proargmodes[s.i] = 'i' THEN ''
  9.               WHEN p.proargmodes[s.i] = 'o' THEN 'OUT '
  10.               WHEN p.proargmodes[s.i] = 'b' THEN 'INOUT '
  11.             END || CASE
  12.               WHEN COALESCE(p.proargnames[s.i], '') = '' THEN ''
  13.               ELSE p.proargnames[s.i] || ' '
  14.               END || pg_catalog.format_type(p.proallargtypes[s.i], NULL)
  15.             FROM
  16.               pg_catalog.generate_series(1, pg_catalog.array_upper(p.proallargtypes, 1)) AS s(i)
  17.       ), ', '
  18.     ) ELSE pg_catalog.array_to_string(
  19.       ARRAY(SELECT CASE
  20.               WHEN COALESCE(p.proargnames[s.i+1], '') = '' THEN ''
  21.               ELSE p.proargnames[s.i+1] || ' '
  22.             END || pg_catalog.format_type(p.proargtypes[s.i], NULL)
  23.             FROM
  24.               pg_catalog.generate_series(0, pg_catalog.array_upper(p.proargtypes, 1)) AS s(i)
  25.       ), ', '
  26.     )
  27.   END AS "Argument data types",
  28.   CASE WHEN p.provolatile = 'i' THEN 'immutable'
  29.        WHEN p.provolatile = 's' THEN 'stable'
  30.        WHEN p.provolatile = 'v' THEN 'volatile'
  31.   END AS "Volatility",
  32.   r.rolname AS "Owner",
  33.   l.lanname AS "Language",
  34.   p.prosrc AS "Source code",
  35.   pg_catalog.obj_description(p.oid, 'pg_proc') AS "Description"
  36. FROM pg_catalog.pg_proc p
  37.   LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
  38.   LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang
  39.   JOIN pg_catalog.pg_roles r ON r.oid = p.proowner
  40. WHERE p.prorettype <> 'pg_catalog.cstring'::pg_catalog.regtype
  41.   AND (p.proargtypes[0] IS NULL
  42.     OR p.proargtypes[0] <> 'pg_catalog.cstring'::pg_catalog.regtype)
  43.   AND NOT p.proisagg
  44.   AND pg_catalog.pg_function_is_visible(p.oid)
  45.   AND r.rolname <> 'pgsql';
Получить список агрегатных функций:
  1. SELECT n.nspname AS "Schema",
  2.   p.proname AS "Name",
  3.   pg_catalog.format_type(p.prorettype, NULL) AS "Result data type",
  4.   CASE WHEN p.pronargs = 0
  5.        THEN CAST('*' AS pg_catalog.text)
  6.        ELSE pg_catalog.array_to_string(ARRAY(
  7.          SELECT pg_catalog.format_type(p.proargtypes[s.i], NULL)
  8.          FROM pg_catalog.generate_series(0, pg_catalog.array_upper(p.proargtypes, 1)) AS s(i)
  9.        ), ', ')
  10.   END AS "Argument data types",
  11.   pg_catalog.obj_description(p.oid, 'pg_proc') AS "Description"
  12. FROM pg_catalog.pg_proc p
  13.   LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
  14. WHERE p.proisagg AND pg_catalog.pg_function_is_visible(p.oid);

Добавить комментарий

Filtered text

CAPTCHA
Этот вопрос предназначен для предотвращения автоматизированной обработки форм.
Fill in the blank.