apply postgres function on an array without unnest

Question

I have a postgres function called site_opened_on_date which take as input an id and a date. The function's signature looks like this :

CREATE OR REPLACE FUNCTION recntrek.site_opened_on_date(
site_id bigint,
in_date date DEFAULT (
now(
))::date)
RETURNS SETOF "TABLE(date date, opening_time time without time zone, closing_time time without time zone)"
LANGUAGE 'plpgsql'
COST 5.0
STABLE 
ROWS 3.0
AS $function$

I want to apply this function on 7 days. I have tried this:

SELECT (
SELECT t FROM site_opened_on_date(100520000101526, _d) t
)FROM unnest(ARRAY[ now(),
 now()+ INTERVAL '1 DAY',
 now()+ INTERVAL '2 DAY',
 now()+ INTERVAL '3 DAY',
 now()+ INTERVAL '4 DAY',
 now()+ INTERVAL '5 DAY',
 now()+ INTERVAL '6 DAY'
]::DATE[])  _d;

But I get the following error: more than one row returned by a subquery used as an expression This is due to the fact that the site_opened_on_date function can return more than one row for a date. Anyone has an idea of a solution ? I would rather not write a new postgres function, I would prefer to find a way to apply my array to the existing function in a query.

it should also be possible to use a function param as array with in_date date[] in postgresql.. and call the function like SELECT site_opened_on_date(100520000101526, ARRAY[ now(), now()+ INTERVAL '1 DAY', now()+ INTERVAL '2 DAY', now()+ INTERVAL '3 DAY', now()+ INTERVAL '4 DAY', now()+ INTERVAL '5 DAY', now()+ INTERVAL '6 DAY' ]::DATE[]) — Raymond Nijland
– Raymond Nijland, Commented Apr 28, 2018 at 21:34

klin · Accepted Answer · 2018-04-28 21:54:53Z

1

You can easily generate a series of consecutive dates with the function generate_series(start, stop, step interval):

SELECT t.*
FROM generate_series(now(), now()+ '6 days', '1 day') as _d
CROSS JOIN LATERAL site_opened_on_date(100520000101526, _d::date) t

or

SELECT (site_opened_on_date(100520000101526, _d::date)).*
FROM generate_series(now(), now()+ '6 days', '1 day') as _d

Use

SELECT t.*

instead of

SELECT t

to get resulting rows in columns (instead of a single column with tuples).

edited Apr 28, 2018 at 21:54

answered Apr 28, 2018 at 21:36

klin

123k15 gold badges241 silver badges263 bronze badges

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

Comments

Adam · Accepted Answer · 2018-04-28 22:08:43Z

1

You don't need to use subquery. Just do:

SELECT
    (recntrek.site_opened_on_date(100520000101526, _d)).*
FROM
    unnest( ARRAY[
            now(),
            now()+ INTERVAL '1 DAY',
            now()+ INTERVAL '2 DAY',
            now()+ INTERVAL '3 DAY',
            now()+ INTERVAL '4 DAY',
            now()+ INTERVAL '5 DAY',
            now()+ INTERVAL '6 DAY'
        ]::DATE[]) _d;

See example here.

edited Apr 28, 2018 at 22:08

answered Apr 28, 2018 at 21:00

Adam

5,6296 gold badges35 silver badges41 bronze badges

2 Comments

Leah Aiach Wolff Over a year ago

Thank you ! I don't get an error anymore. Is there a way I can receive also the column name (date, opening_time, closing_time) in my result ? I would like to have a chart as a result (with column names). Thanks!

Adam Over a year ago

Use SELECT (recntrek.site_opened_on_date(100520000101526, _d)).* FROM ... to get separate columns.

Collectives™ on Stack Overflow

apply postgres function on an array without unnest

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related