传递“ WHERE” PostgreSQL视图的参数？ [英] Pass In "WHERE" parameters to PostgreSQL View?

问题描述

我对PostgreSQL数据库有一个相当复杂的查询，它通过一系列嵌套子查询跨越4个表。但是，尽管外观和设置看起来有些棘手，但最终它仍将基于两个外部参数的匹配（两个字符串需要与不同表中的字段匹配）返回两列（如果有帮助，则来自同一表）。我是PostgreSQL数据库设计的新手，所以我知道存在一个看似不可思议的东西，称为Views，这似乎可以帮助我，但也许不行。

I have a rather complicated query on my PostgreSQL database spanning 4 tables via a series of nested subqueries. However, despite the slightly tricky looking appearance and setup, ultimately it will return two columns (from the same table, if that helps the situation) based on that matching of two external parameters (two strings need to match with fields in different tables). I'm fairly new to database design in PostgreSQL, so I know that this seemingly magical thing called Views exist, and that seems like it could help me here, but perhaps not.

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

Is there some way I can move my complex query inside a view and somehow just pass it the two values I need to match? That would greatly simplify my code on the front-end (by shifting the complexities to the database structure). I can create a view that wraps my static example query, and that works just fine, however that only works for one pair of string values. I need to be able to use it with a variety of different values.

因此，我的问题是：是否可以将参数传递到其他静态视图中并将其变为动态？也许视图不是正确的方法。如果还有其他方法可以更好地工作，我会为您所用！

Thus my question is: is it possible to pass parameters into an otherwise static View and have it become "dynamic"? Or perhaps a View is not the right way to approach it. If there's something else that would work better, I'm all ears!

* 编辑：* 根据评论的要求，这是我的查询为现在可以这样：

* * As requested in comments, here's my query as it stands now:

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;

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

Parameters are set off by percent symbols above.

推荐答案

您可以使用返回的集合函数：

You could use a set returning function:

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;

然后您可以执行以下操作：

Then you can do:

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

Btw：您确定要吗：

Btw: are you sure you want:

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

如果 model_label 不是唯一的（或主键），则最终会引发错误。您可能想要：

if model_label is not unique (or the primary key) then this will throw an error eventually. You probably want:

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

这篇关于传递“ WHERE” PostgreSQL视图的参数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！