Очень краткое руководство по PostgreSQL и PL/pgSQL


Сайт автора данного руководства: http://snakeproject.ru/


Писалось в качестве справочника - записок для самого себя


Взять дистрибутивы можно с оф. Сайта: https://www.postgresql.org/

Либо есть вариант разработчиков из РФ: https://postgrespro.ru/


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


Хочу отметить труд по документации на русском языке команды PostgresPRO - https://postgrespro.ru/docs


Для доступа к серверу и БД можно использовать графический инструмент pgAdmin: https://www.pgadmin.org/

Или EMS SQL Manager for PostgreSQL: https://www.sqlmanager.net/products/postgresql/Manager



Кратко о PostgreSQL


PostgreSQL — реляционная база данных с открытым исходным кодом


В реляционной базе данных информация хранится в виде таблиц с колонками и строками


Схема работы PostgreSQL приложения такова:

1. Процесс "POSTMASTER" на сервере получает запрос на подключение

2. При успешной прохождении проверки POSTMASTER создаёт свою копию

3. Далее взаимодействие с базой данных клиента идет уже через копию


После установки(Настройка кластера баз данных)


На Windows инсталляционный пакет скорей всего пропишет все автоматом для запуска кластера в зависимости от указанной директории, но можно настроить кластер с помощью утилит ниже описанных initdb и pg_ctl, они будут в каталоге bin, например - D:\Postgres\bin


После установки PostgreSQL нужно создать кластер баз данных

В данном случае это набор баз, которые будут управляются одним экземпляром сервера

Один экземпляр PostgreSQL может запускать и контролировать набор баз данных, которые изолированы друг от друга, но обслуживаются через один и тот же сокет TCP/IP или UNIX


Можно воспользоваться вариантом создания кластера с помощью:

initdb — создает новый PostgreSQL кластера баз данных

Синтаксис- initdb [ option …] [ –pgdata | -D ] directory


Желаемое месторасположение кластера БД указывается опцией -D :

$ sudo postgres

$ initdb -D /usr/local/pgsql/data


Либо так:

$ sudo postgres

$ pg_ctl -D /usr/local/pgsql/data initdb


После этого можно будет попробовать запустить сервер - sudo service postgresql start


Либо есть варианты


Запустить postmaster в активном режиме:

$ postmaster – D /usr/local/pgsql/data


Запуск в фоновом режиме с помощью pg_ctl:

$ pg_ctl – D /usr/local/pgsql/data – 1 /tmp/postgresql.log start


Примеры действий pg_ctl:


Старт сервера:

$ pg_ctl start


Остановка сервера:

$ pg_ctl stop


Перезапуск сервера:

$ pg_ctl restart


Статус сервера:

$ pg_ctl status


Утилита psql


В стандартной поставке с сервером с пакетом postgresql-client для администрирования идет утилита psql, приведу несколько примеров ее функционала (примеры для Windows и Linux схожи)


В Linux - su – postgres, Windows – cmd – psql


psql --help - справка


Некоторые аргументы утилиты:

psql -l — выведет список баз данных

psql -d database - подключиться к базе данных с именем «database»

psql -f script.sql - исполнить SQL скрипт «script.sql»

psql -d test -H -c "SELECT * FROM students" -o D:\Postgres\f.html — результат вывода в файл


Вывод списка конфигурационных файлов:

psql > SELECT name, setting FROM pg_settings WHERE category = 'File Locations';


Вывод списка активных сессий:

psql > SELECT * FROM pg_stat_activity;


Убить сессию:

--Узнаем id сесссии

SELECT datname as database,

pid,

usename as username,

application_name as application,

client_addr as client_address,

query

FROM pg_stat_activity;


--Указываем id сессии и БД

SELECT pg_terminate_backend(id сессии)

FROM pg_stat_activity

WHERE datname = 'БД';


Утилита pgdump(резервная копия одной базы)


Справка — pg_dump –help


Синтаксис: pg_dump −Fc "база данных" > "бэкап"

Пример: pg_dump -Fc "test" > "D:\Postgres\testBackup.sql"


Пример бэкапа данных для определенной таблицы "table_test":

pg_dump -a -t table_test -f file_test.sql database_test


Некоторые опции:

-h host - адрес хоста

-p port - порт хоста (по умолчанию 5432)

-u - логин

-a, --data-only - дамп данных без схемы

-s, --schema-only - дамп только схемы

-C, --create - добавит команду создания базы данных

-c - добавит команды удаления (drop) объектов

-t, --table=TABLE - определенная таблица для создания дампа


Утилита pgdumpall(резервная копия всех баз)


pg_dumpall > bases.sql


Утилиты psql и pgrestore(восстановление резервных копий)


psql - восстановит бэкапы из текстовых фалов типа plain text

pg_restore - восстановит бэкапы из архивов типа tar


Восстановление всего бекапа с игнорированием ошибок:

psql -h localhost -U Login -d DBName -f backup.sql


Восстановление из архива:

pg_restore -C -d postgres db.dump


-C или --create

Создать базу данных, прежде чем восстанавливать данные.

Если также указан параметр --clean, удалить и пересоздать целевую базу данных перед подключением к ней.

