Массивы и триггеры

Массивы

Нумерация элементов массива начинается с единицы.

Напишем функцию, принимающую на вход массив целых чисел и возвращающую сумму элементов массива.

CREATE OR REPLACE FUNCTION find_summa_of_array (my_array INTEGER ARRAY) RETURNS INTEGER AS $$
    DECLARE summa_of_array INTEGER;
    DECLARE length_of_array INTEGER;
BEGIN
    length_of_array = array_length(my_array, 1);
    summa_of_array = 0;
    FOR i IN 1..length_of_array LOOP
      summa_of_array = summa_of_array + my_array[i];
    END LOOP;
    RETURN summa_of_array;
END;

$$ LANGUAGE plpgsql;

SELECT find_summa_of_array( ARRAY [10, 30, 40, 20, 25] ) ;

Создадим таблицу для хранения информации о товарах.

CREATE TABLE fruits (fruit_id SERIAL PRIMARY KEY, fruit_name TEXT, fruit_price INTEGER);

Заполним созданную таблицу.

INSERT INTO fruits (fruit_name, fruit_price) VALUES ('Apples', 25);
INSERT INTO fruits (fruit_name, fruit_price) VALUES ('Oranges', 87);
INSERT INTO fruits (fruit_name, fruit_price) VALUES ('Bananas', 44);
INSERT INTO fruits (fruit_name, fruit_price) VALUES ('Pears', 72);

Напишем функцию, рассчитывающую общую сумму цен всех товаров.

CREATE OR REPLACE FUNCTION get_sum_of_fruit_price () RETURNS INTEGER AS $$
    DECLARE my_array_of_prices INTEGER ARRAY;
    DECLARE my_array_length INTEGER;
    DECLARE answer_sum_price INTEGER;
    DECLARE my_fruit RECORD;
BEGIN
    my_array_length = 0;
    FOR my_fruit IN SELECT * FROM fruits LOOP
        my_array_length = my_array_length + 1;
        my_array_of_prices[my_array_length] = my_fruit.fruit_price;
    END LOOP;
    answer_sum_price = find_summa_of_array(my_array_of_prices);
    RETURN answer_sum_price;
END;

$$ LANGUAGE plpgsql;

SELECT get_sum_of_fruit_price ();

Создадим таблицу для хранения имён пользователей.

CREATE TABLE nicknames (nickname TEXT PRIMARY KEY);

Напишем функцию для добавления массива пользователей в таблицу.

CREATE OR REPLACE FUNCTION add_users_to_table (users_array TEXT ARRAY) RETURNS BOOLEAN AS $$
    DECLARE my_user TEXT;
    DECLARE iter INTEGER;
    DECLARE len_of_arr INTEGER;
BEGIN
    len_of_arr = array_length(users_array, 1);
    FOR iter IN 1..len_of_arr LOOP
       my_user = users_array[iter];
       INSERT INTO nicknames (nickname) VALUES (my_user);
    END LOOP;
    RETURN True;
END;

$$ LANGUAGE plpgsql;

Вызываем данную функцию для добавления записей.

SELECT add_users_to_table ( ARRAY ['a1','a2','a3','a4'] );

Попробуем добавить ещё несколько записей.

SELECT add_users_to_table ( ARRAY ['k1','k2','k3','a2','k4','k5'] );

Ни одна из записей не будет добавлена.

Транзакция не выполняется, так как запись a2 уже есть в таблице.

Триггеры

Создадим таблицу для хранения имён и возрастов людей.

CREATE TABLE people (man_id SERIAL PRIMARY KEY, man_name TEXT, man_age INTEGER);

Создадим процедуру, которая будет всегда вызываться перед добавлением записи в таблицу.

При попытке добавить человека с отрицательным возрастом процедура должна отменять добавление и выдавать сообщение об ошибке.

CREATE OR REPLACE FUNCTION my_control_age_of_man () RETURNS TRIGGER AS $tttt$
BEGIN
    IF NEW.man_age < 0 THEN
       RAISE EXCEPTION 'no correct age of man';
    END IF;

    RETURN NEW;
END;

$tttt$ LANGUAGE plpgsql;

CREATE TRIGGER tttt BEFORE INSERT ON people FOR EACH ROW EXECUTE PROCEDURE my_control_age_of_man ();

Добавим записи в таблицу.

INSERT INTO people (man_name, man_age) VALUES ('Maxim', 22);
INSERT INTO people (man_name, man_age) VALUES ('Nina', 25);
INSERT INTO people (man_name, man_age) VALUES ('George', -123);

Первые две записи будут добавлены.

Третья запись НЕ будет добавлена, так как возраст человека отрицательный.

Удаление процедур и триггеров

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

psql \df

Для удаление функции пишется команда:

DROP FUNCTION IF EXISTS get_sum (a INTEGER, b INTEGER);

Для удаления триггера и процедуры, привязанной к нему, пишутся команды:

DROP TRIGGER IF EXISTS tttt ON people;
DROP FUNCTION IF EXISTS my_control_age_of_man ();

Для просмотра списка триггеров можно вбить команду:

select * from pg_trigger;