在Postgresql中创建一个不返回合成值的函数 [英] Creating a function in Postgresql that does not return composite values
问题描述
我正在学习如何在Postgresql中编写函数。我定义了一个名为 _tmp_myfunction()
的函数,它接受一个 id
并返回一个表格(我也定义了一个表对象类型称为 _tmp_mytable
)
- 创建对象类型为返回
CREATE TYPE _tmp_mytable AS(
id整数,
开销双精度
);
- 创建函数返回查询
创建或替换函数_tmp_myfunction(
id整数
)
RETURNS SETOF _tmp_mytable AS $$
BEGIN
RETURN QUERY
SELECT
sales.gid,
cost
FROM
sales
WHERE
id = sales.gid;
END;
$$语言plpgsql;
当我使用一个 id
并使用以下方法调用它:
SELECT * FROM _tmp_myfunction(402);
我想要做的就是调用它,但要使用一列值而不是一个值。但是,如果我使用以下方法,那么我最终将在一列中使用逗号分隔表中的所有值:
- - 使用列中的所有值调用函数
SELECT _tmp_myfunction(t.id)
FROM transactions as t;
SELECT _tmp_myfunction(402);
而不是
SELECT * FROM _tmp_myfunction(402);
但我不知道如何构造我的函数,使得我没有得到合成值当我传入一列值时。
您可以编写类似这样的内容:
SELECT(t2.function_row).id,
(t2.function_row).cost
FROM(SELECT _tmp_myfunction(t.id)as function_row
FROM transactions t)t2;
它会为您提供字段,而不是复合行。
I'm learning how to write functions in Postgresql. I've defined a function called _tmp_myfunction()
which takes in an id
and returns a table (I also define a table object type called _tmp_mytable
)
-- create object type to be returned
CREATE TYPE _tmp_mytable AS (
id integer,
cost double precision
);
-- create function which returns query
CREATE OR REPLACE FUNCTION _tmp_myfunction(
id integer
)
RETURNS SETOF _tmp_mytable AS $$
BEGIN
RETURN QUERY
SELECT
sales.gid,
cost
FROM
sales
WHERE
id = sales.gid;
END;
$$ LANGUAGE plpgsql;
This works fine when I use one id
and call it using the following approach:
SELECT * FROM _tmp_myfunction(402);
What I would like to be able to do is to call it, but to use a column of values instead of just one value. However, if I use the following approach I end up with all values of the table in one column, separated by commas:
-- call function using all values in a column
SELECT _tmp_myfunction(t.id)
FROM transactions as t;
I understand that I can get the same result if I use SELECT _tmp_myfunction(402);
instead of SELECT * FROM _tmp_myfunction(402);
but I don't know how to construct my function in such a way that I do not get composite values when I pass in a column of values.
You can write something like that:
SELECT (t2.function_row).id,
(t2.function_row).cost
FROM (SELECT _tmp_myfunction(t.id) as function_row
FROM transactions t ) t2;
It will give you the fields, instead of composite rows.
这篇关于在Postgresql中创建一个不返回合成值的函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!