Все данные восстанавливаются в базу данных, имя которой записано в архиве.



О конфигурационных файлах


postmaster.opts — содержит строку запуска с параметрами для СУБД PostgreSQL

Например так: D:/Postgres/bin/postgres.exe "-D" "D:\Postgres\data"
Этот путь - «D:\Postgres\data» будет в переменной $PGDATA


pg_hba.conf — отвечает за доступ пользователей(создается автоматически при выполнении команды initdb)

По умолчанию PostgreSQL разрешает подключаться локальному пользователю с совпадающим названием БД с регистрационным именем клиента, если такая база данных существует


Для подключения к базе данных по сети необходимо настроить listen_addresses в postgresql.conf


Пара примеров


Доступ всем локально по совпадению логина и имени БД по паролю:

host all all 127.0.0.1/32 md5


Доступ к базе test логину mike с адреса 1.1.1.1 на доверии:

host test mike 1.1.1.1/32 trust


postgresql.conf — отвечает за настройки сервера


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

listen_addresses - прослушиваемый адрес

port - прослушиваемый порт

max_connections - максимально соединений

superuser_reserved_connections - зарезервированые соединения для суперпользователя


Логи обычно хранятся в директории «pg_log»


Включает логирование действий:

logging_collector = on


Место хранения логов:

log_destination = 'stderr'


Частота ротации логов:

log_rotation_age = 1d


Размер ротации логов:

log_rotation_size = 10MB


Формат файла логов:

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'


Перезапись ротационных логов:

log_truncate_on_rotation = on


Интересные команды


Посмотреть активность в Linux с задержкой в 3 секунды

watch -n 3 'ps auxww | grep ^postgres'


Выше обратите внимение на вывод, для каждого пользователя создается свой экземпляр POSTMASTER, можно увидеть что-то типа postgres: "пользователь" "база" "хост" "статус":

Родительский процесс - /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data

Процесс пользователя - postgres: postgres mike 127.0.0.1(37047) idle


Базы данных и журналы транзакций


По умолчанию создается БД postgres, template0 и template1

В БД postgres менять что-либо не рекомендуется, используется самим PostgreSQL

В БД template0 категорически нельзя что-либо менять

В БД template1 позволяется вносить изменения


Каждая таблица\индексы\blob т.п. хранятся как файл или несколько файлов в каталоге БД


Посмотреть, где хранятся БД — psql - show data_directory;


Базы в PostgreSQL имеют ID узнать можно так:

SELECT oid from pg_database WHERE datname = 'test';


Это число в виде папки мы видим в папке data/base — соответствует базе


ID таблицы можно найти так(подключитесь к самой БД psql -d test):

SELECT relname, relfilenode FROM pg_class WHERE relname = 'students';


Это число уже будет именем файла внутри каталога базы


В WIndows $PGDATA по умолчанию указывает в каталог "data" указанный при установке или инициализации кластера в строке параметра в postmaster.opts


pg_xlog


В postgresql.conf за размер логов отвечает параметр - max_wal_size


Путь - $PGDATA/pg_xlogWindows для PostgresPRO будет в D:\Postgres\data\pg_wal)


По завершении транзакций PostgreSQL записывает данные в журнал транзакций WAL - Write-ahead log, а уже после в саму базу данных


Сами файлы выглядят типа - '0000000100000000000000A2', которые содержат образы данных последних транзакций. Эти журналы также используются при бинарной репликации


Нельзя удалять\перемещать эти данные, возможно приведет к невосстановимому повреждению базы данных


Каталог может разростись при использовании репликации и падении SLAVE при настроенной репликации


pg_clog


Путь - $PGDATA/pg_clog содержит журналы метаданных транзакций, записывает информацию о завершении\незавершении транзакции

Врядли когда-то увеличится, но ни в коем случае удалять файлы оттуда нельзя, без них восстановить базы данных не выйдет


Об SQL



Приведем список основных команд SQL


В PostgreSQL поддерживаются все основные команды\операторы языка SQL


Ниже приведена таблица с кратким описанием операторов:




Права и роли (DCL – Data Control Language)



После установки был создан пользователь postgres с указанным паролем


Логинимся (для Linux: sudo su — postgres)


Сама PostgreSQL управляет доступом при помощи ролей

Посмотреть роли - зайти в psql - набрать \du


Будет что-то похожее:

List of roles

Role name | Attributes | Member of

-----------+------------------------------------------------+-----------

postgres | Superuser, Create role, Create DB, Replication | {}


Для Windows ОС может возникнуть потребность корректно отображать кодировки, необходимо будет дать команду:

chcp 1251


Создание базы данных:

CREATE DATABASE test;


Создать новую роль:

CREATE ROLE test_role;


Удалить роль можно так:

DROP ROLE test_role;


Посмотреть опции для создания роли можно так:

\h CREATE ROLE


Пример с опцией - логин, связанный с ролью, будет иметь привилегии соединения:

CREATE ROLE test_role WITH LOGIN;



Создать нового пользователя можно с помощью команды:

CREATE USER test_user;

Обратите внимание, что вместе с пользователем создастся роль test_user с привилегией соединения


