我将尝试从PostgreSQL函数中返回自定义类型,具体如下:
然而,知道为什么plpgsql函数不能正确工作会很好...
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 $1;
oResult GaugeSummary_GetDateRangeForGauge_Type%ROWTYPE;
BEGIN
SELECT INTO oResult
min(ArchivedMinimumTime) as Minimum,
max(TelemeteredMaximumTime) as Maximum
FROM GaugeSummary
WHERE GaugeID = $1;
RETURN oResult;
END;
$$ LANGUAGE plpgsql;
SELECT GaugeSummary_GetDateRangeForGauge(2291308);
我遇到了两个问题。
1) - 我的结果以 "("1753-01-01 12:00:00","2009-11-11 03:45:00")"的形式返回为单列,而我需要它们以两列的形式返回。
解决!- 愚蠢的错误... 应该是SELECT * FROM GaugeSummary_GetDateRangeForGauge(123)
2) 结果是整个表中的最大值和最小值 - WHERE约束没有被使用。
例如:
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"
但是调用该函数会给我:
"1753-01-01 12:00:00", "2009-11-11 03:45:00"谢谢!
答案2:
似乎在“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 = $1;
$$ LANGUAGE 'SQL' STABLE;
然而,知道为什么plpgsql函数不能正确工作会很好...