films_outbox_string_agg.sql

-- PROCEDURE: films.insert_into_outbox(integer)
-- DROP PROCEDURE IF EXISTS films.insert_into_outbox(integer);
-- EXAMPLE: "CALL films.insert_into_outbox(100);"
CREATE OR REPLACE PROCEDURE films.insert_into_outbox(IN filmid integer)
LANGUAGE 'sql'
BEGIN ATOMIC
    -- delete existing record
    DELETE
    FROM films.outbox
    WHERE (outbox.film_id = insert_into_outbox.filmid);
    
    -- insert new record
    INSERT INTO films.outbox (film_id, title, release_year,
                              film_language, rating, categories,
                              actors, rental_duration, length_minutes, 
                              replacement_cost, rental_rate)
    SELECT f.film_id,
        initcap(f.title) AS title,
        f.release_year,
        trim(BOTH FROM l.name) AS film_language,
        f.rating,
        (SELECT array
             (SELECT c.name
              FROM films.film_category AS fc
              JOIN films.category AS c ON fc.category_id = c.category_id
              WHERE film_id = f.film_id)::text AS categories),
        (SELECT array
             (SELECT initcap(concat(a.first_name, ' ', a.last_name)) AS actors
              FROM films.film_actor AS fa
              JOIN films.actor AS a ON fa.actor_id = a.actor_id
              WHERE film_id = f.film_id)::text AS actor_array), 
        f.rental_duration,
        f.length AS length_minutes,
        f.replacement_cost,
        f.rental_rate
    FROM films.film f
        JOIN films.language l ON f.language_id = l.language_id
    WHERE (f.film_id = insert_into_outbox.filmid)
    GROUP BY f.film_id, (trim(BOTH FROM l.name));
END;

ALTER PROCEDURE films.insert_into_outbox (integer) 
    OWNER TO products_admin;