sql - 在全局表中存储 WHERE 子句

Question

我运行这个查询 50 多次，我想抽象下面的 AND 查询语句并将它们存储在一个/全局表中，所以将来我只需要编辑一个表(相对于 50 个)如果我想要编辑任何 AND 语句。将 AND 语句存储在单独的表中然后在下面的查询中调用它们的最有效方法是什么？

SELECT  
    Field,
    Field2,
    Field3
    into table1
FROM    table2
WHERE (DESCRIPTION iLIKE '%ADVANCE%AUTO%Pa%')

AND is_duplicate!=1
AND amount >0 
AND currency_id = 152 
AND transaction_base_type = 'debit' 
AND TRANSACTION_STATUS <> 'D'

Answer 1

您可以创建 View /materialized view :

CREATE VIEW my_view
AS
SELECT  
    Field,
    Field2,
    Field3,
    DESCRIPTION
FROM    table2
WHERE  is_duplicate!=1
    AND amount >0 
    AND currency_id = 152 
    AND transaction_base_type = 'debit' 
    AND TRANSACTION_STATUS <> 'D'

然后:

SELECT Field, Field2, Field3
FROM my_view
WHERE (DESCRIPTION iLIKE '%ADVANCE%AUTO%Pa%')

---

编辑

I simply need to store WHERE clauses in one place so I can update them once and call them in 50 queries vs. including them in every query and updating them 50 times. Is it really that complicated?

正如我在评论中所写，您不能简单地参数化表名(加上它可能表示 schema is flawed )。 SQL 是一种强大的语言，因此您可以使用 dynamic SQL和功能。

CREATE OR REPLACE FUNCTION my_func(tab_name text)
RETURNS TABLE (
   id INT,     -- here goes common column list shared across all 50 tables
   col1 INT,
   col2 INT
 )
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN 
    RETURN QUERY 
    EXECUTE format('SELECT * from %I where col2 > 0',tab_name);
                                     -- here goes shared conditions
END 
$BODY$;

SELECT * FROM my_func('tab1');
SELECT * FROM my_func('tab2') WHERE col2 = 2;
                              -- condition that is not shared

db<>fiddle demo