Спер отсюда, дабы не потерялось и было под рукой. Далее идет полное цитирование автора, за что ему нескончаемый респект.
Здесь я собрал небольшой список запросов, которые помогают получить информацию об различных объектах в кластере. Да и вообще буду постепенно пополнять список по мере нахождения полезных запросов. Все запросы были проверены на сервере версии 8.3.
Получить список всех БД: | SQL | 1
2
3
4
5
6
7
8
| 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; |
|
| SQL | 1
2
3
4
5
6
7
8
9
10
11
| 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; |
|
| SQL | 1
2
3
4
5
6
| 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; |
|
| SQL | 1
2
3
4
5
6
7
8
9
10
11
12
13
| 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; |
|
| SQL | 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
| 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 для получения списка последовательностей
Получить список функций (так же будет выведен список аргументов, тело функции и др. полезная информация):
| SQL | 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
| 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'; |
|
| SQL | 1
2
3
4
5
6
7
8
9
10
11
12
13
14
| 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); |
|
|
