我想在PostgreSQL上设置一个返回表的函数。这是该函数的源代码:
CREATE OR REPLACE FUNCTION feiertag(inDate Date) RETURNS TABLE (eingabeDatum DATE, f_heute INT, f_1 INT, f_2 INT, f_3 INT, f_5 INT) AS $$ DECLARE f_heute integer := 0; f_1 integer := 0; f_2 integer := 0; f_3 integer := 0; f_5 integer := 0; BEGIN SELECT 1 INTO f_heute FROM feiertage where datum = inDate; SELECT 1 INTO f_1 FROM feiertage where datum = (inDate + interval '1' day); SELECT 1 INTO f_2 FROM feiertage where datum = (inDate + interval '2' day); SELECT 1 INTO f_3 FROM feiertage where datum = (inDate + interval '3' day); SELECT 1 INTO f_5 FROM feiertage where datum = (inDate + interval '5' day); RETURN QUERY SELECT inDate as eingabeDatum, coalesce(f_heute, 0) as f_heute, coalesce(f_1,0) as f_1, coalesce(f_2,0) as f_2, coalesce(f_3,0) as f_3, coalesce(f_5,0) as f_5 ; END; $$ LANGUAGE plpgsql;
调用该函数仅返回一列,并用’,’分隔值:
psql (9.5.12) Type "help" for help. tarec=> select feiertag('2017-01-01'); feiertag ------------------------ (2017-01-01,1,0,0,0,0) (1 row)
我期望有不同的列(在函数的开头为表指定的每个值一个),而不仅仅是所有值的列。有谁知道为什么会这样,我该如何解决?
谢谢蒂莫
使用
SELECT * FROM feiertag('2017-01-01');
代替
SELECT feiertag('2017-01-01');
以获得表格的结果。
(像对待表一样处理该函数。)