Описание:

В предыдущем задании вы изучили основы использования SQL для хранения и обработки данных в PostgreSQL. Сейчас мы обсудим более сложные возможности SQL, помогающие гибко управлять данными.

Перейдем сразу к делу, допустим, у вас есть таблица пользователей и вы очень часто к ней обращаетесь. Причем вам нужна не вся таблица, а только определенные поля. Очень скоро вам надоест вводить один и тот же запрос руками. Задача усложняется, если вы работаете на удаленной машине, где нет возможности скопировать данные из текстового документа в консоль, чтобы быстро ввести запрос. Да и, в принципе, вы не хотели бы каждый раз вводить весь этот запрос.

Есть ли решение этой задачи в PostgreSQL? Да! Точно, есть!

Вы можете создать представление по этому запросу.

Но что такое представление и как с ним работать? Давайте разберемся!

View (представление)

Итак, снова вспомним, что вы не хотите каждый раз вводить один и тот же запрос.

Выглядит ваш запрос так:

SELECT username, email
  FROM users
    WHERE city = moscow;

Вы можете создать представление по этому запросу, то есть, по факту, просто присвоить имя запросу и сохранить его в базе данных, а затем обращаться к нему по имени как к обычной таблице.

Выглядит это следующим образом:

# Создаем представление с помощью DDL SQL команды CREATE с ключевым словом VIEW, 
# которое из **таблицы №1** предыдущего задания будет возвращать список пользователей, территориально находящихся в городе Москва. 
# Также напротив каждого пользователя будет имя его электронной почты.

CREATE VIEW usernames_and_emails_sity_moscow AS
    SELECT username, email
        FROM users
          WHERE city = moscow;

Теперь, чтобы запросить из базы то же самое, выполним команду:

SELECT * FROM usernames_and_emails_sity_moscow;

Результат тот же самый, что и в обычном запросе! Удобно? Не то слово!

По факту, активное использование представлений — это ключевой аспект хорошего проектирования баз данных SQL. Представления позволяют вам скрыть внутреннее устройство таблиц, которое может меняться по мере развития приложения, за надежными интерфейсами.

Представления можно использовать практически везде, где можно использовать обычные таблицы. И довольно часто представления создаются на базе других представлений.

Встроенные функции

PostgreSQL предоставляет огромное количество функций и операторов для встроенных типов данных. Но важно понимать, что практически все функции и операторы, описанные в документации PostgreSQL, за исключением простейших арифметических и операторов сравнения, а также явно отмеченных функций, не описаны в стандарте SQL. Поэтому, если для вас важна переносимость базы данных, имейте в виду этот факт.

Теперь разберемся подробнее.

Посмотреть все существующие функции и операторы можно через утилиту psql с помощью команд \df и \do, соответственно.

Типы аргументов и результата функции обозначаются так:

repeat ( text, integer ) → text

В данном случае она говорит, что функция repeat принимает один текстовый и один целочисленный аргумент и возвращает результат текстового типа. Стрелка вправо также указывает на результат в примере использования, например:

repeat('Pg', 4) → PgPgPgPg

Оконные функции

Оконная функция выполняет вычисления для набора строк, некоторым образом связанных с текущей строкой. Ее действие можно сравнить с вычислением, производимым агрегатной функцией. Однако с оконными функциями строки не группируются в одну выходную строку, как это происходит с обычными, не оконными, агрегатными функциями. Вместо этого строки остаются отдельными сущностями. Внутри же оконная функция, как и агрегатная, может обращаться не только к текущей строке результата запроса.

Например, вы хотите посчитать стоимость всех практикумов компании REBRAIN из таблицы №2 предыдущего задания. Запрос будет выглядеть так:

SELECT course_id, coursename, price, tasks_count, sum(price) OVER () FROM courses;

Результат:

Модернизируем запрос так, чтобы цена выводилась постепенно в каждой строке инкрементно:

SELECT course_id, coursename, price, tasks_count, sum(price) OVER (ORDER BY course_id) FROM courses;

Результат:

Функции, созданные вами (custom functions)

В PostgreSQL представлены функции четырех видов:

1. Функции на языке запросов (функции, написанные на SQL)
2. Функции на процедурных языках (функции, написанные, например, на PL/pgSQL или PL/Tcl)
3. Внутренние функции
4. Функции на языке C

