Передать в поле" где " параметры для представления PostgreSQL?

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



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



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



*Edit: * как и было предложено в комментариях, вот мой запрос в его нынешнем виде: 



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;


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

763   5  

5 ответов:

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

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')

Кстати: вы уверены, что хотите: 

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)
43  
2012-07-09 22:43:35

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

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 работает только в том случае, если имена столбцов однозначны во всех таблицах слева от соединения. В противном случае вам придется использовать более явный синтаксис:
    ON l.param_id = p.param_id

23  
2015-02-09 16:05:20

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

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]
16  
2014-08-12 07:32:42

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

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

2  
2012-07-09 22:40:55

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

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; параметры exists здесь будут использовать только значения индекса, даже не касаясь таблицы данных, за исключением получения окончательных данных из таблицы параметров.

0  
2017-07-17 23:21:56

Comments

    Ничего не найдено.
Click here to cancel reply.