Задать пароль пользователю:

\password test_user


или с помощью sql:

CREATE USER test_user WITH password 'password';


Удаление пользователя:

DROP USER IF EXISTS test_user;


Добавить пользователю роль можно так - GRANT test TO test_user;


Теперь зададим пользователю test_user права на базу test с помощью роли:

Входим в базу - \c test


Даем все привелегии роли:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO GROUP test_user;


Или особые привелегии:

GRANT SELECT, UPDATE, INSERT ON ALL TABLES IN SCHEMA public TO test_user;


Выходим - \q


Проверяем досту под пользователем - psql -U test_user -d test -h 127.0.0.1 -W


Сделайте запрос, например: select * from students


Забрать привилегии роли можно так:

REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM GROUP test_user;


По аналогии с самим пользователем:

\c test

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO test_user;

REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM test_user;


Сделать владельцем БД, разрешить создание ил подключение на уровне БД можно с помощью команд:

ALTER DATABASE test OWNER TO test_user;

GRANT TEMP ON DATABASE test TO test_user WITH GRANT OPTION;

GRANT CREATE ON DATABASE test TO test_user WITH GRANT OPTION;

GRANT CONNECT ON DATABASE test TO test_user;


Права для схемы (владелец\использование\создание) назначить можно так:

ALTER SCHEMA public OWNER TO test_user;

GRANT USAGE ON SCHEMA public TO test_user;

GRANT CREATE ON SCHEMA public TO test_user;

Пароли пользователей хранятся в системной таблице:

SELECT * FROM pg_shadow;



Типы данных



Преобразование строковой константы к другому типу:

Значение:: Тип

CAST (Значение AS Тип)


Примеры:

SELECT pg_typeof( CAST ('50' AS INTEGER) );

SELECT pg_typeof( '50'::INTEGER );


SELECT pg_typeof( CAST (50 AS VARCHAR) );

SELECT pg_typeof( 50::VARCHAR );


В PostrgreSQL есть довольно широкий выбор из типов данных


Основная часть — типы языка SQL


Присутствуют и типы только самой PostgreSQL


Некоторые типы данных имеют синонимы


При переписи кода на другие БД могут возникнуть проблемы



В интернете есть ряд утилит для переноса структуры и данных между разными СУБД


Можно определять собственные типы с помощью CREATE TYPE



Приведена таблица типов данных c описанием:




DDL - data definition language (язык описания данных)



--Пример создания таблицы:

CREATE TABLE Table1

( id INTEGER,

name CHAR(25) );



--Добавление столбца

ALTER TABLE Table1 ADD lastname VARCHAR(30) NOT NULL;

--Добавление столбца с ограничением уникальности

ALTER TABLE Table1 ADD address VARCHAR(50) NOT NULL CONSTRAINT address_unique UNIQUE;


--Удаление ограничения

ALTER TABLE Table1 DROP CONSTRAINT address_unique;


--Переименование столбца и изменение типа данных

ALTER TABLE Table1 ALTER COLUMN lastname TYPE CHAR(20);


--Удаление столбца

ALTER TABLE Table1 DROP COLUMN address ;


--Пример создания неуникального индекса:

CREATE INDEX Tname ON Table1 (name);


--Пример создания уникального индекса:

CREATE UNIQUE INDEX Tid ON Table1 (id);


--Удаление индекса:

DROP INDEX Tid;


-- Удаление таблицы

DROP TABLE Table1


--Создание таблицы с первичным ключом(Столбцы первичного ключа уже имеют индекс):

CREATE TABLE Table1

( id INTEGER,

name CHAR(25),

PRIMARY KEY (id));


--Создание таблиц с первичными ключами и связями(отношениями):

CREATE TABLE firsttable

( ID INT NOT NULL PRIMARY KEY,

name VARCHAR (50) NULL,

lastname VARCHAR (100) NULL );


CREATE TABLE secondtable

( ID INT NOT NULL PRIMARY KEY,

sname VARCHAR (50) NULL,

slastname VARCHAR (100) NULL,

mainID INT NOT NULL,

CONSTRAINT FK_ToFirsttable FOREIGN KEY (mainID)

REFERENCES firsttable (ID) );


--Удалить отношение(связь):

ALTER TABLE secondtable DROP CONSTRAINT FK_ToFirsttable;


--Задать отношение(связь) можно после, по аналогии с предыдущей таблицей:

ALTER TABLE secondtable ADD CONSTRAINT FK_ToFirsttable FOREIGN KEY (mainID) REFERENCES firsttable (ID);



DML - язык манипулирования данными


--Добавим строки в таблицы

INSERT INTO firsttable(ID,name, lastname)

VALUES ('1','name1', 'lastname1');

INSERT INTO firsttable(ID,name, lastname)

VALUES ('2','name2', 'lastname2');

INSERT INTO firsttable(ID,name, lastname)

VALUES ('3','name3', 'lastname3');

INSERT INTO secondtable(ID,sname, slastname,mainID)

VALUES ('1','name1', 'lastname1','1');

INSERT INTO secondtable(ID,sname, slastname,mainID)

VALUES ('2','name2', 'lastname2','2');

