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

--Создать в staging слое таблицы для загрузки данных из таблиц:

--- inventory

--- rental

--- payment

create table staging.inventory(

inventory_id int4 not null,

film_id int2 not null,

store_id int2 not null 

);

create table staging.rental(

rental_id int4 not null,

rental_date timestamp not null,

inventory_id int4 not null,

customer_id int2 not null,

return_date timestamp,

staff_id int2 not null

);

create table staging.payment(

payment_id int4 not null,

customer_id int2 not null,

staff_id int2 not null,

rental_id int4 not null,

amount numeric(5,2) not null,

payment_date timestamp not null

);

--Сделать процедуры для загрузки данных из источника в staging слой в таблицы:

--- inventory

--- rental

--- payment

create procedure inventory_load()

as $$

begin

delete from staging.inventory;

INSERT INTO staging.inventory

(

inventory_id, 

film_id, 

store_id

)

select 

inventory_id, 

film_id, 

store_id

from

film_src.inventory i;

end;

$$ language plpgsql;

call inventory_load();

select * from staging.inventory;

create procedure rental_load()

as $$

begin

delete from staging.rental;

INSERT INTO staging.rental

(

rental_id, 

rental_date, 

inventory_id, 

customer_id, 

return_date, 

staff_id

)

select 

rental_id, 

rental_date, 

inventory_id, 

customer_id, 

return_date, 

staff_id

from

film_src.rental;

end;

$$ language plpgsql;

call rental_load();

select * from staging.rental r ;

create procedure payment_load()

as $$

begin

delete from staging.payment;

INSERT INTO staging.payment

(

payment_id, 

customer_id, 

staff_id, 

rental_id, 

amount, 

payment_date

)

select

payment_id, 

customer_id, 

staff_id, 

rental_id, 

amount, 

payment_date

from

film_src.payment;

end;

$$ language plpgsql;

call payment_load();

select * from staging.payment p;

call film_load();

call inventory_load();

call rental_load();

call payment_load();