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

I have part of a SELECT statement that is a pretty lengthy set of conditional statements. I want to put it into a function so I can call it much more efficiently on any table I need to use it on.

我有一个SELECT语句的一部分，这是一组很长的条件语句。我想把它放到一个函数中这样我就可以更有效地调用任何需要用到它的表。

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;

Is it possible to add part of a SELECT to a function so that can be injected just by calling the function?

是否可以将SELECT的一部分添加到函数中，以便仅通过调用函数就可以将其注入?

I am sorting through documentation and Google, and it seems like it might be possible if creating the function in the plpgsql language as opposed to sql. However, what I am reading isn't very clear.

我正在整理文档和谷歌，如果在plpgsql语言中创建函数而不是sql，这似乎是可能的。然而，我所读的内容并不十分清楚。

1 个解决方案

#1

You cannot just wrap any part of a SELECT statement into a function. But an expression like your CASE can easily be wrapped:

不能将SELECT语句的任何部分封装到函数中。但是像你的案子这样的表达方式很容易被包装:

CREATE OR REPLACE FUNCTION  pg_temp.calc_add_cost(_labor integer, _overhead numeric)
  RETURNS numeric AS
$func$
SELECT 
    CASE 
        WHEN _labor < 100 AND _overhead < .20 THEN numeric '1'  -- example value
--      WHEN .....
--      WHEN .....
--      WHEN .....
        ELSE numeric '0'  -- example value
    END;
$func$
  LANGUAGE sql IMMUTABLE;

While you could also use PL/pgSQL for this, the demo is a simple SQL function. See:

虽然您也可以为此使用PL/pgSQL，但是演示是一个简单的SQL函数。看到的:

Difference between language sql and language plpgsql in PostgreSQL functions
语言sql和语言plpgsql在PostgreSQL函数中的区别

Adapt input and output data types to your need. Just guessing integer and numeric for lack of information.

根据需要调整输入和输出数据类型。只是猜测整数和数字，因为缺少信息。

Call:

电话:

SELECT calc_add_cost(1, 0.1);

In your statement:

在你的声明:

SELECT 
    itemnumber, 
    itemname, 
    base, 
    calc_add_cost(labor, overhead) AS add_cost,  -- pass column values as input
    gpm
FROM items1;

You should understand some basics about Postgres functions to make proper use. Might start with the manual page on CREATE FUNCTION.

您应该了解一些有关Postgres函数的基本知识，以便正确使用。可以从创建函数的手册页开始。

There are also many related questions & answers here on SO.

这里也有很多相关的问题和答案。

Also see the related, more sophisticated case passing whole rows here:

也可以看到相关的，更复杂的情况通过整行:

Pass the table name used in FROM to function automatically in PostgreSQL 9.6.3
将在PostgreSQL 9.6.3中使用的表名自动传递给函数

#1