Часто видно жалобы на то, что параметры "не работают". Как же они не работают?
А вот так:
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 строк) Как видно, потери в производительности есть, но, в общем, не слишком существенные.
ссылка на оригинал статьи https://habrahabr.ru/post/278019/
Добавить комментарий