function: 1: create or replace function get_actor_by_filmID(input_film… — Database 4031 — TG.ME

create or replace function get_actor_by_filmID(input_filmID integer)
returns table  (
  first_name varchar(45),
  last_name varchar(45)
)
as $$
begin
    return query
    select
  actor.first_name,
  actor.last_name
    from
        actor join film_actor using(actor_id)
    where
        film_actor.film_id = input_filmID ;
end;
$$ language plpgsql;

select get_actor_by_filmID(133)

create or replace function get_films_of_country(input_contry_of_film  varchar)
returns table  (
  film_id int,
  title varchar
)
as $$
begin
    return query
    select 
  distinct film.film_id,film.title
    from
        film join inventory using(film_id)
    join store using(store_id)
    join address using(address_id)
    join city using(city_id)
    join country using(country_id)
    where
        country = input_contry_of_film
  order by film_id asc;  
end;
$$ language plpgsql;

select *from  get_films_of_country('Canada')

CREATE OR REPLACE FUNCTION FilmAvailability(film_id INT, store_id INT)
RETURNS BOOLEAN AS $$
BEGIN
    RETURN (
        -- Check if there are available (unrented) copies in the store
        (SELECT COUNT(*) 
         FROM inventory i
         LEFT JOIN rental r ON i.inventory_id = r.inventory_id AND r.return_date IS NULL
         WHERE i.film_id = $1
         AND i.store_id = $2
         AND r.rental_id IS NULL) > 0
    );
END;
$$ LANGUAGE plpgsql;

SELECT FilmAvailability(10, 1);

CREATE OR REPLACE FUNCTION CustomerBalance(customer_id INT)
RETURNS DECIMAL AS $$
BEGIN
    RETURN (
        -- Total rental fees minus total payments
        (SELECT COALESCE(SUM(f.rental_rate), 0)
         FROM rental r
         JOIN inventory i ON r.inventory_id = i.inventory_id
         JOIN film f ON i.film_id = f.film_id
         WHERE r.customer_id = $1)
        -
        (SELECT COALESCE(SUM(amount), 0)
         FROM payment
         WHERE payment.customer_id = $1)
    );
END;
$$ LANGUAGE plpgsql;

SELECT CustomerBalance(12);