PostgreSQL函数返回表 [英] PostgreSQL function Return table

问题描述

我想在PostgreSQL上设置一个返回表的函数。这是该函数的源代码：

i want to setup a function on PostgreSQL which returns a table. This is the source code of the function:

   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;

调用该函数仅返回带有'，'分隔值的一列：

Calling the function returns only one column with ',' separated values:

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)

我希望有不同的列（每个值一个因为该表是在函数的开头指定的），而不是只有一个包含所有值的表。有人知道为什么会这样吗，我该如何解决？

I expected differnt columns (one for each value as the table is specified at the beginning of the function) and not only one with all values. Does anybody know why this is happening and how i could fix this?

感谢
Timo

Thanks Timo

推荐答案

使用

SELECT *
       FROM feiertag('2017-01-01');

而不是

SELECT feiertag('2017-01-01');

以表的形式获取结果。

（像对待表一样处理函数。）

(Treat the function as if it were a table.)

这篇关于PostgreSQL函数返回表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！