sql - 通过 FROM 中使用的表名自动运行？

Question

使用 PostgreSQL 9.6.3。我不熟悉数据库中的函数。

假设有多个商品编号表。每一个都有项目编号、项目成本和其他几个列，这些列被计入“额外成本”。我想将计算放入一个函数中，以便我可以为这些表中的任何一个调用它。

所以代替:

SELECT 
    itemnumber, 
    itemname, 
    base, 
    CASE 
        WHEN labor < 100 AND overhead < .20 THEN
        WHEN .....
        WHEN .....
        WHEN .....
        .....
    END AS add_cost,
    gpm
FROM items1;

我可以做到:

SELECT 
    itemnumber, 
    itemname, 
    base, 
    calc_add_cost(),
    gpm
FROM items1;

如果我希望能够在任何 item 表上使用它，我想我需要设置一个 table_name 参数，该函数在添加至少可以说，函数中的表名是不可取的。

calc_add_cost(items1)

但是，有没有一种更简单的方法，当我调用 calc_add_cost() 时，它只会使用 FROM 子句中的表名？

SELECT ....., calc_add_cost(item1) FROM item1

只是看起来多余。

我确实遇到过一些主题，这些主题的标题听起来像是解决了我希望完成的问题，但在回顾它们时，它们看起来像是一个不同的问题。

Answer 1

您甚至可以按照您的想法模拟“计算字段”或“生成列”。这里的基础知识:

• Store common query as column?

一个表的简单演示:

CREATE OR REPLACE FUNCTION add_cost(items1)  -- function name = default col name
  RETURNS numeric AS
$func$
SELECT 
    CASE 
        WHEN $1.labor < 100 AND $1.overhead < .20 THEN numeric '1'
--      WHEN .....
--      WHEN .....
--      WHEN .....
        ELSE numeric '0'  -- ?
    END;
$func$
  LANGUAGE sql IMMUTABLE;

调用:

SELECT *, t.add_cost FROM items1 t;

注意 t.add_cost 中的表限定条件。我只演示这种语法变体，因为您一直在要求它。我的建议是使用不太容易混淆的标准语法:

SELECT *, add_cost(t) AS add_cost FROM items1 t;  -- column alias is also optional

但是，SQL 是一种严格类型化的语言。如果您将特定行类型定义为输入参数，它将绑定(bind)到该特定行类型。传递各种整表类型 更为复杂，但对于多态 输入类型仍然可行。

CREATE OR REPLACE FUNCTION add_cost(ANYELEMENT)  -- function name = default col name
  RETURNS numeric AS
$func$
SELECT 
    CASE 
        WHEN $1.labor < 100 AND $1.overhead < .20 THEN numeric '1'
--      WHEN .....
--      WHEN .....
--      WHEN .....
        ELSE numeric '0'  -- ?
    END;
$func$
  LANGUAGE sql IMMUTABLE;

对具有匹配数据类型的 labor 和 overhead 列的任何表进行相同的调用。

dbfiddle here

另请参阅此处传递简单值的相关简单案例:

• How to put part of a SELECT statement into a Postgres function

对于更复杂的要求——比如还返回各种行类型——请参阅:

• Refactor a PL/pgSQL function to return the output of various SELECT queries