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

sticky bit · Accepted Answer · 2018-05-21 17:09:15Z

2

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.)

answered May 21, 2018 at 17:09

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Timo Over a year ago

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

Collectives™ on Stack Overflow

PostgreSQL function Return table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related