create table person
( id bigint primary key ,
name varchar not null,
age integer not null default 10,
gender varchar default 'female' not null ,
address varchar
);
alter table person add constraint ch_gender check ( gender in ('female','male') );
insert into person values (1, 'Anna', 16, 'female', 'Moscow');
insert into person values (2, 'Andrey', 21, 'male', 'Moscow');
insert into person values (3, 'Kate', 33, 'female', 'Kazan');
insert into person values (4, 'Denis', 13, 'male', 'Kazan');
insert into person values (5, 'Elvira', 45, 'female', 'Kazan');
insert into person values (6, 'Irina', 21, 'female', 'Saint-Petersburg');
insert into person values (7, 'Peter', 24, 'male', 'Saint-Petersburg');
insert into person values (8, 'Nataly', 30, 'female', 'Novosibirsk');
insert into person values (9, 'Dmitriy', 18, 'male', 'Samara');
create table pizzeria
(id bigint primary key ,
name varchar not null ,
rating numeric not null default 0);
alter table pizzeria add constraint ch_rating check ( rating between 0 and 5);
insert into pizzeria values (1,'Pizza Hut', 4.6);
insert into pizzeria values (2,'Dominos', 4.3);
insert into pizzeria values (3,'DoDo Pizza', 3.2);
insert into pizzeria values (4,'Papa Johns', 4.9);
insert into pizzeria values (5,'Best Pizza', 2.3);
insert into pizzeria values (6,'DinoPizza', 4.2);
create table person_visits
(id bigint primary key ,
person_id bigint not null ,
pizzeria_id bigint not null ,
visit_date date not null default current_date,
constraint uk_person_visits unique (person_id, pizzeria_id, visit_date),
constraint fk_person_visits_person_id foreign key (person_id) references person(id),
constraint fk_person_visits_pizzeria_id foreign key (pizzeria_id) references pizzeria(id)
);
insert into person_visits values (1, 1, 1, '2022-01-01');
insert into person_visits values (2, 2, 2, '2022-01-01');
insert into person_visits values (3, 2, 1, '2022-01-02');
insert into person_visits values (4, 3, 5, '2022-01-03');
insert into person_visits values (5, 3, 6, '2022-01-04');
insert into person_visits values (6, 4, 5, '2022-01-07');
insert into person_visits values (7, 4, 6, '2022-01-08');
insert into person_visits values (8, 5, 2, '2022-01-08');
insert into person_visits values (9, 5, 6, '2022-01-09');
insert into person_visits values (10, 6, 2, '2022-01-09');
insert into person_visits values (11, 6, 4, '2022-01-01');
insert into person_visits values (12, 7, 1, '2022-01-03');
insert into person_visits values (13, 7, 2, '2022-01-05');
insert into person_visits values (14, 8, 1, '2022-01-05');
insert into person_visits values (15, 8, 2, '2022-01-06');
insert into person_visits values (16, 8, 4, '2022-01-07');
insert into person_visits values (17, 9, 4, '2022-01-08');
insert into person_visits values (18, 9, 5, '2022-01-09');
insert into person_visits values (19, 9, 6, '2022-01-10');
create table menu
(id bigint primary key ,
pizzeria_id bigint not null ,
pizza_name varchar not null ,
price numeric not null default 1,
constraint fk_menu_pizzeria_id foreign key (pizzeria_id) references pizzeria(id));
insert into menu values (1,1,'cheese pizza', 900);
insert into menu values (2,1,'pepperoni pizza', 1200);
insert into menu values (3,1,'sausage pizza', 1200);
insert into menu values (4,1,'supreme pizza', 1200);
insert into menu values (5,6,'cheese pizza', 950);
insert into menu values (6,6,'pepperoni pizza', 800);
insert into menu values (7,6,'sausage pizza', 1000);
insert into menu values (8,2,'cheese pizza', 800);
insert into menu values (9,2,'mushroom pizza', 1100);
insert into menu values (10,3,'cheese pizza', 780);
insert into menu values (11,3,'supreme pizza', 850);
insert into menu values (12,4,'cheese pizza', 700);
insert into menu values (13,4,'mushroom pizza', 950);
insert into menu values (14,4,'pepperoni pizza', 1000);
insert into menu values (15,4,'sausage pizza', 950);
insert into menu values (16,5,'cheese pizza', 700);
insert into menu values (17,5,'pepperoni pizza', 800);
insert into menu values (18,5,'supreme pizza', 850);
create table person_order
(
id bigint primary key ,
person_id bigint not null ,
menu_id bigint not null ,
order_date date not null default current_date,
constraint fk_order_person_id foreign key (person_id) references person(id),
constraint fk_order_menu_id foreign key (menu_id) references menu(id)
);
insert into person_order values (1,1, 1, '2022-01-01');
insert into person_order values (2,1, 2, '2022-01-01');
insert into person_order values (3,2, 8, '2022-01-01');
insert into person_order values (4,2, 9, '2022-01-01');
insert into person_order values (5,3, 16, '2022-01-04');
insert into person_order values (6,4, 16, '2022-01-07');
insert into person_order values (7,4, 17, '2022-01-07');
insert into person_order values (8,4, 18, '2022-01-07');
insert into person_order values (9,4, 6, '2022-01-08');
insert into person_order values (10,4, 7, '2022-01-08');
insert into person_order values (11,5, 6, '2022-01-09');
insert into person_order values (12,5, 7, '2022-01-09');
insert into person_order values (13,6, 13, '2022-01-01');
insert into person_order values (14,7, 3, '2022-01-03');
insert into person_order values (15,7, 9, '2022-01-05');
insert into person_order values (16,7, 4, '2022-01-05');
insert into person_order values (17,8, 8, '2022-01-06');
insert into person_order values (18,8, 14, '2022-01-07');
insert into person_order values (19,9, 18, '2022-01-09');
insert into person_order values (20,9, 6, '2022-01-10');
CREATE TABLE person_audit (
created timestamptz NOT NULL DEFAULT(CURRENT_TIMESTAMP),
type_event char(1) NOT NULL DEFAULT('I'),
row_id bigint NOT NULL,
name varchar,
age integer,
gender varchar,
address varchar,
CONSTRAINT ch_type_event CHECK (type_event IN ('I', 'U', 'D'))
);
CREATE OR REPLACE FUNCTION fnc_trg_person_insert_audit() RETURNS trigger AS $trg_person_insert_audit$
BEGIN
IF (TG_OP = 'INSERT') THEN
INSERT INTO person_audit SELECT CURRENT_TIMESTAMP AT TIME ZONE 'Asia/Yakutsk', 'I', NEW.*;
END IF;
RETURN NULL;
END;
$trg_person_insert_audit$ LANGUAGE plpgsql;
CREATE TRIGGER trg_person_insert_audit
AFTER INSERT ON person FOR EACH ROW EXECUTE FUNCTION fnc_trg_person_insert_audit();
INSERT INTO person(id, name, age, gender, address) VALUES (10,'Damir', 22, 'male', 'Irkutsk');
CREATE OR REPLACE FUNCTION fnc_trg_person_update_audit() RETURNS trigger AS $trg_person_update_audit$
BEGIN
IF (TG_OP = 'UPDATE') THEN
INSERT INTO person_audit SELECT CURRENT_TIMESTAMP AT TIME ZONE 'Asia/Yakutsk', 'U', OLD.*;
END IF;
RETURN NULL;
END;
$trg_person_update_audit$ LANGUAGE plpgsql;
CREATE TRIGGER trg_person_update_audit
AFTER UPDATE ON person FOR EACH ROW EXECUTE FUNCTION fnc_trg_person_update_audit();
UPDATE person SET name = 'Bulat' WHERE id = 10;
UPDATE person SET name = 'Damir' WHERE id = 10;
CREATE OR REPLACE FUNCTION fnc_trg_person_delete_audit() RETURNS trigger AS $trg_person_delete_audit$
BEGIN
IF (TG_OP = 'DELETE') THEN
INSERT INTO person_audit SELECT CURRENT_TIMESTAMP AT TIME ZONE 'Asia/Yakutsk', 'D', OLD.*;
END IF;
RETURN NULL;
END;
$trg_person_delete_audit$ LANGUAGE plpgsql;
CREATE TRIGGER trg_person_delete_audit
AFTER DELETE ON person FOR EACH ROW EXECUTE FUNCTION fnc_trg_person_delete_audit();
DELETE FROM person WHERE id = 10;
DROP TRIGGER trg_person_delete_audit ON person;
DROP TRIGGER trg_person_insert_audit ON person;
DROP TRIGGER trg_person_update_audit ON person;
DROP FUNCTION fnc_trg_person_delete_audit();
DROP FUNCTION fnc_trg_person_insert_audit();
DROP FUNCTION fnc_trg_person_update_audit();
TRUNCATE person_audit;
CREATE OR REPLACE FUNCTION fnc_trg_person_audit() RETURNS TRIGGER AS $trg_person_audit$
BEGIN
IF (TG_OP = 'INSERT') THEN
INSERT INTO person_audit SELECT CURRENT_TIMESTAMP AT TIME ZONE 'Asia/Yakutsk', 'I', NEW.*;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO person_audit SELECT CURRENT_TIMESTAMP AT TIME ZONE 'Asia/Yakutsk', 'U', OLD.*;
ELSIF (TG_OP = 'DELETE') THEN
INSERT INTO person_audit SELECT CURRENT_TIMESTAMP AT TIME ZONE 'Asia/Yakutsk', 'D', OLD.*;
END IF;
RETURN NULL;
END;
$trg_person_audit$ LANGUAGE plpgsql;
CREATE TRIGGER trg_person_audit
AFTER INSERT OR UPDATE OR DELETE ON person FOR EACH ROW EXECUTE FUNCTION fnc_trg_person_audit();
INSERT INTO person(id, name, age, gender, address)
VALUES (10,'Damir', 22, 'male', 'Irkutsk');
UPDATE person SET name = 'Bulat' WHERE id = 10;
UPDATE person SET name = 'Damir' WHERE id = 10;
DELETE FROM person WHERE id = 10;
CREATE FUNCTION fnc_persons_female() RETURNS TABLE (
id bigint,
name varchar,
age integer,
gender varchar,
address varchar
) AS $persons_female$
SELECT * FROM person WHERE gender = 'female'
$persons_female$
LANGUAGE SQL;
CREATE FUNCTION fnc_persons_male() RETURNS TABLE (
id bigint,
name varchar,
age integer,
gender varchar,
address varchar
) AS $persons_male$
SELECT * FROM person WHERE gender = 'male'
$persons_male$
LANGUAGE SQL;
DROP FUNCTION fnc_persons_female(), fnc_persons_male();
CREATE FUNCTION fnc_persons(IN pgender varchar DEFAULT 'female') RETURNS TABLE (
id bigint,
name varchar,
age integer,
gender varchar,
address varchar
) AS $persons$
SELECT * FROM person WHERE pgender = person.gender
$persons$
LANGUAGE SQL;
CREATE FUNCTION fnc_person_visits_and_eats_on_date(
IN pperson varchar DEFAULT 'Dmitriy',
IN pprice numeric DEFAULT 500,
IN pdate date DEFAULT '2022-01-08') RETURNS TABLE (
name_of_pizzerias varchar
) AS $person_visits_and_eats_on_date$
BEGIN
RETURN QUERY
((SELECT pizzeria.name
FROM (SELECT *
FROM
person_visits
WHERE
person_visits.visit_date = pdate) query1
JOIN
(SELECT * FROM person
WHERE person.name = pperson) query2 ON query1.person_id = query2.id
JOIN
pizzeria ON query1.pizzeria_id = pizzeria.id
JOIN
(SELECT * FROM menu
WHERE menu.price < pprice) query3 ON pizzeria.id = query3.pizzeria_id
)
INTERSECT
(SELECT pizzeria.name
FROM (SELECT *
FROM
person_order
WHERE
person_order.order_date = pdate) query1
JOIN
(SELECT * FROM person
WHERE person.name = pperson) query2 ON query1.person_id = query2.id
JOIN
(SELECT * FROM menu
WHERE menu.price < pprice) query3 ON query1.menu_id = query3.id
JOIN
pizzeria ON query3.pizzeria_id = pizzeria.id));
END;
$person_visits_and_eats_on_date$ LANGUAGE plpgsql;
select *
from fnc_person_visits_and_eats_on_date(pprice := 800);
select *
from fnc_person_visits_and_eats_on_date(pperson := 'Anna',pprice := 1300,pdate := '2022-01-01');
Telegram: DB developers channel