从 Postgresql 函数返回自定义类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2044787/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Returning a Custom Type from a Postgresql function
提问by Robert
I'm trying to return a Custom type from a PostgreSQL function as follows:
我正在尝试从 PostgreSQL 函数返回自定义类型，如下所示：
DROP TYPE IF EXISTS GaugeSummary_GetDateRangeForGauge_Type CASCADE; -- Drop our previous type
CREATE TYPE GaugeSummary_GetDateRangeForGauge_Type AS -- Recreate our type
(
Minimum timestamp without time zone,
Maximum timestamp without time zone
);
CREATE OR REPLACE FUNCTION GaugeSummary_GetDateRangeForGauge
(
GaugeID integer
)
RETURNS GaugeSummary_GetDateRangeForGauge_Type AS
$$
DECLARE
iGaugeID ALIAS FOR ;
oResult GaugeSummary_GetDateRangeForGauge_Type%ROWTYPE;
BEGIN
SELECT INTO oResult
min(ArchivedMinimumTime) as Minimum,
max(TelemeteredMaximumTime) as Maximum
FROM GaugeSummary
WHERE GaugeID = ;
RETURN oResult;
END;
$$ LANGUAGE plpgsql;
SELECT GaugeSummary_GetDateRangeForGauge(2291308);
There are two problems I'm having with this.
我对此有两个问题。
1) - My results come back as a single column as "("1753-01-01 12:00:00","2009-11-11 03:45:00")", where I need them to come back in two columns.
1) - 我的结果作为单列返回“("1753-01-01 12:00:00","2009-11-11 03:45:00")”，我需要他们回来两列。
Solved! - Silly mistake... It should be SELECT * FROM GaugeSummary_GetDateRangeForGauge(123)
解决了！- 愚蠢的错误...应该是 SELECT * FROM GaugeSummary_GetDateRangeForGauge(123)
2) The results are the maximum and minimum values from the whole table - the WHERE constraint isn't being used.
2) 结果是整个表中的最大值和最小值 - 未使用 WHERE 约束。
Example:
例子：
GaugeSummaryID GaugeID ArchivedMinimumTime TelemeteredMaximumTime
80 4491 "2009-03-28 12:00:00" "2009-06-27 12:00:00"
81 4491 "2009-03-28 12:00:00" "2009-06-27 12:00:00"
But a call to the function gives me : "1753-01-01 12:00:00", "2009-11-11 03:45:00"
但是对该函数的调用给了我：“1753-01-01 12:00:00”、“2009-11-11 03:45:00”
Thanks!
谢谢！
Answer for 2:
回答2：
It seems that running this same query inside a "LANGUAGE 'SQL' STABLE;" function works fine:
似乎在“LANGUAGE 'SQL' STABLE;”中运行相同的查询。功能工作正常：
CREATE OR REPLACE FUNCTION GaugeSummary_GetDateRangeForGauge
(
GaugeID integer
)
RETURNS GaugeSummary_GetDateRangeForGauge_Type AS
$$
SELECT min(ArchivedMinimumTime) as Minimum,
max(TelemeteredMaximumTime) as Maximum
FROM GaugeSummary WHERE GaugeID = ;
$$ LANGUAGE 'SQL' STABLE;
However, it would be nice to know why the plpgsql function isn't working correctly....
但是，很高兴知道为什么 plpgsql 函数无法正常工作....
回答by Arthur Thomas
I tried this and I get two columns back when doing
我试过这个，在做的时候我得到了两列
SELECT * GaugeSummary_GetDateRangeForGauge(1);
results:
结果：
aadb=# select * from GaugeSummary_GetDateRangeForGauge(1);
minimum | maximum
----------------------------+----------------------------
2010-01-11 15:14:20.649786 | 2010-01-11 15:14:24.745783
(1 row)
I am using 8.4 and running it in psql. Could you clarify how you are getting your results?
我正在使用 8.4 并在 psql 中运行它。你能澄清一下你是如何得到结果的吗？
As for #2, if you just want the results then remove the min() and max() aggregate functions from your query. Removing those will ensure that the results from those columns will be returned on the row that matches your ID.
至于#2，如果您只想要结果，则从查询中删除 min() 和 max() 聚合函数。删除这些将确保这些列的结果将在与您的 ID 匹配的行上返回。
UPDATE: ok I am not sure whats going on then. I just put all the similar stuff into my test DB and its working as I expect it to.
更新：好的，我不确定发生了什么。我只是将所有类似的东西放入我的测试数据库中，并按我的预期工作。
custom type
自定义类型
create type custom_type as (
minimum timestamp without time zone,
maximum timestamp without time zone);
table (test)
表（测试）
aadb=# select * from test order by id;
id | a | b
----+----------------------------+----------------------------
1 | 2010-01-11 17:09:52.329779 | 2010-01-11 17:09:52.329779
1 | 2010-01-11 17:10:04.729776 | 2010-01-11 17:10:04.729776
2 | 2010-01-11 17:09:55.049781 | 2010-01-11 17:10:21.753781
2 | 2010-01-11 17:10:30.501781 | 2010-01-11 17:10:30.501781
3 | 2010-01-11 17:09:58.289772 | 2010-01-11 17:09:58.289772
3 | 2010-01-11 17:35:38.089853 | 2010-01-11 17:35:38.089853
(6 rows)
function
功能
create or replace function maxmin (pid integer) returns custom_type as $$
declare
oResult custom_type%rowtype;
begin
select into oResult min(a) as minimum, max(b) as maximum
from test where id = pid;
return oResult;
end;
$$ language plpgsql;
results
结果
aadb=# select * from maxmin(2);
minimum | maximum
----------------------------+----------------------------
2010-01-11 17:09:55.049781 | 2010-01-11 17:10:30.501781
(1 row)