INSERT INTO secondtable(ID,sname, slastname,mainID)

VALUES ('3','name3', 'lastname3','3');


--Удалим данные

DELETE FROM secondtable WHERE ID = 3;


--Изменим данные

UPDATE firsttable SET name = 'name3_New' WHERE ID = 3;


--Очистим полностью таблицы:

TRUNCATE TABLE firsttable, secondtable;



Оператор SELECT


--Конструкция SELECT:

--1 - SELECT (DISTINCT LIMIT)

--2 - FROM

--3 - WHERE

--4 - GROUP BY

--5 - HAVING

--6 - ORDER BY


--Последовательность логической обработки SELECT

--1 - FROM

--2 - WHERE

--3 - GROUP BY

--4 - HAVING

--5 - SELECT (DISTINCT LIMIT)

--6 - ORDER BY


--(DISTINCT LIMIT) уникальные или ограничения вывода количества записей

--FROM указывает цель запроса (например таблица)

--WHERE указывает условия отбора

--GROUP BY определяет поле для группировки

--ORDER BY определяет поле для сортировки, ASC - по возрастанию,DESC - по убыванию


Для примеров введем обратно данные в обе таблицы из предыдущей главы DDL:


TRUNCATE TABLE firsttable, secondtable;


INSERT INTO firsttable(ID,name, lastname)

VALUES ('1','name1', 'lastname1');

INSERT INTO firsttable(ID,name, lastname)

VALUES ('2','name2', 'lastname2');

INSERT INTO firsttable(ID,name, lastname)

VALUES ('3','name3', 'lastname3');

INSERT INTO firsttable(ID,name, lastname)

VALUES ('4','name4', 'lastname4');

INSERT INTO firsttable(ID,name, lastname)

VALUES ('5','name5', 'lastname5');

INSERT INTO firsttable(ID,name, lastname)

VALUES ('6','name6', 'lastname6');

INSERT INTO secondtable(ID,sname, slastname,mainID)

VALUES ('1','name1', 'lastname1','1');

INSERT INTO secondtable(ID,sname, slastname,mainID)

VALUES ('2','name2', 'lastname2','2');

INSERT INTO secondtable(ID,sname, slastname,mainID)

VALUES ('3','name3', 'lastname3','3');

INSERT INTO secondtable(ID,sname, slastname,mainID)

VALUES ('4','name3_2', 'lastname3_2','3');

INSERT INTO secondtable(ID,sname, slastname,mainID)

VALUES ('5','name3_2_3', 'lastname3_2_3','3');

INSERT INTO secondtable(ID,sname, slastname,mainID)

VALUES ('6','name1_2_New', 'lastname3_2_New','3');

INSERT INTO secondtable(ID,sname, slastname,mainID)

VALUES ('7','name3', NULL,'3');


--Пример запроса (без таблицы)

SELECT 1, 25 - 7, 'Test' AS word;


--Определенное число записей в выводе

SELECT * FROM firsttable limit 2;


--Вывод уникальных значений

SELECT DISTINCT mainid FROM secondtable;


--Вывод с условием

SELECT * FROM firsttable WHERE id > 1 AND id < 4;


--Вывод с сортировкой по столбцу

SELECT * FROM firsttable ORDER BY id DESC;


--Вывод с группировкой по столбцам (в данном примере функция COUNT - считает записи, а не определенный столбец)

SELECT mainid, COUNT(*) FROM secondtable GROUP BY mainid;


--Вывод с группировкой по столбцам и HAVING - условие для группировки

SELECT mainid, COUNT(*) FROM secondtable GROUP BY mainid HAVING mainid IN (2,3);


--Вывод с проверкой на "пустые"\"непустые" значения NULL\NOT NULL

SELECT * FROM secondtable WHERE slastname IS NULL;

SELECT * FROM secondtable WHERE slastname IS NOT NULL;


--Подзапросы

SELECT f.name, (SELECT s.slastname FROM secondtable AS s WHERE f.id = s.mainid limit 1) AS slastname FROM firsttable as f;


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

https://postgrespro.ru/docs/postgrespro/10/functions

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


И конечно рассмотрим примеры с триггерами, функциями, программированием и представлениями.


Соединения — JOIN


Выборка из нескольких таблиц с соединением по значениям


--Внутреннее соединение (общие совпадающие id - mainid)

SELECT

A.id AS "id from firsttable"

,B.mainid AS "mainid from secondtable"

,A.name AS "name from firsttable"

,B.slastname AS "slastname from secondtable"

FROM

firsttable AS A INNER JOIN secondtable AS B

ON A.id = B.mainid;


--Соединение ко всем значениям левой таблицы

SELECT

A.id AS "id from firsttable"

,B.mainid AS "mainid from secondtable"

,A.name AS "name from firsttable"

,B.slastname AS "slastname from secondtable"

FROM

firsttable AS A LEFT JOIN secondtable AS B

ON A.id = B.mainid;




--Соединение ко всем значениям правой таблицы

SELECT

A.id AS "id from firsttable"

,B.mainid AS "mainid from secondtable"

,A.name AS "name from firsttable"

,B.slastname AS "slastname from secondtable"

