PostgreSQL function Return table

Question

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?

Thanks Timo

Answer 1

Use

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

instead of

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

to get the result as a table.

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

• Timo: Thanks for saving a lot more time, this is working fine!