Создание staging слоя

drop table if exists staging.film;

CREATE TABLE staging.film (

film_id int NOT NULL,

title varchar(255) NOT NULL,

description text NULL,

release_year int2 NULL,

language_id int2 NOT NULL,

rental_duration int2 NOT NULL,

rental_rate numeric(4, 2) NOT NULL,

length int2 NULL,

replacement_cost numeric(5, 2) NOT NULL,

rating varchar(10) NULL,

last_update timestamp NOT NULL,

special_features _text NULL,

fulltext tsvector NOT NULL

);

select * from staging.film;

select * from pg_catalog.pg_available_extensions;

create extension postgres_fdw;

create server film_pg foreign data wrapper postgres_fdw options (

host 'localhost',

dbname 'postgres',

port '5432'

);

create user mapping for postgres server film_pg options (

user 'postgres',

password '1234'

);

drop schema if exists film_src;

create schema film_src authorization postgres;

DROP TYPE if exists mpaa_rating;

CREATE TYPE public.mpaa_rating AS ENUM (

'G',

'PG',

'PG-13',

'R',

'NC-17');

CREATE DOMAIN public.year AS integer CHECK(VALUE >= 1901 AND VALUE <= 2155);

import foreign schema public from server film_pg into film_src;

select * from film_src.actor;

drop procedure if exists staging.film_load();

create procedure staging.film_load() 

as $$

begin

delete from staging.film;

insert

into

staging.film

(film_id,

title,

description,

release_year,

language_id,

rental_duration,

rental_rate,

length,

replacement_cost,

rating,

last_update,

special_features,

fulltext)

select 

film_id,

title,

description,

release_year,

language_id,

rental_duration,

rental_rate,

length,

replacement_cost,

rating,

last_update,

special_features,

fulltext

from

film_src.film;

end;

$$ language plpgsql;

call staging.film_load();

select * from staging.film;