Разбор домашнего задания

Полный код пересоздания staging, core и report слоев вместе с расчетом отчета из ДЗ. Изменения отмечены желтым фоном.

drop table if exists report.sales_film;

create table report.sales_film (

film_title varchar(255) not null,

amount numeric(7,2) not null 

);

create or replace procedure report.sales_film_calc()

as $$

begin 

delete from report.sales_film;

INSERT INTO report.sales_film

(

film_title, 

amount

)

select

di.title as film_title,

sum(p.amount) as amout

from

core.fact_payment p

join core.dim_inventory di 

on p.inventory_fk = di.inventory_pk 

group by

di.title;

end;

$$ language plpgsql;

create or replace procedure full_load()

as $$

begin

call staging.film_load();

call staging.inventory_load();

call staging.rental_load();

call staging.payment_load();

call staging.staff_load();

call staging.address_load();

call staging.city_load();

call staging.store_load();

call core.fact_delete();

call core.load_inventory();

call core.load_staff();

call core.load_payment();

call core.load_rental();

call report.sales_date_calc();

call report.sales_film_calc();

end;

$$ language plpgsql;