Основные положения PL / pgSQL

Вступление

В этом разделе мы описали все типы операторов, которые явно понятны PL / pgSQL. Все, что не распознано как один из этих типов операторов, считается командой SQL и отправляется на выполнение главному ядру базы данных.

Версия: 9.3

присваивание

В PL / pgSQL присвоение значения переменной записывается так:

 переменная: = выражение;

Вот пример:

Примеры:

 налог: подытог * 0,04;
my_record.user_id: = 30;

Выполнение команды без результата

Есть некоторые команды SQL, которые не возвращают строки, например, INSERT без предложения RETURNING, вы можете выполнить команду в функции PL / pgSQL. Имя переменной, отображаемое в тексте команды, рассматривается как параметр, а текущее значение переменной обрабатывается как значение параметра во время выполнения. Иногда полезно оценить выражение или запрос SELECT, но отбросить результат, например, при вызове функции, которая имеет побочные эффекты, но не имеет полезного значения результата. Чтобы сделать это в PL / pgSQL, используйте оператор PERFORM:

Вот общий синтаксис объявления переменной:

Синтаксис:

 Запрос PERFORM;

Примеры:

Код:

CREATE OR REPLACE FUNCTION test() RETURNS void AS $$ 
INSERT INTO mytable VALUES (30),(50) 
$$ LANGUAGE sql;

Код:

CREATE OR REPLACE FUNCTION demo ()
RETURNS text AS $$
BEGIN
PERFORM test();
RETURN ’OK’;
END;
$$ LANGUAGE plpgsql;

Также вы можете написать это в следующем формате

Сначала создайте функцию следующим образом

Код:

CREATE OR REPLACE FUNCTION test() RETURNS void AS $$ 
INSERT INTO mytable VALUES (30),(50) 
$$ LANGUAGE sql;

тогда следующее

Код:

CREATE OR REPLACE FUNCTION demo ()
RETURNS text AS $$
BEGIN
PERFORM test();
RETURN ’OK’;
END;
$$ LANGUAGE plpgsql;

Вот другой формат

Код:

CREATE OR REPLACE FUNCTION demo ()
RETURNS text AS $$
BEGIN
PERFORM (SELECT * FROM mytable);
RETURN ’OK’;
END;
$$ LANGUAGE plpgsql;

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

Результат команды SQL, дающей одну строку (возможно, из нескольких столбцов), может быть присвоен переменной записи, переменной типа строки или списку скалярных переменных. Это делается написанием базовой команды SQL и добавлением предложения INTO. Например,

Синтаксис:

 SELECT select_expressions INTO [STRICT] target FROM ...;
INSERT ... ВОЗВРАЩАЯ выражения INTO [STRICT] target;
ОБНОВЛЕНИЕ ... ВОЗВРАЩЕНИЕ выражений INTO [STRICT] target;
УДАЛИТЬ ... ВОЗВРАЩАЯ выражения в [STRICT] target;

где целью может быть переменная записи, переменная строки или разделенный запятыми список простых переменных и полей записи / строки. Переменные PL / pgSQL будут подставлены в остальную часть запроса, и план будет кэширован, как описано выше для команд, которые не возвращают строки. Это работает для SELECT, INSERT / UPDATE / DELETE с RETURNING и служебных команд, которые возвращают результаты набора строк (например, EXPLAIN). За исключением предложения INTO, команда SQL такая же, как если бы она была написана вне PL / pgSQL.

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

Предложение INTO может появиться практически в любом месте команды SQL. Обычно он записывается либо непосредственно перед списком select_expressions в команде SELECT, либо сразу после него, либо в конце команды для других типов команд. Рекомендуется следовать этому соглашению на случай, если синтаксический анализатор PL / pgSQL станет более строгим в будущих версиях.

Если STRICT не указан в предложении INTO, тогда для цели будет задана первая строка, возвращаемая запросом, или нулевые значения, если запрос не вернул ни одной строки. (Обратите внимание, что «первая строка» не является четко определенной, если вы не использовали ORDER BY.) Любые строки результатов после первой строки отбрасываются. Вы можете проверить специальную переменную FOUND, чтобы определить, была ли возвращена строка:

Код:

CREATE FUNCTION test1(fstname text) RETURNS text AS $$
DECLARE
    fst_name text;
found_employee employees%ROWTYPE;
BEGIN
    SELECT * INTO found_employee FROM employees WHERE first_name = fst_name;
IF NOT FOUND THEN
    RAISE EXCEPTION 'employee % not found', fst_name;
END IF;

Если указан параметр STRICT, запрос должен возвращать ровно одну строку, иначе будет сообщено об ошибке во время выполнения, либо NO_DATA_FOUND (без строк), либо TOO_MANY_ROWS (более одной строки). Вы можете использовать блок исключения, если хотите перехватить ошибку, например:

Код:

CREATE FUNCTION test3(fstname text) RETURNS text AS $$
DECLARE
    fst_name text;
found_employee employees%ROWTYPE;
BEGIN
    SELECT * INTO STRICT found_employee FROM employees WHERE first_name = fst_name;
IF NOT FOUND THEN
    RAISE EXCEPTION 'employee % not found', fst_name;
END IF;

Выполнение динамических команд

Часто вы захотите генерировать динамические команды внутри ваших функций PL / pgSQL, то есть команд, которые будут включать разные таблицы или разные типы данных при каждом их выполнении. Обычные попытки PL / pgSQL кешировать планы для команд (как обсуждено в Разделе 40.10.2) не будут работать в таких сценариях. Для решения такого рода проблем предусмотрен оператор EXECUTE:

Синтаксис:

 ВЫПОЛНИТЬ командную строку [INTO [STRICT] target] [USING expression [, ...]];

где command-string - это выражение, дающее строку (типа text), содержащую команду, которая должна быть выполнена. Необязательной целью является переменная записи, переменная строки или разделенный запятыми список простых переменных и полей записи / строки, в которые будут сохраняться результаты команды. Необязательные выражения USING предоставляют значения для вставки в команду.

Командная строка может использовать значения параметров, на которые в команде ссылаются как $ 1, $ 2 и т. Д. Эти символы относятся к значениям, указанным в предложении USING. Этот метод часто предпочтительнее вставки значений данных в командную строку в виде текста: он позволяет избежать накладных расходов во время выполнения преобразования значений в текст и обратно, и он гораздо менее подвержен атакам с использованием SQL-инъекций, поскольку нет необходимости заключать в кавычки или сбежать. Примером является:

Примеры :

Код:

CREATE FUNCTION test() RETURNS text AS $$
DECLARE
found_employee employees%ROWTYPE;
BEGIN
    SELECT * INTO STRICT found_employee FROM employees WHERE first_name = fst_name;
EXECUTE 'SELECT count(*) FROM employees WHERE manager_id<>0' into found_employee;
END;
$$ LANGUAGE plpgsql;

Следующий:

• Управляющие структуры

Предыдущий: Введение в plpgSQL
Далее: Контрольные структуры