如何将一部分SELECT语句放入Postgres函数中 [英] How to put part of a SELECT statement into a Postgres function
问题描述
我有一个 SELECT
语句的一部分,它是一组相当冗长的条件语句。我想把它放到一个函数中,这样我就可以在任何需要使用它的表上更高效地调用它。
所以不是:
SELECT
itemnumber,
itemname,
base,
CASE
当劳动< ; 100 AND开销< .20 THEN
当.....
当..... .....
当..... .....
.....
END as add_cost,
gpm
FROM items1;
我可以这样做:
<$ p $选择
itemnumber,
itemname,
base,
calc_add_cost(),
gpm
FROM items1;
是否可以添加 SELECT的一部分
到一个函数,以便可以通过调用函数注入?
我通过文档和Google进行排序,并且似乎可能创建函数在 plpgsql
语言中,而不是 sql
。然而,我所读到的内容并不是很清楚。
你不能只包装任何部件将 SELECT
语句添加到函数中。但是像 CASE
这样的表达式可以很容易地被打包:
CREATE OR REPLACE FUNCTION pg_temp.calc_add_cost(_labor integer,_overhead numeric)
RETURNS数字AS
$ func $
SELECT
CASE
当_labor< 100和_overhead< .20那么数字'1' - 示例值
- 当.....
- 当.....
- 当.....
ELSE数字'0' - 示例值
END;
$ func $
LANGUAGE sql IMMUTABLE;
虽然您也可以使用PL / pgSQL来做这个演示,一个简单的SQL函数。请参阅:
语言 - plpgsql-in-postgresql-functions / 24771561#24771561
根据需要调整输入和输出数据类型。只是猜测整数
和数字
缺乏信息。
调用:
SELECT calc_add_cost(1,0.1);
在您的声明中:
SELECT
itemnumber,
itemname,
base,
calc_add_cost(人工,开销)AS add_cost, - 将列值作为输入
gpm
FROM items1;
您应该了解一些有关Postgres函数的基本知识以便正确使用。可能从 CREATE FUNCTION
。
还有许多 相关问题&在这里回答SO。
另请参阅相关的更复杂的案例,在这里传递整行:
< li>< 3/46166048#46166048>传递FROM中使用的表名以在PostgreSQL 9.6.3中自动运行
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.
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?
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.
You cannot just wrap any part of a SELECT
statement into a function. But an expression like your CASE
can easily be wrapped:
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:
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
.
There are also many related questions & answers here on SO.
Also see the related, more sophisticated case passing whole rows here:
这篇关于如何将一部分SELECT语句放入Postgres函数中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!