Передать параметры "WHERE" в PostgreSQL View?

У меня довольно сложный запрос в моей базе данных PostgreSQL, охватывающий 4 таблицы через ряд вложенных подзапросов. Однако, несмотря на слегка сложный внешний вид и настройку, в конечном итоге он вернет два столбца (из той же таблицы, если это поможет ситуации) на основе соответствия двух внешних параметров (две строки должны совпадать с полями в разных таблицах). Я новичок в дизайне базы данных в PostgreSQL, поэтому я знаю, что эта, казалось бы, волшебная вещь, которая называется Views, существует, и похоже, что она может помочь мне здесь, но, возможно, нет.

Есть ли способ переместить мой сложный запрос внутри представления и каким-то образом передать ему два значения, которые мне нужно сопоставить? Это значительно упростило бы мой код на интерфейсе (путем смещения сложностей с структурой базы данных). Я могу создать представление, которое обертывает мой статический пример запроса, и это работает отлично, однако это работает только для одной пары строковых значений. Мне нужно иметь возможность использовать его с различными значениями.

Таким образом, мой вопрос: возможно ли передавать параметры в статический вид, который статический, и стать ли он "динамическим"? Или, возможно, представление - это не правильный способ приблизиться к нему. Если есть что-то еще, что будет работать лучше, я все уши!

* Изменить: * В соответствии с запросом в комментариях, здесь мой запрос в том виде, в котором он находится сейчас:

SELECT   param_label, param_graphics_label
  FROM   parameters
 WHERE   param_id IN 
         (SELECT param_id 
            FROM parameter_links
           WHERE region_id = 
                 (SELECT region_id
                    FROM regions
                   WHERE region_label = '%PARAMETER 1%' AND model_id =
                         (SELECT model_id FROM models WHERE model_label = '%PARAMETER 2%')
                 )
         ) AND active = 'TRUE'
ORDER BY param_graphics_label;

Параметры устанавливаются символами процента выше.

Ответы

Ответ 1

Вы можете использовать функцию возврата набора:

create or replace function label_params(parm1 text, parm2 text)
  returns table (param_label text, param_graphics_label text)
as
$body$
  select ...
  WHERE region_label = $1 
     AND model_id = (SELECT model_id FROM models WHERE model_label = $2)
  ....
$body$
language sql;

Затем вы можете сделать:

select *
from label_params('foo', 'bar')

Btw: вы уверены, что хотите:

AND model_id = (SELECT model_id FROM models WHERE model_label = $2)

если model_label не является уникальным (или первичный ключ), то это в конечном итоге приведет к ошибке. Вероятно, вы хотите:

AND model_id IN (SELECT model_id FROM models WHERE model_label = $2)

Ответ 2

В дополнение к тому, что уже прояснили @a_horse, вы можете упростить свой SQL, используя JOIN синтаксис вместо вложенных подзапросов. Производительность будет аналогичной, но синтаксис намного короче и проще в управлении.

CREATE OR REPLACE FUNCTION param_labels(_region_label text, _model_label text)
  RETURNS TABLE (param_label text, param_graphics_label text) AS
$func$
    SELECT p.param_label, p.param_graphics_label
    FROM   parameters      p 
    JOIN   parameter_links l USING (param_id)
    JOIN   regions         r USING (region_id)
    JOIN   models          m USING (model_id)
    WHERE  p.active
    AND    r.region_label = $1 
    AND    m.model_label = $2
    ORDER  BY p.param_graphics_label;
$func$ LANGUAGE sql;

  • Если model_label не является уникальным или что-то еще в запросе создает повторяющиеся строки, вы можете сделать это SELECT DISTINCT p.param_graphics_label, p.param_label - с подходящим предложением ORDER BY для лучшей производительности. Или используйте предложение GROUP BY.

  • Так как Postgres 9.2 вы можете использовать объявленные имена параметров вместо $1 и $2 в функциях SQL. (Возможно для функций PL/pgSQL в течение длительного времени).

  • Следует соблюдать осторожность, чтобы избежать конфликтов имен. Вот почему я делаю привычкой префикс имен параметров в объявлении (они видны повсюду внутри функции) и имена столбцов таблицы в заголовке.

  • Я упростил WHERE p.active = 'TRUE' до WHERE p.active, потому что столбец active должен, скорее всего, иметь тип boolean, а не text.

  • USING работает только в том случае, если имена столбцов недвусмысленны во всех таблицах слева от JOIN. Иначе вы должны использовать более явный синтаксис:
    ON l.param_id = p.param_id

Ответ 3

В большинстве случаев функция set-returns - это путь, но в случае, если вы хотите как читать, так и записывать в набор, представление может быть более уместным. И можно просмотреть представление параметра сеанса:

CREATE VIEW widget_sb AS SELECT * FROM widget WHERE column = cast(current_setting('mydomain.myparam') as int)

SET mydomain.myparam = 0
select * from widget_sb
[results]

SET mydomain.myparam = 1
select * from widget_sb
[distinct results]

Ответ 4

Я не думаю, что "динамическое" представление, как вы заявили, возможно.

Почему бы не написать хранимую процедуру, которая вместо этого принимает 2 аргумента?

Ответ 5

Я бы перефразировал запрос следующим образом:

SELECT   p.param_label, p.param_graphics_label
  FROM   parameters p
where exists (
    select 1
    from parameter_links pl
    where pl.parameter_id = p.id
    and exists (select 1 from regions r where r.region_id = pl.region_id
) and p.active = 'TRUE'
order by p.param_graphics_label;

Предполагая, что у вас есть индексы в разных столбцах идентификатора, этот запрос должен быть значительно быстрее, чем при использовании оператора IN; существующие параметры здесь будут использовать только значения индекса, даже не касаясь таблицы данных, кроме получения окончательных данных из таблицы параметров.