Функции любых видов могут принимать в качестве аргументов (параметров) базовые типы, составные типы или их сочетания. Кроме того, любые функции могут возвращать значения базового или составного типа. Также можно определить функции, возвращающие наборы базовых или составных значений.

Функции многих видов могут также принимать или возвращать определенные псевдотипы (например, полиморфные типы), но доступные средства для работы с ними различаются. За подробностями обратитесь к описанию конкретного вида функций.

Проще всего определить функции на языке SQL, поэтому сначала мы рассмотрим их. Многие концепции, касающиеся функций на SQL, затем распространятся и на другие виды функций.

CREATE FUNCTION — создать функцию

CREATE [ OR REPLACE ] FUNCTION
    имя ( [ [ режим_аргумента ] [ имя_аргумента ] тип_аргумента [ { DEFAULT | = } выражение_по_умолчанию ] [, ...] ] )
    [ RETURNS тип_результата
      | RETURNS TABLE ( имя_столбца тип_столбца [, ...] ) ]
  { LANGUAGE имя_языка
    | TRANSFORM { FOR TYPE имя_типа } [, ... ]
    | WINDOW
    | IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF
    | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
    | PARALLEL { UNSAFE | RESTRICTED | SAFE }
    | COST стоимость_выполнения
    | ROWS строк_в_результате
    | SUPPORT вспомогательная_функция
    | SET параметр_конфигурации { TO значение | = значение | FROM CURRENT }
    | AS 'определение'
    | AS 'объектный_файл', 'объектный_символ'
  } ...

Примеры: Ниже приведено несколько простых примеров.

CREATE FUNCTION add(integer, integer) RETURNS integer
    AS 'select $1 + $2;'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

Функция увеличения целого числа на 1, использующая именованный аргумент, на языке PL/pgSQL:

CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$
        BEGIN
                RETURN i + 1;
        END;
$$ LANGUAGE plpgsql;

Функция, возвращающая запись с несколькими выходными параметрами:

CREATE FUNCTION dup(in int, out f1 int, out f2 text)
    AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
    LANGUAGE SQL;

SELECT * FROM dup(42);

То же самое можно сделать более развернуто, явно объявив составной тип:

CREATE TYPE dup_result AS (f1 int, f2 text);

CREATE FUNCTION dup(int) RETURNS dup_result
    AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
    LANGUAGE SQL;

SELECT * FROM dup(42);

Еще один способ вернуть несколько столбцов — применить функцию TABLE:

CREATE FUNCTION dup(int) RETURNS TABLE(f1 int, f2 text)
    AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
    LANGUAGE SQL;

SELECT * FROM dup(42);

Однако пример с TABLE отличается от предыдущих, так как в нем функция на самом деле возвращает не одну, а набор записей.

Чтобы удалить ненужную функцию используйте команду DROP FUNCTION

DROP FUNCTION [ IF EXISTS ] имя [ ( [ [ режим_аргумента ] [ имя_аргумента ] тип_аргумента [, ...] ] ) ] [, ...]
    [ CASCADE | RESTRICT ]

DROP FUNCTION удаляет существующую функцию.

Важно! Пользователь, выполняющий эту команду, должен быть владельцем функции. Помимо имени функции требуется указать типы ее аргументов, так как в базе данных могут существовать несколько функций с одним именем, но с разными списками аргументов.

Примеры: Эта команда удаляет функцию, вычисляющую квадратный корень:

DROP FUNCTION sqrt(integer);

Эта — удаляет нескольких функций одной командой:

DROP FUNCTION sqrt(integer), sqrt(bigint);

Если имя функции уникально в ее схеме, на нее можно сослаться без списка аргументов:

DROP FUNCTION update_employee_salaries;

Заметьте, что эта строка отличается от

DROP FUNCTION update_employee_salaries();

Данная форма ссылается на функцию с нулем аргументов, тогда как первый вариант подходит для функции с любым числом аргументов, в том числе и с нулем, если имя функции уникально.

Разработка защищенных функций SECURITY DEFINER

Так как функция SECURITY DEFINER выполняется с правами пользователя, владеющего ей, необходимо позаботиться о том, чтобы ее нельзя было использовать не по назначению. В целях безопасности в пути search_path следует исключить любые схемы, доступные на запись недоверенным лицам. Это не позволит злонамеренным пользователям создать свои объекты (например, таблицы, функции и операторы), которые замаскируют объекты, используемые функцией. Особенно важно в этом отношении исключить схему временных таблиц, которая по умолчанию просматривается первой, а право записи в нее по умолчанию имеют все. Соответствующую защиту можно организовать, поместив временную схему в конец списка поиска. Для этого следует сделать pg_temp последней записью в search_path. Безопасное использование демонстрирует следующая функция:

