CREATE OR REPLACE FUNCTION shiftwisedata_sp(INOut shiftid bigint,InOut userdate date,OUT shift_name character varying (50),OUT from_time character varying(50),OUT to_time character varying(50),OUT cal bigint)
RETURNS SETOF record AS
$BODY$
BEGIN
return query
SELECT userdate, s.shift_name,
('00:00' + (h.hour * interval '1Hour'):: time) AS from_time,
('00:00' + ((h.hour + 1) * interval '1Hour'):: time) AS to_time,
COALESCE(r.Readings, 0) AS readings
FROM shift_wise s
CROSS JOIN (VALUES(0), (1), (2), (3), (4), (5), (6), (7), (8), (9),
(10), (11), (12), (13), (14), (15), (16), (17), (18), (19),
(20), (21), (22), (23)) AS h(hour)
LEFT JOIN LATERAL (SELECT SUM(r.param_value) AS Readings
FROM table_1 r
WHERE r.timestamp_col >= CAST(userdate as timestamp without time zone ) + h.hour * interval '1Hour'
AND r.timestamp_col < CAST(userdate as timestamp without time zone ) + h.hour + 1 * interval '1Hour'
) AS r
WHERE s.shift_id = shiftid
AND (s.to_time > s.from_time AND
h.hour >= date_part(HOUR, s.from_time) AND
h.hour < date_part(HOUR, s.to_time)
OR
s.to_time < s.from_time AND
(h.hour >= date_part(HOUR, s.from_time) OR
h.hour < date_part(HOUR, s.to_time))
)
ORDER BY s.to_time;
END;
$BODY$
LANGUAGE plpgsql VOLATILE