Hello
I would like to clean out duplicates here
Could you show the better way to rewrite my query?
--DROP FUNCTION get_customers_rental_activity;
CREATE OR REPLACE FUNCTION get_customers_rental_activity (IN i_client_id INTEGER, i_left_boundary DATE, i_right_boundary DATE) RETURNS TABLE (metric_name# TEXT, metric_value# TEXT) LANGUAGE plpgsql AS $$ BEGIN RETURN query SELECT 'customer''s info#' AS "name", first_name || ', ' || last_name || ', ' || email AS "value" FROM customer c WHERE customer_id = i_client_id UNION ALL SELECT 'num.of films rented#' AS "name", COUNT(i.film_id)::VARCHAR(5) AS "value" FROM payment p JOIN rental r ON p.rental_id = r.rental_id JOIN inventory i ON r.inventory_id = i.inventory_id WHERE r.rental_date >= (i_left_boundary::DATE) AND r.rental_date <= (i_right_boundary::DATE) AND p.customer_id = i_client_id UNION ALL SELECT 'rented film''s titles#' AS "name", array_to_string(array_agg(DISTINCT f.title), ', ') AS "value" FROM payment p JOIN rental r ON p.rental_id = r.rental_id JOIN inventory i ON r.inventory_id = i.inventory_id JOIN film f ON i.film_id = f.film_id WHERE r.rental_date >= (i_left_boundary::DATE) AND r.rental_date <= (i_right_boundary::DATE) AND p.customer_id = i_client_id GROUP BY p.customer_id UNION ALL SELECT 'num.of payments#' AS "name", count(p.payment_id)::VARCHAR(5) AS "value" FROM payment p JOIN rental r ON p.rental_id = r.rental_id JOIN inventory i ON r.inventory_id = i.inventory_id WHERE r.rental_date >= (i_left_boundary::DATE) AND r.rental_date <= (i_right_boundary::DATE) AND p.customer_id = i_client_id UNION ALL SELECT 'payment''s amount#' AS "name", sum(p.amount)::varchar(5) AS "value" FROM payment p JOIN rental r ON p.rental_id = r.rental_id JOIN inventory i ON r.inventory_id = i.inventory_id WHERE r.rental_date >= (i_left_boundary::DATE) AND r.rental_date <= (i_right_boundary::DATE) AND p.customer_id = i_client_id; end;$$
--check query
SELECT * FROM get_customers_rental_activity(60, '2005-05-03', '2017-05-03');