CREATE FUNCTION check_password(uname TEXT, pass TEXT)
RETURNS BOOLEAN AS $$
DECLARE passed BOOLEAN;
BEGIN
        SELECT  (pwd = $2) INTO passed
        FROM    pwds
        WHERE   username = $1;

        RETURN passed;
END;
$$  LANGUAGE plpgsql
    SECURITY DEFINER
    -- Установить безопасный путь поиска: сначала доверенная схема(ы), затем 'pg_temp'.
    SET search_path = admin, pg_temp;

Эта функция должна обращаться к таблице admin.pwds, но без предложения SET или с SET, включающим только admin, ее можно «обмануть», создав временную таблицу pwds.

До PostgreSQL 8.3 предложение SET отсутствовало, так что старые функции могут содержать довольно сложную логику для сохранения, изменения и восстановления переменной search_path. Существующее теперь предложение SET позволяет сделать это намного проще.

Также следует помнить о том, что по умолчанию право выполнения для создаваемых функций имеет роль PUBLIC. Однако часто требуется разрешить доступ к функциям, работающим в контексте определившего, только некоторым пользователям. Для этого необходимо отозвать стандартные права PUBLIC и затем дать права на выполнение индивидуально. Чтобы не образовалось окно, в котором новая функция будет доступна всем, создайте ее и назначьте права в одной транзакции. Например, так:

BEGIN;
CREATE FUNCTION check_password(uname TEXT, pass TEXT) ... SECURITY DEFINER;
REVOKE ALL ON FUNCTION check_password(uname TEXT, pass TEXT) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION check_password(uname TEXT, pass TEXT) TO admins;
COMMIT;

Stored Procedures

Процедура представляет собой объект базы данных, подобный функции. Отличие состоит в том, что процедура не возвращает значение, и поэтому для нее не определяется возвращаемый тип. Тогда как функция вызывается в составе запроса или команды DML, процедура делается явно командой CALL. Если команда CALL выполняется не в явной транзакции, в процедурах на многих серверных языках во время выполнения можно начинать, фиксировать и отменять транзакции, что невозможно в функциях.

Все, о чем мы поговорим в теме функций, применимо и к процедурам, за исключением:

• для процедур используется команда CREATE PROCEDURE;
• не определяется тип результата;
• к ним не относятся некоторые свойства, например, строгость.

Функции и процедуры в совокупности также называются подпрограммами. Существуют команды, такие как ALTER ROUTINE и DROP ROUTINE, которые способны работать и с функциями, и с процедурами, не требуя указания точного вида объекта. Однако заметьте, что команды CREATE ROUTINE нет.

Теперь немного о команде CREATE PROCEDURE — создать процедуру.

CREATE [ OR REPLACE ] PROCEDURE
    имя ( [ [ режим_аргумента ] [ имя_аргумента ] тип_аргумента [ { DEFAULT | = } выражение_по_умолчанию ] [, ...] ] )
  { LANGUAGE имя_языка
    | TRANSFORM { FOR TYPE имя_типа } [, ... ]
    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
    | SET параметр_конфигурации { TO значение | = значение | FROM CURRENT }
    | AS 'определение'
    | AS 'объектный_файл', 'объектный_символ'
  } ...

Пример использования:

CREATE PROCEDURE insert_data(a integer, b integer)
LANGUAGE SQL
AS $$
INSERT INTO tbl VALUES (a);
INSERT INTO tbl VALUES (b);
$$;

CALL insert_data(1, 2);

Команда CREATE PROCEDURE определяет новую процедуру. CREATE OR REPLACE PROCEDURE создает новую процедуру либо заменяет определение уже существующей. Чтобы определить процедуру, необходимо иметь право USAGE для соответствующего языка.

Если указано имя схемы, процедура создается в заданной схеме, в противном случае — в текущей. Имя новой процедуры должно отличаться от имен существующих процедур и функций с такими же типами аргументов в этой схеме. Однако процедуры и функции с аргументами разных типов могут иметь одно имя (это называется перегрузкой).

Команда CREATE OR REPLACE PROCEDURE предназначена для изменения текущего определения существующей процедуры. С ее помощью нельзя изменить имя или типы аргументов (если попытаться сделать это, будет создана новая отдельная процедура).

