首页 文章

将“WHERE”参数传递给PostgreSQL View？

提问于
浏览 次
37

我通过一系列嵌套子查询对我的PostgreSQL数据库进行了相当复杂的查询，该数据库跨越4个表 . 然而，尽管外观和设置略显棘手，但最终它将返回两列（来自同一个表，如果这有助于这种情况）基于两个外部参数的匹配（两个字符串需要与不同表中的字段匹配） . 我对PostgreSQL中的数据库设计还是比较陌生的，所以我知道这个看似神奇的东西叫做Views，这似乎可以帮助我，但也许不是 .

有没有什么方法可以在视图中移动我的复杂查询，并以某种方式只传递我需要匹配的两个值？这将大大简化我在前端的代码（通过将复杂性转移到数据库结构） . 我可以创建一个包装我的静态示例查询的视图，它可以正常工作，但是只适用于一对字符串值 . 我需要能够使用各种不同的值 .

因此我的问题是：是否可以将参数传递给静态视图并使其变为“动态”？或者View可能不是接近它的正确方法 . 如果还有其他更好的东西，我全都耳朵！

*编辑：*根据评论中的要求，这是我现在的查询：

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;

参数由上面的百分比符号设置 .

sql postgresql stored-procedures view

5 回答

  • 20

    您可以使用set return函数：

    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)
    回复于
  • 48

    除了@a_horse已经清除的内容之外，您还可以使用JOIN syntax而不是嵌套子查询来简化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开始，您可以在SQL函数中使用声明的参数名来代替 $1 和 $2 . （很长一段时间都可以用于PL / pgSQL函数） .

    • 必须注意避免命名冲突 . 这就是为什么我习惯在声明中为参数名称添加前缀（这些参数在函数内部最为可见）和表格限定正文中的列名称 .

    • 我将 WHERE p.active = 'TRUE' 简化为 WHERE p.active ，因为 active 列最有可能是 boolean ，而不是 text .

    • USING 仅在JOIN左侧的所有表中的列名明确无误时才有效 . 否则，您必须使用更明确的语法：
      ON l.param_id = p.param_id

    回复于
  • 0

    在大多数情况下，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]
    回复于
  • 2

    我不认为你所说的“动态”观点是可能的 .

    为什么不编写一个带有2个参数的存储过程呢？

    回复于
  • 25

    我会将查询重新改写如下：

    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;

    假设您在各个id列上有索引，此查询应该比使用IN运算符快得多;这里的exists参数只使用索引值，甚至不触及数据表，除了从参数表中获取最终数据 .

    回复于

相关问题