Таблица как параметр в Postgresql
Часто видно жалобы на то, что параметры «не работают». Как же они не работают?
А вот так:
select * from $1 where ...;
И правда не работают — таблица должна быть известна серверу на момент подготовки запроса. Так что же — выходит, совсем никак невозможно передать таблицу как параметр? А если не как параметр? А если у меня в одной таблице значениями колонок являются другие таблицы — что делать? Не все так страшно — можно использовать функции. В самом деле, давайте создадим простую и незатейливую функцию, которая выполнит запрос и вернет нам результат:
create or replace function doSelect(query text) returns setof record as
$code$
begin
return query execute query;
end;
$code$
language plpgsql
В данном случае мы передаем не имя таблицы, а сразу запрос. И правда, нам могут понадобиться не все строки из нее, а только часть — так почему бы не заставить сервер сразу отобрать все нужные нам строки? Действительно, при выборке из функции сервер будет вынужден фильтровать все возвращаемые значения, отбирая только нужные, а в случае запроса он может воспользоваться, например, индексами, не говоря уже о том, что запрос может быть сложнее, чем просто выборка всего из таблицы.
Проверим:
work=# select * from doSelect('select relname::text from pg_class') as ds(name text) where name='aa';
name
------
aa
(1 строка)
Работает.
В принципе можно сделать еще и работу с параметрами:
create or replace function doSelect(query text, p1 text) returns setof record as
$code$
begin
return query execute query using p1;
end;
$code$
language plpgsql;
create or replace function doSelect(query text, p1 text, p2 text) returns setof record as
$code$
begin
return query execute query using p1, p2;
end;
$code$
language plpgsql;
… и так далее.
Несмотря на некоторую неуклюжесть (в execute…using нельзя передать массив параметров; массивы — это наборы элементов одного типа, а параметры, вообще говоря, могут иметь разный) это все прекрасно работает:
work=# select *
from doSelect(format('select table_catalog::text,
table_schema::text,
table_name::text
from %s
where table_schema=$1 limit $2::bigint',
'information_schema.tables'), 'public',1::text)
as i(table_catalog text, table_schema text, table_name text);
table_catalog | table_schema | table_name
---------------+--------------+------------
work | public | aa
(1 строка)
Чтобы не создавать множество однотипных функций можно просто создать функцию с переменным числом параметров:
create or replace function doSelect(query text, variadic param text[]) returns setof record as
$code$
begin
return query execute query using param;
end;
$code$
language plpgsql
Правда, при формировании строки запроса придется использовать не вполне удобный синтаксис — $1[N], где N — номер переданного параметра. Так, для запроса выше получается:
work=# select *
from doSelect(format('select table_catalog::text,
table_schema::text,
table_name::text
from %s
where table_schema=$1[1] limit $1[2]::bigint',
'information_schema.tables'), 'public',1::text)
as i(table_catalog text, table_schema text, table_name text);
table_catalog | table_schema | table_name
---------------+--------------+------------
work | public | aa
(1 строка)
Суть проблемы заключается в том, что в Postgres невозможно иметь массивы разнотипных элементов — и в функциях выше все приводится, как видно, к типу text, отчего в теле запроса требуются явные приведения к требуемым типам (любопытно, кстати, что limit требует тип bigint). Тем не менее это все вполне работоспособно. Что самое интересное, эти функции можно использовать не только с параметрами, передаваемыми из приложения — их можно использовать и с колонками из другой таблицы, например:
work=# select table_name, cnt.cnt
from information_schema.tables t,
doSelect(format('select count(*) from %s', t.table_name)) as cnt(cnt bigint)
where table_schema='public';
table_name | cnt
---------------+----------
aa | 10000000
ttq | 3
report | 12
colltest | 100000
t2 | 100000
tpair | 10000
call | 0
XXXXXXX_locks | 1
t | 2
sbr | 273370
stest | 954
house | 21000
ttn | 1000000
addrobj | 21000
tt1 | 1
tt2 | 1
ttt | 100000
tt | 1
t1 | 10000
(19 строк)
Хотелось бы обратить внимание на то, что мы ссылаемся в вызове функции на колонку из таблицы, расположенной левее в перечислении таблиц во from.
Так что таблицу как параметр использовать вполне можно; стоит, правда, обратить внимание на то, что во время выполнения запроса для каждого выполнения функции будет строиться отдельный план для динамического запроса, что, разумеется, требует определенных ресурсов, хотя, с другой стороны, часто может оказаться вполне желательным побочным эффектом.
Насколько просядет производительность при таком подоходе? Как ни странно, по крайней мере в простых случаях весьма незначительно:
create table tableasparameter as select n from generate_series(1,1000) as gs(n);
work=# explain analyze
select tt.* from generate_series(1,10000), tableasparameter tt;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..12526.50 rows=1000000 width=4) (actual time=1.919..1347.240 rows=10000000 loops=1)
-> Function Scan on generate_series (cost=0.00..10.00 rows=1000 width=0) (actual time=1.896..2.508 rows=10000 loops=1)
-> Materialize (cost=0.00..19.00 rows=1000 width=4) (actual time=0.000..0.042 rows=1000 loops=10000)
-> Seq Scan on tableasparameter tt (cost=0.00..14.00 rows=1000 width=4) (actual time=0.017..0.219 rows=1000 loops=1)
Planning time: 0.068 ms
Execution time: 1648.586 ms
(6 строк)
work=# explain analyze
work-# select tt.* from generate_series(1,10000), doSelect('select * from tableasparameter') as tt(val int);
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.25..20010.25 rows=1000000 width=4) (actual time=1.294..1401.768 rows=10000000 loops=1)
-> Function Scan on generate_series (cost=0.00..10.00 rows=1000 width=0) (actual time=1.033..1.590 rows=10000 loops=1)
-> Function Scan on doselect tt (cost=0.25..10.25 rows=1000 width=4) (actual time=0.000..0.047 rows=1000 loops=10000)
Planning time: 0.039 ms
Execution time: 1705.056 ms
(5 строк)
Как видно, потери в производительности есть, но, в общем, не слишком существенные.