Когда команда CREATE OR REPLACE PROCEDURE заменяет существующую процедуру, владелец и права доступа к этой процедуре не меняются. Все другие свойства процедуры получают значения, задаваемые командой явно или по умолчанию. Чтобы заменить процедуру, необходимо быть ее владельцем (или быть членом роли-владельца).

Важно! Владельцем процедуры становится создавший ее пользователь. Важно! Чтобы создать процедуру, необходимо иметь право USAGE для типов ее аргументов.

Замечания:

• Дополнительные детали создания функций, которые применимы и к процедурам, описываются в CREATE FUNCTION.

Чтобы выполнить процедуру, воспользуйтесь командой CALL — вызвать процедуру.

CALL имя ( [ аргумент ] [, ...] )

Пример использования:

CALL do_db_maintenance();

Чтобы вызывать процедуру, пользователь должен иметь право EXECUTE для нее. Для вызова функции (не процедуры) следует использовать SELECT. Если CALL выполняется в блоке транзакции, вызываемая процедура не может выполнять операторы управления транзакциями. Такие операторы допускаются, только если CALL выполняется в собственной транзакции.

Выходные параметры команд CALL в PL/pgSQL обрабатываются по-другому.

Чтобы удалить ненужную вам процедуру, используйте команду DROP PROCEDURE.

DROP PROCEDURE [ IF EXISTS ] имя [ ( [ [ режим_аргумента ] [ имя_аргумента ] тип_аргумента [, ...] ] ) ] [, ...]
    [ CASCADE | RESTRICT ]

Пример использования:

DROP PROCEDURE do_db_maintenance();

DROP PROCEDURE удаляет существующую процедуру.

Важно! Пользователь, выполняющий эту команду, должен быть владельцем процедуры. Помимо имени процедуры требуется указать типы ее аргументов, так как в базе данных могут существовать несколько процедур с одним именем, но с разными списками аргументов.

sequence (генераторы последовательности)

Если коротко, то sequence — это инкремент и можно его селектить. В нем должно быть автоинкрементное поле. Но все же давайте разберемся подробней.

Такие объекты (также называемыми генераторами последовательностей или просто последовательностями) являются специальными таблицами из одной строки и создаются командой CREATE SEQUENCE. Используются они обычно для получения уникальных идентификаторов строк таблицы. Функции, перечисленные в Таблице для работы с последовательностями, предоставляют простые и безопасные для параллельного использования методы получения очередных значений таких последовательностей.

Команда CREATE SEQUENCE создает генератор последовательности.

CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] имя
    [ AS тип_данных ]
    [ INCREMENT [ BY ] шаг ]
    [ MINVALUE мин_значение | NO MINVALUE ] [ MAXVALUE макс_значение | NO MAXVALUE ]
    [ START [ WITH ] начало ] [ CACHE кеш ] [ [ NO ] CYCLE ]
    [ OWNED BY { имя_таблицы.имя_столбца | NONE } ]

Эта операция включает создание и инициализацию специальной таблицы «имя», содержащей одну строку. Владельцем генератора будет пользователь, выполняющий эту команду.

Если указано имя схемы, последовательность создается в заданной схеме, в противном случае — в текущей. Временные последовательности существуют в специальной схеме, так что при создании таких последовательностей имя схемы задать нельзя. Имя последовательности должно отличаться от имен других последовательностей, таблиц, индексов, представлений или сторонних таблиц, уже существующих в этой схеме.

После создания последовательности работать с ней можно, вызывая функции nextval, currval и setval.

Хотя непосредственно изменить значение последовательности нельзя, получить ее параметры и текущее состояние можно таким запросом:

SELECT * FROM name;

В частности, поле last_value последовательности будет содержать последнее значение, выделенное для какого-либо сеанса. (Конечно, ко времени вывода это значение может стать неактуальным, если другие сеансы активно вызывают nextval.)

Примеры:

Создание возрастающей последовательности с именем serial, с начальным значением 101:

CREATE SEQUENCE serial START 101;

Получение следующего номера этой последовательности:

SELECT nextval('serial');

 nextval
---------
     101

Получение следующего номера этой последовательности:

SELECT nextval('serial');

 nextval
---------
     102

Использование этой последовательности в команде INSERT:

INSERT INTO distributors VALUES (nextval('serial'), 'nothing');

