通过函数调用使用 Postgresql 的 EXTRACT 工具的困难

Question

我正试图弄清楚如何使用PostgresqlEXTRACT函数将给定的date_variable转换为一周中等效的一天。我知道它将把date_variable转换成从0到6的编号(0是星期日，6是星期六等(

我创建了一个简单的表来测试我的查询。在这里，我将尝试将start_date转换为其DOW等价物。

DROP TABLE IF EXISTS test;
CREATE TABLE test(
start_date    date PRIMARY KEY,
end_date      date
);
INSERT INTO test (start_date, end_date) VALUES ('2021-03-31', '2021-03-31'); -- Today (wed), hence 3
INSERT INTO test (start_date, end_date) VALUES ('2021-03-30', '2021-03-30'); -- Yesterday (tues), hence 2
INSERT INTO test (start_date, end_date) VALUES ('2021-03-29', '2021-03-29'); -- Day before (mon), hence 1

如果我在下面运行查询

SELECT (EXTRACT(DOW FROM t.start_date)) AS day FROM test t;

它运行良好，并按预期返回结果(分别返回值为(3、2、1(的单列表(

然而，当我试图编写一个函数来返回完全相同的查询时

CREATE OR REPLACE FUNCTION get_day()
RETURNS TABLE (day integer) AS $$
BEGIN
RETURN QUERY
SELECT (EXTRACT(DOW FROM t.start_date)) as day 
FROM test t;
END;
$$ LANGUAGE plpgsql;
SELECT * FROM get_day(); -- throws error "structure of query does not match function result type"

我得到了一个错误。我似乎找不到问题，也不知道是什么原因造成的。

Answer 1

extract()返回一个double precision值，但函数被声明为返回一个整数。你需要铸造价值：

CREATE OR REPLACE FUNCTION get_day()
RETURNS TABLE (day integer) AS $$
BEGIN
RETURN QUERY
SELECT EXTRACT(DOW FROM t.start_date)::int as day 
FROM test t;
END;
$$ LANGUAGE plpgsql;

但您并不真的需要PL/pgSQL，language SQL函数也可以。

CREATE OR REPLACE FUNCTION get_day()
RETURNS TABLE (day integer) AS $$
SELECT EXTRACT(DOW FROM t.start_date)::int as day 
FROM test t;
$$ 
LANGUAGE sql
stable;

由于你没有传递任何参数，我实际上会使用一个视图：

create or replace view day_view
AS
SELECT EXTRACT(DOW FROM t.start_date)::int as day 
FROM test t;

Answer 2

提取函数返回双精度类型的值。

您将结果声明为整数。

您应该将EXTRACT的结果强制转换为integer:

CREATE OR REPLACE FUNCTION get_day()
RETURNS TABLE (day integer) AS $$
BEGIN
RETURN QUERY
SELECT EXTRACT(DOW FROM t.start_date)::integer as day 
FROM test t;
END;
$$ LANGUAGE plpgsql;