Использование функций в PostgreSQL как параметризированных представлений16.02.2016 17:03

В ежедневной работе часто встает задача ясно и просто ссылаться на большие списки колонок и выражений в выборке, и/или обходиться с громоздкими и неясными условиями в предложении 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 в ряде случаев умеет внедрять тело функции непосредственно в запрос.
В каких случаях это происходит?

Скалярные функции:

  1. Функция реализована на SQL (LANGUAGE SQL) как простой select, возвращающий скалярный тип
  2. Функция помечена как immutable или stable
  3. Функция не содержит подзапросов
  4. Функция не помечена как security definer
  5. У функции нет специфических set (н., set enable_seqscan=off и т.п.)
  6. Функция возвращает только одну колонку
  7. Возвращаемый тип должен совпадать с типом функции
  8. И еще ряд ограничений (полный список см. по ссылке ниже)


Это может пригодиться для инкапсуляции несложной, но громоздкой логики, например: 

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)


У табличных функций похожие, но значительно более мягкие ограничения:

  1. Функция реализована на SQL (LANGUAGE SQL)
  2. Функция immutable или stable
  3. Функция не security definer
  4. Функция не strict
  5. Нет специфических set
  6. Тело функции содержит единственный select (и толькоselect,insert/update/delete` не допускаются)
  7. Типы возвращаемых колонок должны соответствовать типам в объявлении функции
  8. И еще ряд достаточно специфичных ограничений


Таким образом, реализованное в 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

© Habrahabr.ru