Изменение значения последовательности после COPY FROM:

BEGIN;
COPY distributors FROM 'input_file';
SELECT setval('serial', max(id)) FROM distributors;
END;

Для удаления последовательности применяется команда DROP SEQUENCE.

DROP SEQUENCE [ IF EXISTS ] имя [, ...] [ CASCADE | RESTRICT ]

Пример:

#Удаление последовательности serial

DROP SEQUENCE serial;

Важно понимать, что последовательности основаны на арифметике bigint, так что их значения не могут выходить за диапазон восьмибайтовых целых (-9223372036854775808 .. 9223372036854775807).

Так как вызовы nextval и setval никогда не откатываются, объекты последовательностей не подходят, если требуется обеспечить непрерывное назначение номеров последовательностей. Непрерывное назначение можно организовать, используя исключительную блокировку таблицы со счетчиком; однако это решение будет гораздо дороже, чем применение объектов последовательностей, особенно когда последовательные номера будут требоваться сразу многими транзакциями.

Если значение параметра «кеш» больше единицы, и объект последовательности используется параллельно в нескольких сеансах, результат может оказаться не вполне ожидаемым. Каждый сеанс будет выделять и кешировать несколько очередных значений последовательности при одном обращении к объекту последовательности и, соответственно, увеличивать последнее значение этого объекта. Затем при следующих кеш-1 вызовах nextval в этом сеансе будет просто возвращать заготовленные значения, не касаясь объекта последовательности. В результате, все числа, выделенные, но не использованные в сеансе, будут потеряны при завершении сеанса, что приведет к образованию «дырок» в последовательности.

Более того, хотя разным сеансам гарантированно выделяются различные значения последовательности, если рассмотреть все сеансы в целом, порядок этих значений может быть нарушен. Например, при значении кеша, равном 10, сеанс A может зарезервировать значения 1..10 и получить nextval=1, затем сеанс B может зарезервировать значения 11..20 и получить nextval=11 до того, как в сеансе A сгенерируется nextval=2. Таким образом, при значении кеш, равном одному, можно быть уверенными в том, что nextval генерирует последовательные значения; но если кеш больше одного, рассчитывать можно только на то, что все значения nextval различны; их порядок может быть непоследовательным. Кроме того, last_value возвращает последнее зарезервированное значение для всех сеансов, вне зависимости от того, было ли оно уже возвращено функцией nextval.

Также следует учитывать, что действие функции setval, выполненной для такой последовательности, проявится в других сеансах только после того, как в них будут использованы все предварительно закешированные значения.

Trigger

В СУБД PostgreSQL, да и в других СУБД тоже, иногда возникают ситуации, когда какие-либо запросы вам необходимо выполнять с завидной частотой, и вручную делать это со временем надоедает. Тогда в бой вступают триггеры.

Чтобы создать новый триггер, серверу СУБД необходимо отправить запрос CREATE TRIGGER. Триггер будет связан с указанной таблицей, представлением или сторонней таблицей и будет выполнять заданную функцию "function_name" при определенных операциях с этой таблицей.

Например, вот так:

# Создаем триггер с именем "check_update", который выполняет функцию check_account_update() перед любым изменением строк в таблице accounts:

CREATE TRIGGER check_update
    BEFORE UPDATE ON accounts
    FOR EACH ROW
    EXECUTE FUNCTION check_account_update();

Триггер является указанием, что база данных должна автоматически выполнить заданную функцию всякий раз, когда выполнен определенный тип операции. Триггеры можно использовать с таблицами (секционированными и обычными), представлениями и внешними таблицами.

Для обычных и сторонних таблиц можно определять триггеры, которые будут срабатывать до или после любой из команд INSERT, UPDATE или DELETE; либо один раз для каждой модифицируемой строки, либо один раз для оператора SQL. Триггеры на UPDATE можно установить так, чтобы они срабатывали, только когда в предложении SET оператора UPDATE упоминаются определенные столбцы. Также они могут срабатывать для операторов TRUNCATE. Если происходит событие триггера, для обработки этого события в установленный момент времени вызывается функция триггера.