FROM

firsttable AS A RIGHT JOIN secondtable AS B

ON A.id = B.mainid;

--Полное соединение

SELECT

A.id AS "id from firsttable"

,B.mainid AS "mainid from secondtable"

,A.name AS "name from firsttable"

,B.slastname AS "slastname from secondtable"

FROM

firsttable AS A FULL JOIN secondtable AS B

ON A.id = B.mainid;

--Перекрестное соединение

SELECT

A.id AS "id from firsttable"

,B.mainid AS "mainid from secondtable"

,A.name AS "name from firsttable"

,B.slastname AS "slastname from secondtable"

FROM

firsttable AS A CROSS JOIN secondtable AS B;


--Соединение с условием

SELECT

A.id AS "id from firsttable"

,B.mainid AS "mainid from secondtable"

,A.name AS "name from firsttable"

,B.slastname AS "slastname from secondtable"

FROM

firsttable AS A INNER JOIN secondtable AS B

ON A.id = B.mainid

WHERE A.name <> 'lastname3' and B.slastname <> 'lastname3';


Sequence - последовательности


Последовательность или sequence в PostgreSQL - объект БД, представляет автоматически увеличивающееся число


INSERT INTO public.firsttable(name, lastname) VALUES('name_sequence1','lastname_sequence1');

Выдаст из-за первичного ключа, его необходимо добавлять явно

ОШИБКА: нулевое значение в столбце "id" нарушает ограничение NOT NULL

DETAIL: Ошибочная строка содержит (null, name_sequence1, lastname_sequence1).


--Назначим автоинкремент

CREATE SEQUENCE firsttable_sec_id_seq

INCREMENT BY 1

NO MAXVALUE

MINVALUE 0;


--Создание таблицы с последовательностью

CREATE TABLE public.firsttable_sec (

id integer DEFAULT nextval('firsttable_sec_id_seq') NOT NULL,

name VARCHAR(50) NULL,

lastname VARCHAR(100) NULL

);

INSERT INTO public.firsttable_sec(name, lastname) VALUES('name_sequence1','lastname_sequence1');

INSERT INTO public.firsttable_sec(name, lastname) VALUES('name_sequence2','lastname_sequence2');

SELECT * FROM public.firsttable_sec;


--Добавление последовательности для столбца существующей таблицы

ALTER TABLE public.firsttable ALTER COLUMN id SET DEFAULT nextval('firsttable_sec_id_seq');


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

SELECT setval('firsttable_sec_id_seq', (SELECT max(id) FROM public.firsttable));


--Теперь не вызовет ошибки

INSERT INTO public.firsttable(name, lastname) VALUES('name_sequence1','lastname_sequence1');


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

ALTER TABLE public.firsttable ALTER COLUMN id DROP DEFAULT;


Хранимые функции



Хранимые процедуры в PostgreSQL как таковые отсутствуют, их заменяют хранимые функции


Синтаксис выглядит приблизительно так:

CREATE [OR REPLACE] FUNCTION Имя_Функции (Аргументы_функции)

RETURNS return_datatype AS $variable_name$

DECLARE

declaration;

[...]

BEGIN

< Тело_Функции >

[...]

RETURN { variable_name | value }

END; LANGUAGE { plpgsql, sql, C };


Имя_Функции - имя создаваемой функции

Тело_Функции - исполняемый код в самой функции

Аргументы_функции - необязательно, могут присутствовать принимаемые аргументы

Необязательная опция [OR REPLACE] позволяет изменять уже существующую функцию

RETURN - обязательное возвращаемое значение

Ключевое слово AS указывает на создание автономной функции

plpgsql, C или sql - название языка для функции


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

Когда выходных параметров несколько, используется RETURNS SETOF record

Когда только один, используется RETURNS SETOF указанный_тип


Ключевые слова IMMUTABLE | STABLE | VOLATILE (используется по умолчанию, если явно не указано) - информируют оптимизатор запросов о поведении функции


IMMUTABLE (постоянная) - говорит, что функция не может модифицировать базу данных и всегда возвращает один и тот же результат при определённых значениях аргументов

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

Если функция имеет такую характеристику, любой её вызов с аргументами-константами можно немедленно заменить значением функции


STABLE (стабильная) - говорит, что функция не может модифицировать БД и в рамках одного сканирования таблицы всегда возвращает один и тот же результат для определённых значений аргументов

Этот результат может быть разным в разных операторах SQL

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

Но этот вариант не подходит для триггеров AFTER, желающих прочитать строки, изменённые текущей командой

Также заметьте, что функции семейства current_timestamp также считаются стабильными, так как их результаты не меняются внутри транзакции


VOLATILE (изменчивая) - говорит, что результат функции может меняться даже в рамках одного сканирования таблицы, так что её вызовы нельзя оптимизировать

Изменчивы в этом смысле относительно немногие функции баз данных, например: random(), currval() и timeofday()

Но заметьте, что любая функция с побочными эффектами должна быть классифицирована как изменчивая, даже если её результат вполне предсказуем, чтобы её вызовы не были соптимизированы

Пример такой функции: setval()


--Функция на чистом языке SQL, вариант - SETOF

