将FROM中使用的表名自动传递给函数？

Working with PostgreSQL 9.6.3. I am new to functions in databases.

使用PostgreSQL 9.6.3。我是数据库中的新功能。

Let's say there are multiple tables of item numbers. Each one has the item number, the item cost and several other columns which are factored into the "additional cost". I would like to put the calculation into a function so I can call it for any of these tables.

假设有多个项目编号表。每个都有项目编号,项目成本和其他几个列,这些列被计入“附加成本”中。我想把计算放到一个函数中,所以我可以为任何这些表调用它。

So instead of:

所以代替:

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

I can just do:

我可以这样做:

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

If I want to be able to use it on any of the item tables, I guess I would need to set a table_name parameter that the function takes since adding the table name into the function would be undesirable to say the least.

如果我希望能够在任何项目表上使用它,我想我需要设置一个table_name参数,该函数采用该函数,因为将表名添加到函数中是不可取的。

calc_add_cost(items1)

However, is there a simpler way such that when I call calc_add_cost() it will just use the table name from the FROM clause?

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

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

Just seems redundant.

看起来多余。

I did come across a few topics with titles that sounded like they addressed what I was hoping to accomplish, but upon reviewing them it looked like they were a different issue.

我确实遇到过一些主题,其中的标题听起来像是我们希望完成的内容,但在审核它们时,看起来它们是一个不同的问题。

1 个解决方案

#1

You can even emulate a "computed field" or "generated column" like you had in mind. Basics here:

您甚至可以像想象的那样模拟“计算字段”或“生成列”。基础知识:

Store common query as column?

将常见查询存储为列?

Simple demo for one table:

一个表的简单演示:

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;

Call:

SELECT *, t.add_cost FROM items1 t;

Note the table-qualification in t.add_cost. I only demonstrate this syntax variant since you have been asking for it. My advise is to use the less confusing standard syntax:

请注意t.add_cost中的table-qualification。我只是证明了这种语法变体,因为你一直在要求它。我的建议是使用较少混淆的标准语法:

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

However, SQL is a strictly typed language. If you define a particular row type as input parameter, it is bound to this particular row type. Passing various whole table types is more sophisticated, but still possible with polymorphic input type.

但是,SQL是一种严格类型的语言。如果将特定行类型定义为输入参数,则它将绑定到此特定行类型。传递各种整个表类型更复杂,但仍然可以使用多态输入类型。

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;

Same call for any table that has the columns labor and overhead with matching data type.

对具有匹配数据类型的列人工和开销的任何表的相同调用。

dbfiddle here

Also see the related simple case passing simple values here:

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

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

如何将SELECT语句的一部分放入Postgres函数中

For even more complex requirements - like also returning various row types - see:

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

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

重构PL / pgSQL函数以返回各种SELECT查询的输出

#1