Использование временных таблиц
-- когда функция возвращает несколько значений - как обращаться к одному из параметров?
-- в скобки взять вызов функции и через точку обратиться к полю
drop function filmAverages;
create or replace function filmAverages(out disk_per_film float, out amount_per_film float, out film_title_top_amount varchar(255))
as $$
declare
film_nm integer;
disk_nm integer;
amount float;
begin
film_nm := (select count(*) from film);
disk_nm := (select count(*) from inventory);
amount := (select sum(p.amount) from payment p);
declare
film_id_total_amount integer;
begin
film_id_total_amount := (
-- подзапрос должен возвращать одну строку и одно значение
select
film_id
from
(
select
i.film_id,
sum(p.amount) as total_amount
from
inventory i
join rental r using(inventory_id)
join payment p using(rental_id)
group by
i.film_id
order by
total_amount desc
limit 1
) subq
);
film_title_top_amount := (
select
f.title
from
film f
where
f.film_id = film_id_total_amount
);
end;
disk_per_film := 1.0 * disk_nm / film_nm;
amount_per_film := amount / film_nm;
end;
$$
language plpgsql immutable;select disk_per_film from filmAverages();
explain analyze
select
(filmAverages()).disk_per_film,
(filmAverages()).amount_per_film,
(filmAverages()).film_title_top_amount;
select filmAverages()
-- внутри другой функции можно результат сохранить в record
create function testF(out disk_per_film float)
as $$
declare
i record;
begin
i = filmAverages();
disk_per_film = i.disk_per_film;
end;
$$ language plpgsql;
select testF();
-- создание временных таблиц
-- Создать функцию, которая на вход принимает rental_rate и при расчетах учитывает только фильмы с указанным rental_rate или больше.
-- По каждой rental_duration из отобрнных фильмов определить:
--- Сколько всего фильмов
--- В скольких категориях фильмов есть фильмы
--- Какая средняя выручка на фильм
drop function if exists rental_duration_details;
create or replace function rental_duration_details(rental_rate float) returns
table(
rental_duration integer,
total_films integer,
category_nm integer,
amount_avg_per_film float)
as $$
begin
-- получаем все нужные фильмы
create temporary table film_t on commit drop as
select
f.film_id,
f.rental_duration
from
film f
where
f.rental_rate >= rental_duration_details.rental_rate;
-- считаем сколько всего фильмов с каждым из значений rental_duration
create temporary table total_films on commit drop as
select
f.rental_duration,
count(*) as total_films
from
film_t f
group by
f.rental_duration;
-- считаем в сколько категорий попадают фильмы с одним значением rental_duration
create temporary table category_nm on commit drop as
select
f.rental_duration,
count(distinct fc.category_id) as category_nm
from
film_t f
left join film_category fc using(film_id)
group by
f.rental_duration;
-- считаем среднюю выручку на фильм
create temporary table amount_total_by_rental_duration on commit drop as
select
f.rental_duration,
sum(p.amount) as total_amount
from
film_t f
left join inventory i using(film_id)
left join rental r using(inventory_id)
left join payment p using(rental_id)
group by
f.rental_duration;
return query
select
f.rental_duration::integer,
tf.total_films::integer,
cnm.category_nm::integer,
cast((ard.total_amount / tf.total_films) as float) as amount_avg_per_film
from
(
select distinct
t.rental_duration
from
film_t t
) f
join total_films tf on tf.rental_duration = f.rental_duration
join category_nm cnm on cnm.rental_duration = f.rental_duration
join amount_total_by_rental_duration ard on ard.rental_duration = f.rental_duration;
return;
end;
$$ language plpgsql;
select
*
from
rental_duration_details(0.5)
order by rental_duration;
select * from category_nm;
drop table category_nm;