Таблица как параметр в 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 строк)

Как видно, потери в производительности есть, но, в общем, не слишком существенные.