Для представлений триггеры могут быть определены для выполнения вместо операций INSERT, UPDATE и DELETE. Такие триггеры INSTEAD OF вызываются единожды для каждой строки, которая должна быть изменена в этом представлении. Именно функция триггера отвечает за то, чтобы произвести необходимые изменения в нижележащих базовых таблицах представления и должным образом возвращать измененные строки, чтобы они появлялись в представлении. Триггеры для представлений тоже могут быть определены так, что они будут выполняться единожды для всего оператора SQL, до или после операций INSERT, UPDATE или DELETE. Однако они срабатывают, только если для представления определен триггер INSTEAD OF. В противном случае все операторы, обращающиеся к представлению, должны быть переписаны в виде операторов, обращающихся к нижележащим базовым таблицам, и тогда будут срабатывать триггеры, установленные для этих таблиц.

Триггерная функция должна быть создана до триггера. Она объявляется без аргументов и возвращает тип trigger. Триггерная функция получает данные на вход посредством специально переданной структуры TriggerData, а не в форме обычных аргументов.

После создания триггерной функции создается триггер с помощью CREATE TRIGGER. Одна и та же триггерная функция может быть использована для нескольких триггеров.

PostgreSQL предлагает как построчные, так и операторные триггеры. В построчном случае триггерная функция вызывается один раз для каждой строки, затронутой оператором, запустившим триггер. Операторный же триггер, напротив, вызывается только один раз при выполнении соответствующего оператора, независимо от количества строк, которые он затрагивает. В частности, оператор, который не затрагивает никаких строк, все равно приведет к срабатыванию операторного триггера. Эти два типа также называют триггерами уровня строк и уровня оператора, соответственно. Триггеры на TRUNCATE могут быть определены только на уровне оператора, а не на уровне строк.

Триггеры также классифицируются в соответствии с тем, срабатывают ли они до, после или вместо операции. Они называются BEFORE, AFTER и INSTEAD OF, соответственно. Триггеры BEFORE уровня оператора срабатывают до того, как оператор начинает делать что-либо, тогда как триггеры AFTER уровня оператора срабатывают в самом конце его работы. Эти типы триггеров могут быть определены для таблиц, представлений или сторонних таблиц. Триггеры BEFORE уровня строки срабатывают непосредственно перед обработкой конкретной строки, в то время как триггеры AFTER уровня строки срабатывают в конце работы всего оператора (но до любого из триггеров AFTER уровня оператора). Эти типы триггеров могут определяться только для таблиц, в том числе сторонних, но не для представлений. Триггеры INSTEAD OF могут определяться только для представлений и только на уровне строк: они срабатывают для каждой строки сразу после того, как строка представления идентифицирована как подлежащая обработке.

Оператор, нацеленный на родительскую таблицу в иерархии наследования или секционирования, не вызывает срабатывания триггеров уровня оператора для задействованных дочерних таблиц; срабатывать будут только такие триггеры для родительской таблицы. Однако если для этих дочерних таблиц установлены триггеры уровня строк, они будут срабатывать.

Триггерные функции, вызываемые триггерами операторов, должны всегда возвращать NULL. Триггерные функции, вызываемые триггерами строк, могут вернуть строку таблицы (значение типа HeapTuple). У триггера уровня строки, срабатывающего до операции, есть следующий выбор:

Можно вернуть NULL, чтобы пропустить операцию для текущей строки. Это указывает исполнителю запросов, что не нужно выполнять операцию со строкой, вызвавшей триггер (вставку, изменение или удаление конкретной строки в таблице).

Возвращаемая строка для триггеров INSERT или UPDATE будет именно той, которая будет вставлена или обновлена в таблице. Это позволяет триггерной функции изменять вставляемую или обновляемую строку.

Если в триггере BEFORE уровня строки не планируется использовать любой из этих вариантов, то нужно аккуратно вернуть в качестве результата ту же строку, которая была передана на вход (то есть строку NEW для триггеров INSERT и UPDATE, или строку OLD — для триггеров DELETE).

Триггер уровня строки INSTEAD OF должен вернуть либо NULL, чтобы указать, что он не модифицирует базовые таблицы представления, либо строку представления, полученную на входе (строку NEW для операций INSERT и UPDATE или строку OLD для операций DELETE). Отличное от NULL возвращаемое значение сигнализирует, что триггер выполнил необходимые изменения данных в представлении. Это приведет к увеличению счетчика количества строк, затронутых командой.

Возвращаемое значение игнорируется для триггеров уровня строки, вызываемых после операции, поэтому они могут возвращать NULL.

Полезные ссылки:

• Представление (VIEW)
• Оконные функции
• CREATE FUNCTION
• SQL-FUNCTIONS
• CREATE PROCEDURE
• CREATE SEQUENCE
• CREATE TRIGGER

