В ежедневной работе часто встает задача ясно и просто ссылаться на большие списки колонок и выражений в выборке, и/или обходиться с громоздкими и неясными условиями в предложении where
. Обычно для этих целей используются представления, что вполне удобно и наглядно. Можно сравнить запрос:
select v.* from v_active_user vau, v_detailized_user v where v.id=vau.id
который достаточно ясно воспринимается как "берем активных пользователей и получаем по ним детальную информацию" и этот же запрос, но, так сказать, в развернутом виде:
select
u.first_name, u.last_name, u.patronymic_name,
format('%s %s %s', u.first_name, u.last_name, u.patronymic_name) as fio,
birth_date,
(extract(days from now() - birth_date)/365.25)::int as full_years_count,
hire_date,
(current_date - hire_date) as work_time,
(select string_agg(format('%s %s', ch.first_name, ch.last_name),', ')
from children ch
where array[u.id] <@ array[ch.mother_id, ch.father_id]) as children,
dep.id as dep_id,
coalesce(dep.name, get_localized_message('Внештатный сотрудник - неприменимо')) as dep_name
....
.... as last_vaction_date,
.... as salary_history
from usr u join dep on u.dep_id=dep.id
....
where u.state='active' and not exists(select * from pending_fires ...)
and not exists(select * from usr_vacation uv where ...)
and exists(...)
and col123<>col321
...
Запросы подобного вида — с большим списком получаемых колонок и выражений на их основе, со сложными условиями и которые в реальной жизни нередко отягчены историческими напластованиями — зачастую совершенно нечитаемы и малопонятны. Наверное, стоит заметить, что само изменение понятия "активный" (например, убрать или добавить удаленных работников или сотрудников в декретном отпуске и т.п.) может стать не то чтобы нетривиальным, но очень утомительным занятием; да и на количестве ошибок оно вряд ли скажется достаточно благоприятно; и изменение списка колонок или просто выражения влечет за собой схожие последствия. Пожалуй, можно сказать, что если для таблиц выражение select * from table
строго неприемлемо, то для представлений подобного вида оно, наверное, даже предпочтительно. Ну для некоторых, по крайней мере.
Рассмотрим другую задачу. Пусть у нас есть простая таблица пользователей:
create table usr(
id serial primary key,
name text,
added timestamptz
)
и таблица друзей:
create table friend(
usr_id int references usr(id),
friend_usr_id int references usr(id),
primary key(usr_id, friend_usr_id)
)
Требуется:
Получить определенного пользователя со списком друзей.
Решение тривиально:
select u.id, array(select f.friend_usr_id from friend f where f.usr_id=u.id) as friends
from usr u
Так как эта операция требуется достаточно часто, создаем для нее представление:
create view v_usr_with_friends as
select u.id, array(select f.friend_usr_id from friend f where f.usr_id=u.id) as friends
from usr u
Все хорошо, но появилось новое требование: получить пользователя со списком друзей, которые одновременно являются друзьями другого пользователя (например, просматривающего):
select u.id,
array(select f.friend_usr_id
from friend f
where f.usr_id=u.id
and exists(select * from usr_friend f2 where f2.usr_id=another_usr_id and f2.friend_usr_id=f.friend_usr_id)
) as friends
from usr u
К сожалению, создать представление на основе этого запроса невозможно — передать идентификатор второго пользователя как параметр нельзя; но есть возможность обойти это ограничение с помощью декартова произведения:
create or replace view usr_with_common_friends as
select u2.id as another_usr_id,
u.id,
array(select f.friend_usr_id
from friend f
where f.usr_id=u.id
and exists(select * from friend f2 where f2.usr_id=u2.id and f2.friend_usr_id=f.friend_usr_id)
) as friends
from usr u, usr u2
Использование получившегося представления совершенно естественно:
select * from usr_with_common_friends where id=1 and another_usr_id=2
Поступает новое требование: требуется получать не просто общих друзей, но общих друзей, зарегистрировавшихся в указанный промежуток времени. Так как создать таблицу со всеми возможными временными промежутками не представляется возможным, то придется создать функцию:
create or replace function usr_with_common_friends_created_at_tr(tr tstzrange)
returns table(another_usr_id int, id int, friends int[]) as
$code$
select u2.id as another_usr_id,
u.id,
array(select f.friend_usr_id
from friend f, usr u3
where f.usr_id=u.id
and exists(select * from friend f2 where f2.usr_id=u2.id and f2.friend_usr_id=f.friend_usr_id)
and u3.id=f.friend_usr_id
and u3.added <@ tr
) as friends
from usr u, usr u2;
$code$
language sql
**stable**
Использование тоже достаточно удобно:
select * from usr_with_common_friends_created_at_tr(tstzrange(now() - make_interval(years:=1), now())) where id=1 and another_usr_id=2
Казалось бы, запрос, использующий эту фунцию, будет работать незамысловато — сначала функция вернет все возможные строки, а потом они будут отфильтрованы по условию. Давайте посмотрим:
explain
select * from usr_with_common_friends_created_at_tr(tstzrange(now() - make_interval(years:=1), now())) where id=1 and another_usr_id=2
План:
QUERY PLAN
Nested Loop (cost=0.30..67.17 rows=1 width=8)
-> Index Only Scan using usr_pkey on usr u (cost=0.15..8.17 rows=1 width=4)
Index Cond: (id = 1)
-> Index Only Scan using usr_pkey on usr u2 (cost=0.15..8.17 rows=1 width=4)
Index Cond: (id = 2)
SubPlan 1
-> Nested Loop (cost=19.44..50.82 rows=1 width=4)
-> Hash Join (cost=19.29..30.01 rows=6 width=8)
Hash Cond: (f.friend_usr_id = f2.friend_usr_id)
-> Bitmap Heap Scan on friend f (cost=4.24..14.91 rows=11 width=4)
Recheck Cond: (usr_id = u.id)
-> Bitmap Index Scan on friend_pkey (cost=0.00..4.24 rows=11 width=0)
Index Cond: (usr_id = u.id)
-> Hash (cost=14.91..14.91 rows=11 width=4)
-> Bitmap Heap Scan on friend f2 (cost=4.24..14.91 rows=11 width=4)
Recheck Cond: (usr_id = u2.id)
-> Bitmap Index Scan on friend_pkey (cost=0.00..4.24 rows=11 width=0)
Index Cond: (usr_id = u2.id)
-> Index Scan using usr_pkey on usr u3 (cost=0.15..3.46 rows=1 width=4)
Index Cond: (id = f.friend_usr_id)
Filter: (added <@ tstzrange((now() - '1 year'::interval), now()))
Удивительно, но это не так — сервер сумел развернуть функцию непосредственно в тело запроса. Да, Postgresql в ряде случаев умеет внедрять тело функции непосредственно в запрос.
В каких случаях это происходит?
Скалярные функции:
- Функция реализована на SQL (
LANGUAGE SQL
) как простойselect
, возвращающий скалярный тип - Функция помечена как
immutable
илиstable
- Функция не содержит подзапросов
- Функция не помечена как security definer
- У функции нет специфических
set
(н.,set enable_seqscan=off
и т.п.) - Функция возвращает только одну колонку
- Возвращаемый тип должен совпадать с типом функции
- И еще ряд ограничений (полный список см. по ссылке ниже)
Это может пригодиться для инкапсуляции несложной, но громоздкой логики, например:
create or replace function is_system_catalog_table_name(r anyelement) returns boolean as
$code$
select substring(r.relname from 1 for 3)='pg_' -- выражение, конечно, может быть куда более замысловатым
$code$
immutable
language sql
Запрос:
explain analyze
select * from pg_class pc where is_system_catalog_table_name(pc)
План:
Seq Scan on pg_class pc (cost=0.00..6955.59 rows=827 width=201)
Filter: ("substring"((relname)::text, 1, 3) = 'pg_'::text)
Как видно, никакого вызова функции тут нет — код функции вставился непосредственно в тело запроса. Эту функцию можно рассматривать как своеобразный макрос.
Хотелось бы заодно обратить внимание на компактный синтаксис записи вызова функции — в качестве параметра передается сразу запись, причем принимается не как строго определенный тип (pg_class
в данном случае), а как произвольный тип с колонкой relname
.
Например:
create table t(id serial, relname text);
insert into t(relname) select relname from pg_class limit 100;
explain
select * from t pc where is_system_catalog_table_name(pc)
Seq Scan on t pc (cost=0.00..2.50 rows=1 width=25)
Filter: ("substring"(relname, 1, 3) = 'pg_'::text)
У табличных функций похожие, но значительно более мягкие ограничения:
- Функция реализована на SQL (
LANGUAGE SQL
) - Функция
immutable
илиstable
- Функция не
security definer
- Функция не
strict
- Нет специфических set
- Тело функции содержит единственный
select
(и толькоselect
,insert
/update
/delete
не допускаются) - Типы возвращаемых колонок должны соответствовать типам в объявлении функции
- И еще ряд достаточно специфичных ограничений
Таким образом, реализованное в Postgres встраивание тела функции непосредственно в запрос дает возможность эффективно реализовать отсутствующую в стандарте, но тем не менее востребованную и удобную конструкцию "представление с параметрами".
Интересно, что в DB2 и SQL Server для решения задачи "представление с параметрами" также используются функции, встраиваемые в запрос.
Ссылки:
https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions
https://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000935.html