CREATE OR REPLACE FUNCTION sel_name_from_firsttable()

RETURNS SETOF record AS '

SELECT id, name FROM firsttable;

' LANGUAGE sql;


--Вызов функции с выводом в один столбец

SELECT sel_name_from_firsttable();


--Вызов функции с выводом в разные столбцы

select a, b from sel_name_from_firsttable() f(a int, b varchar(50));


--Удалить функцию

DROP FUNCTION sel_name_from_firsttable();



$$ - символы квотирования, указывают, что между ними будет текст на каком-то языке, в данном ниже случае - LANGUAGE sql


Т.е. В примере ниже код написан на чистом SQL


--Функция на чистом языке SQL с табличным, возвращаемым значением

CREATE FUNCTION sel_name_from_firsttable()

RETURNS TABLE(id int, name varchar(50)) AS $$

SELECT id, name FROM firsttable;

$$ LANGUAGE sql;


--Вызов функции

SELECT * FROM sel_name_from_firsttable();



--Функция на plpgsql с возвращением числовой переменной и принимаемыми аргументами

CREATE FUNCTION count_sum(foo1 integer, foo2 integer)

RETURNS integer AS $total$

DECLARE

total integer;

BEGIN

SELECT foo1 + foo2 INTO total;

RETURN total;

END;

$total$ LANGUAGE plpgsql;


SELECT count_sum(2, 5);


--Пример функции без выходных аргументов(подобно хранимой процедуре), указаоно ключевое слово void, с обработкой ошибки, коды ошибок можно посмотреть тут - https://postgrespro.ru/docs/postgrespro/10/errcodes-appendix :


CREATE OR REPLACE FUNCTION public.func_ins (

foo_id integer,

foo_name varchar,

foo_lastname varchar

)

RETURNS void AS'

BEGIN

INSERT INTO firsttable(id, name, lastname) VALUES (foo_id, foo_name, foo_lastname);

EXCEPTION

WHEN others THEN

RAISE NOTICE ''Невозможно добавить строку! SQLSTATE: %'', SQLSTATE;

RAISE;

END;

'LANGUAGE 'plpgsql'

VOLATILE

CALLED ON NULL INPUT;


--Добавляем строки, произойдет ошибка по уникальности на второй записи

SELECT func_ins(7, 'name7', 'lastname7');

SELECT func_ins(7, 'name7', 'lastname7');

ЗАМЕЧАНИЕ: Невозможно добавить строку! SQLSTATE: 23505

ОШИБКА: повторяющееся значение ключа нарушает ограничение уникальности "firsttable_pkey"

DETAIL: Ключ "(id)=(7)" уже существует.

CONTEXT: SQL-оператор: "INSERT INTO firsttable(id, name, lastname) VALUES (foo_id, foo_name, foo_lastname)"

функция PL/pgSQL func_ins(integer,character varying,character varying), строка 3, оператор SQL-оператор





Транзакции


Управление транзакциями довольно просто


--Начинаем транзакцию

BEGIN;

UPDATE firsttable SET name = 'name1New1' WHERE name = 'name1';

--Делаем точку сохранения изменений

SAVEPOINT Your_savepoint;

UPDATE firsttable SET name = 'name1New2' WHERE name = 'name1';

--Откатываем изменения до точки сохранения изменений

ROLLBACK TO Your_savepoint;

--Завершаем транзакцию

COMMIT;


Представления


--Создаем представление

CREATE VIEW v_ft(v_name, v_lastname)

AS SELECT 'Имя: ' || name, 'Фамилия: ' || lastname FROM firsttable;


--Изменение представления

CREATE OR REPLACE VIEW v_ft(v_name, v_lastname)

AS SELECT 'Имя человека: ' || name, 'Фамилия человека: ' || lastname FROM firsttable;


--Выборка из представления

SELECT * FROM v_ft;


--Удаление представления

DROP VIEW v_ft;


Триггеры


Общий синтаксис:

CREATE TRIGGER Название_триггера

{ BEFORE | AFTER } { событие [ OR событие ] } ON таблица

FOR EACH { ROW | STATEMENT }

EXECUTE PROCEDURE функция ( аргументы )


{ BEFORE | AFTER } - выполнение кода до или после выполнения самой операции


{ событие [ OR событие ] } - что обрабатывается из операций: INSERT\UPDATE\DELETE


ON - таблица, на которой будет записан триггер


FOR EACH { ROW | STATEMENT } - ROW - функция вызывается для каждой модифицируемой записи, STATEMENT - один раз


EXECUTE PROCEDURE функция ( аргументы ) - Имя вызываемой функции и ее аргументы


--Таблица, в которую будут падать удаленные строки из триггерной процедуры

CREATE TABLE firsttable_old

( ID INT NULL,

name VARCHAR (50) NULL,

lastname VARCHAR (100) NULL );



--Создание триггерной функции

CREATE OR REPLACE FUNCTION firsttable_delete_trigger_del() RETURNS trigger AS '

BEGIN

if (select count(*) from firsttable a where id = OLD.id) > 0

then insert into firsttable_old(id,name,lastname) VALUES(OLD.id,OLD.name,OLD.lastname);