Правила выполнения задания:

1. Время создания окружения занимает до 5 минут.
2. После нажатия кнопки «Начать выполнение» для вас будет создано окружение для выполнения задания, состоящее из одной или более виртуальнах машин с Ubuntu Linux. К ним будут выданы IP, логин и пароль для доступа по SSH, а также при необходимости — дополнительных ресурсов.
3. Также, если необходимо, вам могут быть выданы переменные, которые в задании указаны в фигурных скобках, — их надо будет подставить при выполнении задания (например, ${base_domain}).
4. После выполнения всех пунктов задания нажмите кнопку «Проверить выполнение», и в течение 3-5 минут скрипт проверит выполнение всех условий и выставит вам оценку.
5. В случае, если вы что-то забыли, можно исправить ошибку и отправить на проверку повторно (нажав кнопку «Проверить выполнение»).
6. После получения удовлетворительной оценки нажмите кнопку «Завершить задание», чтобы созданное окружение уничтожилось и вы могли приступить к следующему заданию.
7. Если у вас закончилось время (истек таймер) — окружение будет автоматически уничтожено и вам придется начать выполнение заново.
8. Также, если вы успешно сдали задание, но у вас остались вопросы — вы всегда сможете задать их куратору после проверки (используя кнопку «Задать вопрос куратору») или в чате практикума в любое удобное для вас время. Обращаем внимание — кураторы проверяют вопросы в течение 24 часов.

Задание:

1. Установите postgresql v13 на предоставленной VM c OS Ubuntu 20.04.
2. Добавьте в PostgreSQL пользователя root c правами SUPERUSER.
3. Создайте базу данных "rebrain_courses_db".
4. Для работы с базой данных создайте пользователя "rebrain_admin".
5. Выдайте все права пользователю "rebrain_admin" на базу данных "rebrain_courses_db".
6. Переместите файл бэкапа базы данных "rebrain_courses_db" из предыдущего задания на сервер и положите в директорию /tmp/ с именем rebrain_courses_db.sql.bqp.
7. Восстановите данные из бэкапа базы данных из предыдущего задания командой: psql -U root -d rebrain_courses_db -f /tmp/rebrain_courses_db.sql.bqp
8. C помощью утилиты psql, подключитесь к базе данных "rebrain_courses_db".
9. Проверьте наличие данных в таблицах, чтобы понять, что бекап данных прошел успешно.
10. Посчитайте общую стоимость практикумов компании REBRAIN из таблицы courses с помощью оконной функции sum(price) OVER (), результат сохраните в файл /tmp/answers/devops_old_price (используйте запрос SELECT * FROM ...).
11. Обновите данные цены практикума в таблице №2 для практикума "Devops". Новая цена: 100000 руб.
12. Посчитайте общую стоимость практикумов компании REBRAIN из обновленной таблицы courses с помощью оконной функции sum(price) OVER (ORDER BY price), результат сохраните в файл /tmp/answers/devops_new_price (используйте запрос SELECT * FROM ...).
13. Добавьте новую таблицу auditlog с NOT NULL полями:

• id (PrimaryKey)
• user_id (id пользователя, который был создан)
• creation_time (время создания записи о новом пользователе)
• creator (имя пользователя базы данных, с помощью которого производился insert)

14. Создайте функцию c именем "fnc_auditlog_users_insert", которая логирует в таблицу (записывает в таблицу) auditlog информацию о регистрации нового пользователя на сайте компании REBRAIN.
15. Создайте и установите триггер "insert_into_users_trigger" на INSERT данных в таблицу users так, чтобы вызывалась функция c именем "fnc_auditlog_users_insert", которую вы создали выше.
16. С помощью команды INSERT, добавьте в таблицу "users" 15 новых пользователей. Для каждого нового пользователя делайте отдельный запрос, так чтобы срабатывал установленный триггер.
17. Создайте представление c именем "get_last_10_records_from_auditlog", которое позволит вывести из таблицы auditlog последних 10 попыток записи в таблицу users за последний день с сортировкой по времени (select * from auditlog limit 10 сортировка по времени).
18. Сделайте бекап базы с помощью команды: pg_dump -U root rebrain_courses_db > /tmp/rebrain_courses_db_task03.sql.bqp
19. Если уверены, что все выполнили правильно, оправляйте задание на проверку.