for loop inside pg function

Question

I have the following pg script

create or replace function ended_jobs() returns table( "Time Range" timestamptz, "Ended Jobs" numeric ) as $$ begin return query SELECT date_trunc('HOUR',date_interval) as "Time Range", sum(COALESCE(end_count,0)) "Ended Jobs" FROM "job_start_end_rollups" right join ( (select current_timestamp - interval '0 HOUR' date_interval) union all (select current_timestamp - interval '1 HOUR' date_interval) union all (select current_timestamp - interval '2 HOUR' date_interval) union all (select current_timestamp - interval '3 HOUR' date_interval) union all (select current_timestamp - interval '4 HOUR' date_interval) union all (select current_timestamp - interval '5 HOUR' date_interval) union all (select current_timestamp - interval '6 HOUR' date_interval) union all (select current_timestamp - interval '7 HOUR' date_interval) union all (select current_timestamp - interval '8 HOUR' date_interval) union all (select current_timestamp - interval '9 HOUR' date_interval) union all (select current_timestamp - interval '10 HOUR' date_interval) union all (select current_timestamp - interval '11 HOUR' date_interval) union all (select current_timestamp - interval '12 HOUR' date_interval) union all (select current_timestamp - interval '13 HOUR' date_interval) union all (select current_timestamp - interval '14 HOUR' date_interval) union all (select current_timestamp - interval '15 HOUR' date_interval) ) date_intervals on date_trunc('HOUR', TIMESTAMP WITH TIME ZONE 'epoch' + chunk_start_time * interval '1 millisecond' ) = date_trunc('HOUR',date_interval) GROUP BY date_part('HOUR',TIMESTAMP WITH TIME ZONE 'epoch' + chunk_start_time * interval '1 millisecond'),date_interval ORDER BY date_interval LIMIT 16; end; $$ LANGUAGE 'plpgsql';

After running the above mentioned pg script I could get the data

select * from ended_jobs()

How can I write 'for loop' inside the right join to avoid multiple 'union all', like this

....... right join ( (select current_timestamp - interval '0 HOUR' date_interval) for i in 1..16 loop union all (select current_timestamp - concat(i,' HOUR')::INTERVAL date_interval) end loop; ........

Unrelated, but: you don't need plpgsql for this. A simple SQL function will be enough. Also: the language name is an identifier, do not put that into single quotes: it should be language plpgsql — user330315
– user330315, Commented May 23, 2018 at 7:33

user330315 · Accepted Answer · 2018-05-23 07:29:27Z

You can use generate_series() for that:

FROM "job_start_end_rollups" right join generate_series(current_timestamp - interval '15 HOUR', current_timestamp, interval '1 hour') as date_intervals(date_interval) ON ....

You can also move the date_trunc() into that:

right join ( select date_trunc('HOUR',di) as date_interval from generate_series(current_timestamp - interval '15 HOUR', current_timestamp, interval '1 hour') as t(di) ) as date_intervals ON .... = date_intervals.date_interval

@KhachaturSaribekyan: if that solved you problem, please accept the answer so that your question is marked as resolved.

Collectives™ on Stack Overflow

for loop inside pg function

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related