end if;

return OLD;

END;

' LANGUAGE plpgsql;



-- Создание триггера

CREATE TRIGGER firsttable_delete_trigger

BEFORE DELETE ON firsttable FOR EACH ROW

EXECUTE PROCEDURE firsttable_delete_trigger_del();


--Проверка работы триггера

DELETE FROM firsttable WHERE id=6;

SELECT * FROM firsttable_old;


PL/pgSQL ( Procedural Language/PostGres Structured Query Language ) — процедурное расширение языка SQL


Любая программная конструкция (функция\триггер\правило), написанная на PL/pgSQL, выглядит так:


[ <<метка>> ] - это например может быть DO $$

[ DECLARE

объявления переменных ]

BEGIN

тело программы

END [ метка ]; - это например может быть END $$;


--Простой пример анонимной функции

DO $$

DECLARE foo1 VARCHAR(50) = 'name_1_plpg'; foo2 VARCHAR(50) = 'name_2_plpg';

BEGIN

--Пример комментария

/*

Пример

многострочного

комментария

*/

--Присвоение значения переменной

foo1 := 'name_1_plpg:=';

UPDATE firsttable SET name = foo1 WHERE id = 1;

UPDATE firsttable SET name = foo2 WHERE id = 2;

END

$$ LANGUAGE plpgsql;


--Пример основных возможностей языка PL/pgSQL

DO $$

DECLARE

--Объявление переменной

foo1 int := 1;

foo2 int := 2;

--Определение алиаса для перменной

f1 ALIAS FOR foo1;

--Объявление константы (неизменяемой переменной)

const1 CONSTANT int := 3;

--Переменная типа ROWTYPE (строкового типа, например может быть результат целого запроса SELECT)

--Например так: SELECT * INTO row_data FROM public.firsttable_old WHERE if < 3 ;

row_data public.firsttable_old%ROWTYPE;

--Переменная типа TYPE (для отдельного столбца)

column_name_data public.firsttable_old.name%TYPE;

BEGIN

--Присвоение переменной значения из выборки с помощью ключевого слова INTO

SELECT INTO foo1 MAX(id) FROM public.firsttable_old;

SELECT INTO foo1, foo2 MIN(id), MAX(id) FROM public.firsttable_old;

INSERT INTO public.firsttable_old(id, name) VALUES(f1, 'Name_For_Alias_Min');

INSERT INTO public.firsttable_old(id, name) VALUES(foo2, 'Name_Not_Alias_Max');

--Конструкция IF THEN - проверка условия

IF const1 = 3 THEN

INSERT INTO public.firsttable_old(id, name) VALUES(const1, 'Name_Not_Alias_Const_IN_IF_3');

ELSEIF const1 = 5 THEN

INSERT INTO public.firsttable_old(id, name) VALUES(const1, 'Name_Not_Alias_Const_IN_IF_5');

ELSE

INSERT INTO public.firsttable_old(id, name) VALUES(const1, 'Name_Not_Alias_Const_IN_IF_ELSE');

END IF;

f1 := 1;

foo2 := 3;

--Цикл с условием выхода

LOOP

f1 := f1 + f1;

INSERT INTO public.firsttable_old(id, name) VALUES(f1, 'Name_For_Alias_LOOP');

EXIT WHEN f1 > foo2;

END LOOP;

f1 := 1;

foo2 := 3;

--Цикл WHILE с условием

WHILE f1 < foo2 LOOP

f1 := f1 + f1;

INSERT INTO public.firsttable_old(id, name) VALUES(f1, 'Name_For_Alias_WHILE');

END LOOP;

--Цикл FOR (перебор значений)

FOR i IN 1..3 LOOP

INSERT INTO public.firsttable_old(id, name) VALUES(i, 'Name_For_i');

END LOOP;

--Цикл FOR (перебор значений) из выборки с обращением к перременной типа ROWTYPE

FOR row_data IN SELECT * FROM public.firsttable_old WHERE name = 'Name_For_i' LOOP

column_name_data := row_data.name;

INSERT INTO public.firsttable_old(id, name) VALUES(row_data.id, column_name_data || '_FOR');

END LOOP;

END

$$ LANGUAGE plpgsql;


--Несколько вариантов создания идентичных хранимых функций и обращения к ним:


На plpgsql:

CREATE OR REPLACE FUNCTION public.temp_func_ft()

RETURNS TABLE ( _id int, _name varchar, _lastname varchar )

AS $$

BEGIN

RETURN QUERY

SELECT id, name, lastname FROM public.firsttable;

END;

$$ LANGUAGE plpgsql;


SELECT _id, _name, _lastname FROM public.temp_func_ft();


На plpgsql с однозначными именами столбцов:

CREATE OR REPLACE FUNCTION public.temp_func_ft_col()

RETURNS TABLE ( id int, name varchar, lastname varchar )

AS $$

BEGIN

RETURN QUERY

SELECT ft.id, ft.name, ft.lastname FROM public.firsttable AS ft;

END;

$$ LANGUAGE plpgsql;


SELECT id, name, lastname FROM public.temp_func_ft_col();


На sql - SETOF record:

CREATE OR REPLACE FUNCTION public.temp_func_ft_rec()

RETURNS SETOF record

AS $$

SELECT id, name, lastname FROM public.firsttable;

$$ LANGUAGE 'sql';


SELECT id, name, lastname FROM public.temp_func_ft_rec() f(id int, name varchar, lastname varchar);


На sql - TABLE:

CREATE OR REPLACE FUNCTION public.temp_func_ft_sqltable()

RETURNS TABLE ( id int, name varchar, lastname varchar )

AS $$

SELECT id, name, lastname FROM public.firsttable;

$$ LANGUAGE 'sql';


SELECT id, name, lastname FROM public.temp_func_ft_sqltable();


--Работа с временными таблицами в хранимой функции:

CREATE OR REPLACE FUNCTION public.temp_func()

RETURNS TABLE ( _id int, _name varchar, _lastname varchar )

AS $$

BEGIN

CREATE TEMP TABLE IF NOT EXISTS temp_table AS

SELECT id, name, lastname FROM public.firsttable;


INSERT INTO public.firsttable_old(id, name, lastname)

SELECT id, name, lastname FROM temp_table;


RETURN QUERY

SELECT id, name, lastname FROM temp_table;


DROP TABLE IF EXISTS temp_table;

END;

$$ LANGUAGE plpgsql;


SELECT _id, _name, _lastname FROM public.temp_func();


--Команда для возврата значения из функции - RETURN NEXT:

CREATE OR REPLACE FUNCTION get_all_firsttable() RETURNS SETOF public.firsttable AS

$BODY$

DECLARE

r public.firsttable%rowtype;

BEGIN

FOR r IN

SELECT id, name FROM public.firsttable

LOOP

--На данном этапе возможна реализация логики

RETURN NEXT r; --Добавляет текущую строку запроса к возвращаемому результату

END LOOP;

RETURN;

END

$BODY$

LANGUAGE plpgsql;


SELECT id, name FROM get_all_firsttable();


--Пример использования логики в RETURN NEXT:

CREATE OR REPLACE FUNCTION insert_all_from_firsttable_to_firsttable_old()

RETURNS TABLE(_id int, _name varchar, _lastname varchar) AS $$

BEGIN

FOR _id, _name, _lastname IN

SELECT id, name FROM public.firsttable

LOOP

INSERT INTO public.firsttable_old(id, name, lastname) VALUES(_id, _name || ' in LOOP', _lastname || ' in LOOP');

RETURN NEXT;

END LOOP;

END

$$ LANGUAGE plpgsql;


SELECT _id, _name, _lastname FROM insert_all_from_firsttable_to_firsttable_old();



Курсоры


Курсор - объект, работающий с областью памяти данных


Директивы:

CUR - объявление курсора

OPEN - открытие курсора

FETCH ... INTO - выборка текущей записи из результирующего набора записей

CLOSE - закрытие курсора



--Функция с курсором

CREATE OR REPLACE FUNCTION public.cursor()

RETURNS void

AS $$

DECLARE

val public.firsttable%rowtype;

--Курсор

cur CURSOR IS SELECT id, name FROM public.firsttable;

BEGIN

OPEN cur;

LOOP

FETCH cur INTO val;


--Выход при окончании записей

EXIT WHEN not FOUND;

val.name := 'New_Name_' || val.id;

UPDATE public.firsttable SET name = val.name WHERE id = val.id;

END LOOP;

CLOSE cur;

END;

$$

LANGUAGE plpgsql;


--Вызов

SELECT public.cursor();


REINDEX

Восстанавливает (перестраивает\строит заново) поврежденные индексы в таблицах


Синтаксис команды:

REINDEX { TABLE | DATABASE | INDEX } имя_объекта [ FORCE ]


Параметры команды:

TABLE DATABASE INDEX - типы индексируемых объектов

FORCE - восстанавление индексов для всех перечисленных объектов, если параметр не задан - восстанавливаются только поврежденные индексы


Пример:

--Следующая команда восстанавливает все поврежденные индексы для таблицы public.firsttable:

REINDEX TABLE public.firsttable;



VACUUM

Удаляет временные данные транзакций, анализирует БД, обновляет статистику

Если указать ANALYZE, сгенерирует статистическую информацию про данные

Регулярный вызов команды повысит быстродействие БД


Синтаксис команды:

VACUUM [ VERBOSE ] [ ANALYZE ] [ таблица ]

VACUUM [ VERBOSE ] ANALYZE [ таблица [ (поле [. ...] ) ] ]


Параметры команды:

VERBOSE - детальный вывод по обработанным таблицам

ANALYZE - генерирование статистики для оптимизатора

Если таблица не указана, VACUUM обработает все таблицы БД

Поле используется при обновлении статистики для оптимизатора



Пример:

--Следующая команда выолняется с ключевым словом VERBOSE для таблицы public.firsttable:

VACUUM VERBOSE public.firsttable;



Основные системные переменные

SELECT

current_catalog, current_role, current_schema, current_user, session_user, user,

current_database(), current_query(),

inet_client_addr(), inet_client_port(), inet_server_addr(), inet_server_port(),

version();