是否将“WHERE”参数传递到PostgreSQL视图？

cidc1ykv 于 2023-03-08 发布在 PostgreSQL

关注(0)|答案(5)|浏览(288)

我在PostgreSQL数据库上有一个相当复杂的查询，通过一系列嵌套的子查询跨越4个表。然而，尽管外观和设置看起来有点棘手，但最终它将返回两列（来自同一个表，如果这对情况有帮助的话）基于两个外部参数的匹配（两个字符串需要与不同表中的字段匹配）。我对PostgreSQL的数据库设计相当陌生，所以我知道这个看似神奇的东西叫做视图，这似乎可以帮助我在这里，但也许不是。
有没有什么方法可以将复杂的查询移到视图中，并以某种方式将需要匹配的两个值传递给它？这将大大简化前端的代码（通过将复杂性转移到数据库结构中）。我可以创建一个视图来 Package 我的静态示例查询，而且效果很好，然而，这只适用于一对字符串值。我需要能够将它与各种不同的值一起使用。
因此，我的问题是：有没有可能把参数传递到一个静态的视图中，然后让它变成“动态的”？或者视图不是处理它的正确方法。如果有其他更好的方法，我洗耳恭听！

**编辑：**根据评论中的要求，以下是我现在的问题：

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;

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

postgresql

来源：https://stackoverflow.com/questions/11401749/pass-in-where-parameters-to-postgresql-view

5条答案

按热度按时间

wmomyfyw1#

可以使用集合返回函数：

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)

赞(0）回复(0）举报 2023-03-08

enyaitl32#

除了@a_horse已经解决的问题之外，您还可以使用JOIN语法而不是嵌套子查询来简化SQL语句，性能将是相似的，但语法更短，更易于管理。

CREATE OR REPLACE FUNCTION param_labels(_region_label text, _model_label text)
  RETURNS TABLE (param_label text, param_graphics_label text)
  LANGUAGE sql 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$;

如果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。
JOIN条件中的USING仅当左侧所有表的列名都明确时才有效。否则，请使用更显式的语法：ON l.param_id = p.param_id

赞(0）回复(0）举报 2023-03-08

kqlmhetl3#

在大多数情况下，返回集合的函数是可行的，但是如果你想同时读写集合，视图可能更合适，而且视图读取会话参数 * 是 * 可能的：

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]

赞(0）回复(0）举报 2023-03-08

ncgqoxb04#

我不认为你所说的“动态”观点是可能的。
为什么不写一个带两个参数的存储过程呢？

赞(0）回复(0）举报 2023-03-08

oxcyiej75#

我将把这个问题改写如下：

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操作符快得多;除了从参数表中获取最终数据之外，这里的现有参数将仅使用索引值，甚至不接触数据表。

赞(0）回复(0）举报 2023-03-08

我来回答

是否将“WHERE”参数传递到PostgreSQL视图？

5条答案

相关问题

热门标签

最新问答