Error query returned no rows

PL/pgSQL — процедурный язык SQL

• Обзор
  • Преимущества использования PL/pgSQL
  • Поддерживаемые типы данных аргумента и результата
• Структура PL/pgSQL
• Объявления
  • Объявление параметров функции
  • ALIAS
  • Типы копирования
  • Типы строк
  • Типы записей
  • Сортировка переменных PL/pgSQL
• Выражения
• Основные положения
  • Присваивание
  • Выполнение команды без результата
  • Выполнение запроса с результатом в одну строку
  • Выполнение динамических команд
  • Получение статуса результата
  • Пустой оператор
• Управляющие структуры
  • Возврат из функции
    • RETURN
    • RETURN NEXT и RETURN QUERY
  • Возврат из процедуры
  • Вызов процедуры
  • Условные операторы
    • IF-THEN
    • IF-THEN-ELSE
    • IF-THEN-ELSIF
    • Простой CASE
    • Поисковое выражение CASE
  • Простые циклы
    • LOOP
    • EXIT
    • CONTINUE
    • WHILE
    • FOR (целочисленный вариант)
  • Цикл по результатам запроса
  • Цикл по массивам
  • Ошибки захвата
    • Получение информации об ошибке
  • Получение информации о месте выполнения
• Курсоры
  • Объявление переменных курсора
  • Открытие курсоров
    • OPEN FOR query
    • OPEN FOR EXECUTE
    • Открытие связанного курсора
  • Использование курсоров
    • FETCH
    • MOVE
    • UPDATE/DELETE WHERE CURRENT OF
    • CLOSE
    • Возврат курсора
  • Цикл по результату курсора
• Управление транзакциями
• Ошибки и сообщения
  • Вывод ошибок и сообщений
  • Проверка утверждений
• Триггерные функции
  • Триггеры при изменении данных
  • Триггеры на события
• PL/pgSQL под капотом
  • Подстановка переменных
  • Планирование кэширования
• Советы по разработке на PL/pgSQL
  • Обработка кавычек
  • Дополнительные проверки во время компиляции и во время выполнения
• Портирование из Oracle PL/SQL
  • Примеры портирования
  • Другие вещи для наблюдения
    • Неявный откат после исключений
    • EXECUTE
    • Оптимизация функций PL/pgSQL
  • Приложение

Обзор

PL/pgSQL — это загружаемый процедурный язык для системы баз данных
QHB. Цели разработки PL/pgSQL заключались в создании
загружаемого процедурного языка, который

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

Функции, созданные с помощью PL/pgSQL, можно использовать везде, где
могут использоваться встроенные функции. Например, можно создавать
сложные функции условного вычисления, а затем использовать их для
определения операторов или использовать их в выражениях индекса.

В QHB PL/pgSQL устанавливается по
умолчанию. Однако это все еще загружаемый модуль, поэтому
администраторы, особенно заботящиеся о безопасности, могут удалить его.

Преимущества использования PL/pgSQL

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

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

С PL/pgSQL вы можете сгруппировать блок вычислений и серию запросов на
сервере базы данных, таким образом обладая мощью процедурного языка и
простотой использования SQL, но при этом значительно экономя накладные
расходы на связь клиент-сервер.

• Дополнительные обходы между клиентом и сервером исключены
• Промежуточные результаты, которые не нужны клиенту, не нужно
  передавать между сервером и клиентом
• Можно избежать нескольких раундов разбора запроса

Это может привести к значительному увеличению производительности по
сравнению с приложением, которое не использует хранимые функции.

Кроме того, с PL/pgSQL вы можете использовать все типы данных,
операторы и функции SQL.

Поддерживаемые типы данных аргумента и результата

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

Функции PL/pgSQL могут быть объявлены для приема переменного числа
аргументов с помощью маркера VARIADIC. Это работает точно так же, как и
для функций SQL, как описано в разделе Функции SQL с переменным числом аргументов.

Функции PL/pgSQL также могут быть объявлены для приема и возврата
полиморфных типов anyelement, anyarray, anynonarray, anyenum и
anyrange. Фактические типы данных, обрабатываемые полиморфной функцией,
могут варьироваться от вызова к вызову, как описано в разделе Полиморфные типы.
Пример показан в разделе Объявление параметров функции.

Функции PL/pgSQL также могут быть объявлены так, чтобы они возвращали
«набор» (или таблицу) любого типа данных, который может быть возвращен
как один экземпляр. Такая функция генерирует свои выходные данные,
выполняя RETURN NEXT для каждого требуемого элемента набора результатов
или используя RETURN QUERY для вывода результата оценки запроса.

Наконец, можно объявить функцию PL/pgSQL, которая возвращает void если
она не имеет полезного возвращаемого значения. (В качестве альтернативы,
это может быть написано как процедура в этом случае).

Функции PL/pgSQL также могут быть объявлены с выходными параметрами
вместо явной спецификации возвращаемого типа. Это не добавляет
фундаментальной возможности к языку, но это часто удобно, особенно для
возврата нескольких значений. Обозначение RETURNS TABLE также может
использоваться вместо RETURNS SETOF.

Конкретные примеры приведены в разделе Объявление параметров функции и разделе Возврат из функции.

Структура PL/pgSQL

Функции, написанные на PL/pgSQL, определяются сервером путем
выполнения команд CREATE FUNCTION. Такая команда обычно выглядит,
скажем,

CREATE FUNCTION somefunc(integer, text) RETURNS integer
AS 'function body text'
LANGUAGE plpgsql;

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

PL/pgSQL — это блочно-структурированный язык. Полный текст тела
функции должен быть блоком. Блок определяется как:

[ <<label>> ]
[ DECLARE
    declarations ]
BEGIN
    statements
END [ label ];

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

Заметка
Распространенная ошибка — писать точку с запятой сразу после BEGIN. Это
неверно и приведет к синтаксической ошибке.

label нужна только в том случае, если вы хотите идентифицировать блок
для использования в операторе EXIT или указать имена переменных,
объявленных в блоке. Если метка дается после END, она должна совпадать
с меткой в начале блока.

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

Комментарии работают в коде PL/pgSQL так же, как и в обычном SQL.
Двойная черта ( — ) начинает комментарий, который простирается до конца
строки. /* запускает комментарий блока, который распространяется до
появления символов */.

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

CREATE FUNCTION somefunc() RETURNS integer AS $$
<< outerblock >>
DECLARE
    quantity integer := 30;
BEGIN
    RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 30
    quantity := 50;
    --
    -- Create a subblock
    --
    DECLARE
        quantity integer := 80;
    BEGIN
        RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 80
        RAISE NOTICE 'Outer quantity here is %', outerblock.quantity;  -- Prints 50
    END;

    RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 50

    RETURN quantity;
END;
$$ LANGUAGE plpgsql;

Заметка
На самом деле существует скрытый «внешний блок», окружающий тело любой
функции PL/pgSQL. Этот блок предоставляет объявления параметров
функции (если есть), а также некоторые специальные переменные, такие как
FOUND (см. раздел Получение статуса результата). Внешний блок помечен именем функции, что
означает, что параметры и специальные переменные могут быть дополнены
именем функции.

Важно не путать использование BEGIN/END для группировки операторов в
PL/pgSQL с одноименными командами SQL для управления транзакциями.
PL/pgSQL BEGIN/END предназначены только для группировки; они не
начинают и не заканчивают транзакцию. См. раздел Управление транзакциями для получения
информации об управлении транзакциями в PL/pgSQL. Кроме того, блок,
содержащий предложение EXCEPTION, эффективно формирует субтранзакцию,
которую можно откатить без влияния на внешнюю транзакцию. Подробнее об
этом см. раздел Ошибки захвата.

Объявления

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

Переменные PL/pgSQL могут иметь любой тип данных SQL, например,
integer, varchar и char.

Вот несколько примеров объявлений переменных:

user_id integer;
quantity numeric(5);
url varchar;
myrow tablename%ROWTYPE;
myfield tablename.columnname%TYPE;
arow RECORD;

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

name [ CONSTANT ] type [ COLLATE collation_name ] [ NOT NULL ] [ { DEFAULT | := | = } expression ];

Предложение DEFAULT, если оно задано, задает начальное значение,
назначенное переменной при вводе блока. Если предложение DEFAULT не
задано, переменная инициализируется нулевым значением SQL. Опция
CONSTANT предотвращает присвоение переменной после инициализации, так
что ее значение будет оставаться постоянным на протяжении всего блока.
Опция COLLATE указывает параметры сортировки, которые следует
использовать для переменной (см. раздел Сортировка переменных PL/pgSQL). Если указано NOT NULL,
присвоение нулевого значения приводит к ошибке во время выполнения.
Все переменные, объявленные как NOT NULL должны иметь ненулевое значение
по умолчанию. Равно ( = ) может использоваться вместо
PL/SQL-совместимого :=.

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

Примеры:

quantity integer DEFAULT 32;
url varchar := 'http://mysite.com';
user_id CONSTANT integer := 10;

Объявление параметров функции

Параметры, передаваемые в функции, именуются с помощью идентификаторов
$1, $2 и т.д. При желании псевдонимы могут быть объявлены для $n
имен параметров с целью повышения читабельности. Затем можно использовать
псевдоним или числовой идентификатор для ссылки на значение параметра.

Есть два способа создать псевдоним. Предпочтительным способом является
присвоение имени параметру в команде CREATE FUNCTION, например:

CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$
BEGIN
    RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;

Другой способ — явно объявить псевдоним, используя синтаксис объявления.

name ALIAS FOR $n;

Тот же пример в этом стиле выглядит так:

CREATE FUNCTION sales_tax(real) RETURNS real AS $$
DECLARE
    subtotal ALIAS FOR $1;
BEGIN
    RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;

Заметка
Эти два примера не являются полностью эквивалентными. В первом случае
на промежуточный итог можно ссылаться как на sales_tax.subtotal,
но во втором случае это невозможно. (Если бы мы прикрепили метку к
внутреннему блоку, subtotal можно было бы квалифицировать с этой
меткой).

Еще несколько примеров:

CREATE FUNCTION instr(varchar, integer) RETURNS integer AS $$
DECLARE
    v_string ALIAS FOR $1;
    index ALIAS FOR $2;
BEGIN
    -- some computations using v_string and index here
END;
$$ LANGUAGE plpgsql;


CREATE FUNCTION concat_selected_fields(in_t sometablename) RETURNS text AS $$
BEGIN
    RETURN in_t.f1 || in_t.f3 || in_t.f5 || in_t.f7;
END;
$$ LANGUAGE plpgsql;

Когда функция PL/pgSQL объявляется с выходными параметрами, выходным
параметрам присваиваются имена $n и необязательные псевдонимы точно так
же, как обычные входные параметры. Выходной параметр фактически является
переменной, которая начинается с NULL; это должно быть назначено во
время выполнения функции. Конечное значение параметра — это то, что
возвращается. Например, расчет налога с продаж также может быть сделан
следующим образом:

CREATE FUNCTION sales_tax(subtotal real, OUT tax real) AS $$
BEGIN
    tax := subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;

Обратите внимание, что мы пропустили RETURNS real — мы могли бы включить
его, но это было бы излишним.

Выходные параметры наиболее полезны при возврате нескольких значений.
Тривиальный пример:

CREATE FUNCTION sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$
BEGIN
    sum := x + y;
    prod := x * y;
END;
$$ LANGUAGE plpgsql;

Как обсуждалось в разделе Функции SQL с выходными параметрами, это эффективно создает анонимный тип
записи для результатов функции. Если задано предложение RETURNS, оно
должно типа record.

Другой способ объявить функцию PL/pgSQL — использовать RETURNS TABLE,
например:

CREATE FUNCTION extended_sales(p_itemno int)
RETURNS TABLE(quantity int, total numeric) AS $$
BEGIN
    RETURN QUERY SELECT s.quantity, s.quantity * s.price FROM sales AS s
                 WHERE s.itemno = p_itemno;
END;
$$ LANGUAGE plpgsql;

Это в точности эквивалентно объявлению одного или нескольких параметров
OUT и указанию RETURNS SETOF sometype.

Когда возвращаемый тип функции PL/pgSQL объявлен как полиморфный тип (
anyelement, anyarray, anynonarray, anyenum или anyrange), создается
специальный параметр $0. Тип данных — это фактический тип возвращаемого
значения функции, выведенный из фактических типов ввода (см. раздел Полиморфные типы).
Это позволяет функции получить доступ к ее фактическому типу
возврата, как показано в разделе Типы копирования. $0 инициализируется нулевым
значением и может быть изменено функцией, поэтому его можно использовать
для хранения возвращаемого значения, если это необходимо, хотя это не
требуется. $0 также может быть присвоен псевдоним. Например, эта функция
работает с любым типом данных, который имеет оператор + :

CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement)
RETURNS anyelement AS $$
DECLARE
    result ALIAS FOR $0;
BEGIN
    result := v1 + v2 + v3;
    RETURN result;
END;
$$ LANGUAGE plpgsql;

Тот же эффект можно получить, объявив один или несколько выходных
параметров как полиморфные типы. В этом случае специальный параметр $0
не используется; Сами выходные параметры служат той же цели. Например:

CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement,
                                 OUT sum anyelement)
AS $$
BEGIN
    sum := v1 + v2 + v3;
END;
$$ LANGUAGE plpgsql;

ALIAS

newname ALIAS FOR oldname;

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

Примеры:

DECLARE
  prior ALIAS FOR old;
  updated ALIAS FOR new;

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

Типы копирования

variable%TYPE

%TYPE предоставляет тип данных переменной или столбца таблицы. Вы можете
использовать это для объявления переменных, которые будут содержать
значения базы данных. Например, предположим, у вас есть столбец с именем
user_id в вашей таблице users. Чтобы объявить переменную с тем же
типом данных, что и users.user_id вы пишете:

user_id users.user_id%TYPE;

Используя %TYPE вам не нужно знать тип данных структуры, на которую вы
ссылаетесь, и, самое главное, если тип данных ссылочного элемента
изменится в будущем (например: вы меняете тип user_id с integer на real),
вам может не потребоваться изменить определение функции.

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

Типы строк

name table_name%ROWTYPE;
name composite_type_name;

Переменная составного типа называется переменной строки (или переменной
типа строки). Такая переменная может содержать целую строку результата
запроса SELECT или FOR, если набор столбцов этого запроса соответствует
объявленному типу переменной. Доступ к отдельным полям значения строки
осуществляется с использованием обычной точечной нотации, например
rowvar.field.

Переменная строки может быть объявлена для того же типа, что и строки
существующей таблицы или представления, используя нотацию table_name%ROWTYPE;
или это может быть объявлено путем указания имени составного
типа. (Поскольку каждая таблица имеет связанный составной тип с одним и
тем же именем, в QHB не имеет значения, пишете ли вы %ROWTYPE или
нет. Но форма с %ROWTYPE более переносима).

Параметрами функции могут быть составные типы (полные строки таблицы). В
этом случае соответствующий идентификатор $n будет переменной строки, и
из него можно выбрать поля, например, $1.user_id.

Вот пример использования составных типов. table1 и table2 являются
существующими таблицами, имеющими по крайней мере упомянутые поля:

CREATE FUNCTION merge_fields(t_row table1) RETURNS text AS $$
DECLARE
    t2_row table2%ROWTYPE;
BEGIN
    SELECT * INTO t2_row FROM table2 WHERE ... ;
    RETURN t_row.f1 || t2_row.f3 || t_row.f5 || t2_row.f7;
END;
$$ LANGUAGE plpgsql;

SELECT merge_fields(t.*) FROM table1 t WHERE ... ;

Типы записей

name RECORD;

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

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

Сортировка переменных PL/pgSQL

Когда функция PL/pgSQL имеет один или несколько параметров типов
данных для сопоставления, для каждого вызова функции определяется
сопоставление в зависимости от сопоставлений, назначенных фактическим
аргументам, как описано в разделе Поддержка правил сортировки. Если сопоставление успешно
идентифицировано (т.е. между аргументами нет конфликтов неявных
сопоставлений), то все параметры сопоставления обрабатываются как
имеющие такое сопоставление неявно. Это повлияет на поведение
чувствительных к сбору операций внутри функции. Например, рассмотрим

CREATE FUNCTION less_than(a text, b text) RETURNS boolean AS $$
BEGIN
    RETURN a < b;
END;
$$ LANGUAGE plpgsql;

SELECT less_than(text_field_1, text_field_2) FROM table1;
SELECT less_than(text_field_1, text_field_2 COLLATE "C") FROM table1;

Первое использование less_than будет использовать общее сравнение
text_field_1 и text_field_2 для сравнения, в то время как второе
использование будет использовать C сортировку.

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

CREATE FUNCTION less_than(a text, b text) RETURNS boolean AS $$
DECLARE
    local_a text := a;
    local_b text := b;
BEGIN
    RETURN local_a < local_b;
END;
$$ LANGUAGE plpgsql;

Если параметры типов данных для сопоставления отсутствуют или для них
нельзя определить общие параметры сортировки, то параметры и локальные
переменные используют параметры сортировки по умолчанию для своего типа
данных (обычно это параметры сортировки базы данных по умолчанию, но
могут отличаться для переменных типа домена).

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

DECLARE
    local_a text COLLATE "en_US";

Этот параметр переопределяет параметры сортировки, которые в противном
случае были бы переданы переменной в соответствии с приведенными выше
правилами.

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

CREATE FUNCTION less_than_c(a text, b text) RETURNS boolean AS $$
BEGIN
    RETURN a < b COLLATE "C";
END;
$$ LANGUAGE plpgsql;

Это переопределяет параметры сортировки, связанные со столбцами таблицы,
параметрами или локальными переменными, используемыми в выражении, так
же, как это происходит в простой команде SQL.

Выражения

Все выражения, используемые в инструкциях PL/pgSQL, обрабатываются с
использованием основного SQL- исполнителя сервера. Например, когда вы
пишете оператор PL/pgSQL, как

IF expression THEN ...

PL/pgSQL оценит выражение, подав запрос

SELECT expression

на основной движок SQL. При формировании команды SELECT любые вхождения
имен переменных PL/pgSQL заменяются параметрами, как подробно описано
в разделе Подстановка переменных. Это позволяет составить план запроса для SELECT
только один раз, а затем повторно использовать для последующих оценок с
различными значениями переменных. Таким образом, то, что действительно
происходит при первом использовании выражения, по сути, является
командой PREPARE. Например, если мы объявили две целочисленные
переменные x и y, и мы напишем

IF x < y THEN ...

то, что происходит за кулисами, эквивалентно

PREPARE statement_name(integer, integer) AS SELECT $1 < $2;

и затем этот подготовленный оператор будет EXECUTE d для каждого
выполнения оператора IF, с текущими значениями переменных PL/pgSQL,
предоставленными в качестве значений параметров. Обычно эти детали не
важны для пользователя PL/pgSQL, но их полезно знать при попытке
диагностировать проблему. Более подробная информация представлена в
разделе Планирование кэширования.

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

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

Присваивание

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

variable { := | = } expression;

Как объяснено ранее, выражение в таком выражении оценивается с помощью
команды SQL SELECT отправляемой основному ядру базы данных. Выражение
должно давать одно значение (возможно, значение строки, если переменная
является строкой или переменной записи). Целевая переменная может быть
простой переменной (необязательно квалифицируемой именем блока), полем
строки или переменной записи или элементом массива, который является
простой переменной или полем. Равное ( = ) может использоваться вместо
PL/SQL-совместимого :=.

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

Примеры:

tax := subtotal * 0.06;
my_record.user_id := 20;

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

Для любой команды SQL, которая не возвращает строки, например, INSERT
без предложения RETURNING, вы можете выполнить команду в функции PL /
pgSQL, просто написав команду.

Любое имя переменной PL/pgSQL, отображаемое в тексте команды,
рассматривается как параметр, а затем текущее значение переменной
предоставляется в качестве значения параметра во время выполнения. Это
точно так же, как обработка, описанная ранее для выражений; подробности
см. в разделе Подстановка переменных.

При выполнении команды SQL таким способом PL/pgSQL может кэшировать и
повторно использовать план выполнения для команды, как обсуждалось в
разделе Планирование кэширования.

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

PERFORM query;

Это выполняет query и отбрасывает результат. Напишите query, как и
команду SQL SELECT, но замените исходное ключевое слово SELECT на
PERFORM. Для запросов WITH используйте PERFORM а затем поместите запрос
в скобки. (В этом случае запрос может вернуть только одну строку).
Переменные PL/pgSQL будут подставлены в запрос так же, как для команд,
которые не возвращают результата, и план кэшируется таким же образом.
Кроме того, специальная переменная FOUND устанавливается в значение
true, если запрос выдал хотя бы одну строку, или в значение false, если
не было строк (см. раздел Получение статуса результата).

Заметка
Можно было бы ожидать, что написание SELECT напрямую приведет к этому
результату, но в настоящее время единственный приемлемый способ сделать
это — PERFORM. Команда SQL, которая может возвращать строки, например,
SELECT, будет отклонена как ошибка, если в ней нет предложения INTO,
как описано в следующем разделе.

Пример:

PERFORM create_mv('cs_session_page_requests_mv', my_query);

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

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

SELECT select_expressions INTO [STRICT] target FROM ...;
INSERT ... RETURNING expressions INTO [STRICT] target;
UPDATE ... RETURNING expressions INTO [STRICT] target;
DELETE ... RETURNING expressions INTO [STRICT] target;

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

Заметка
Обратите внимание, что эта интерпретация SELECT с INTO сильно отличается
от обычной команды SELECT INTO QHB, в которой целью INTO
является вновь созданная таблица. Если вы хотите создать таблицу из
результата SELECT внутри функции PL/pgSQL, используйте синтаксис
CREATE TABLE… AS SELECT.

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

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

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

SELECT * INTO myrec FROM emp WHERE empname = myname;
IF NOT FOUND THEN
    RAISE EXCEPTION 'employee % not found', myname;
END IF;

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

BEGIN
    SELECT * INTO STRICT myrec FROM emp WHERE empname = myname;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RAISE EXCEPTION 'employee % not found', myname;
        WHEN TOO_MANY_ROWS THEN
            RAISE EXCEPTION 'employee % not unique', myname;
END;

Успешное выполнение команды с помощью STRICT всегда устанавливает FOUND
в значение true.

Для INSERT / UPDATE / DELETE с RETURNING PL/pgSQL сообщает об ошибке
для более чем одной возвращенной строки, даже если STRICT не указан. Это
связано с тем, что нет такой опции, как ORDER BY с помощью которой можно
определить, какая затронутая строка должна быть возвращена.

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

CREATE FUNCTION get_userid(username text) RETURNS int
AS $$
#print_strict_params on
DECLARE
userid int;
BEGIN
    SELECT users.userid INTO STRICT userid
        FROM users WHERE users.username = get_userid.username;
    RETURN userid;
END
$$ LANGUAGE plpgsql;

При сбое эта функция может выдавать сообщение об ошибке, например

ERROR:  query returned no rows
DETAIL:  parameters: $1 = 'nosuchuser'
CONTEXT:  PL/pgSQL function get_userid(text) line 6 at SQL statement

Заметка
Опция STRICT соответствует поведению SELECT INTO и связанных операторов
Oracle PL/SQL.

Для обработки случаев, когда вам нужно обработать несколько строк
результатов из запроса SQL, см. раздел Цикл по результатам запроса.

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

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

EXECUTE command-string [ INTO [STRICT] target ] [ USING expression [, ... ] ];

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

Подстановка переменных PL/pgSQL в вычисляемой командной строке не
производится. Любые обязательные значения переменных должны быть
вставлены в командную строку по мере ее создания; или вы можете
использовать параметры, как описано ниже.

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

Предложение INTO указывает, где должны быть назначены результаты команды
SQL, возвращающей строки. Если указан список строк или переменных, он
должен точно соответствовать структуре результатов запроса (когда
используется переменная записи, он автоматически настраивается на
соответствие структуре результатов). Если возвращается несколько строк,
только первая будет назначена переменной INTO. Если строки не
возвращаются, NULL присваивается переменной (или переменным) INTO. Если предложение
INTO не указано, результаты запроса отбрасываются.

Если задана опция STRICT выдается сообщение об ошибке, если запрос не
создает ровно одну строку.

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

EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2'
   INTO c
   USING checked_user, checked_date;

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

EXECUTE 'SELECT count(*) FROM '
    || quote_ident(tabname)
    || ' WHERE inserted_by = $1 AND inserted <= $2'
   INTO c
   USING checked_user, checked_date;

Более чистый подход заключается в использовании %I спецификации format()
для имен таблиц или столбцов (строки, разделенные новой строкой,
объединяются):

EXECUTE format('SELECT count(*) FROM %I '
   'WHERE inserted_by = $1 AND inserted <= $2', tabname)
   INTO c
   USING checked_user, checked_date;

Другое ограничение на символы параметров заключается в том, что они
работают только в командах SELECT, INSERT, UPDATE и DELETE. В других
типах операторов (обычно называемых служебными операторами) вы должны
вставлять значения текстуально, даже если они являются просто значениями
данных.

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

SELECT INTO в настоящее время не поддерживается в EXECUTE; вместо этого
выполните простую команду SELECT и укажите INTO как часть самого EXECUTE.

Заметка
Оператор PL/pgSQL EXECUTE не связан с оператором EXECUTE SQL,
поддерживаемым сервером QHB. Оператор EXECUTE сервера нельзя
использовать непосредственно в функциях PL/pgSQL (и не нужен).

Пример 1. Заключение в кавычки значений в динамических запросах

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

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

EXECUTE format('UPDATE tbl SET %I = $1 '
   'WHERE key = $2', colname) USING newvalue, keyvalue;

Также можно напрямую вызывать функции цитирования:

EXECUTE 'UPDATE tbl SET '
        || quote_ident(colname)
        || ' = '
        || quote_literal(newvalue)
        || ' WHERE key = '
        || quote_literal(keyvalue);

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

Поскольку quote_literal помечен как STRICT, он всегда будет возвращать
ноль при вызове с нулевым аргументом. В приведенном выше примере, если
newvalue или keyvalue были нулевыми, вся строка динамического запроса
станет нулевой, что приведет к ошибке EXECUTE. Вы можете избежать этой
проблемы, используя функцию quote_nullable, которая работает так же,
как и quote_literal за исключением того, что при вызове с нулевым
аргументом она возвращает строку NULL. Например,

EXECUTE 'UPDATE tbl SET '
        || quote_ident(colname)
        || ' = '
        || quote_nullable(newvalue)
        || ' WHERE key = '
        || quote_nullable(keyvalue);

Если вы имеете дело со значениями, которые могут быть нулевыми, вы
должны обычно использовать quote_nullable вместо quote_literal.

Как всегда, нужно позаботиться о том, чтобы нулевые значения в запросе
не давали непредвиденных результатов. Например, WHERE

'WHERE key = ' || quote_nullable(keyvalue)

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

'WHERE key IS NOT DISTINCT FROM ' || quote_nullable(keyvalue)

(В настоящее время IS NOT DISTINCT FROM обрабатывается гораздо менее
эффективно, чем =, поэтому не делайте этого, если не нужно. См. раздел Функции и операторы сравнения
для получения дополнительной информации о пустых значениях и IS
DISTINCT).

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

EXECUTE 'UPDATE tbl SET '
        || quote_ident(colname)
        || ' = $$'
        || newvalue
        || '$$ WHERE key = '
        || quote_literal(keyvalue);

потому что он сломался бы, если бы содержимое newvalue содержало $$. То
же самое возражение будет относиться к любому другому ограничителю в знаках
доллара, который вы можете выбрать. Таким образом, для безопасного
цитирования текста, который не известен заранее, вы должны использовать
quote_literal, quote_nullable или quote_ident, в зависимости от
ситуации.

Динамические операторы SQL также можно безопасно создавать с помощью
функции format (см. раздел FORMAT). Например:

EXECUTE format('UPDATE tbl SET %I = %L '
   'WHERE key = %L', colname, newvalue, keyvalue);

%I эквивалентен quote_ident, а %L эквивалентен quote_nullable.
Функция format может использоваться вместе с предложением USING :

EXECUTE format('UPDATE tbl SET %I = $1 WHERE key = $2', colname)
   USING newvalue, keyvalue;

Эта форма лучше, потому что переменные обрабатываются в их собственном
формате типа данных, а не безоговорочно преобразуют их в текст и
заключают их в кавычки через %L Это также более эффективно.

Гораздо больший пример динамической команды и EXECUTE можно увидеть в
Примере 10, который создает и выполняет команду CREATE FUNCTION для
определения новой функции.

Получение статуса результата

Есть несколько способов определить эффект команды. Первый метод
заключается в использовании команды GET DIAGNOSTICS, которая имеет вид:

GET [ CURRENT ] DIAGNOSTICS variable { = | := } item [, ... ];

Эта команда позволяет получить индикаторы состояния системы. CURRENT —
это шумовое слово (но см. Также GET STACKED DIAGNOSTICS в разделе Получение информации об ошибке).
Каждый item является ключевым словом, идентифицирующим
значение состояния, которое должно быть назначено указанной variable
(которое должно иметь правильный тип данных для его получения).
Доступные в настоящее время элементы состояния показаны в таблице 1.
Вместо токена SQL-standard = можно использовать двоеточие ( := ).
Пример:

GET DIAGNOSTICS integer_var = ROW_COUNT;

Таблица 1. Доступные элементы диагностики

Второй метод определения эффектов команды — это проверка специальной
переменной с именем FOUND, которая имеет тип boolean. FOUND начинает
false в каждом вызове функции PL/pgSQL. Он устанавливается каждым из
следующих типов утверждений:

• Оператор SELECT INTO устанавливает FOUND true, если назначена
  строка, и false, если строка не возвращается.

• Оператор PERFORM устанавливает FOUND true, если он создает (и
  отбрасывает) одну или несколько строк, и false, если строка не
  создается.

• Операторы UPDATE, INSERT и DELETE устанавливают FOUND true, если
  затронута хотя бы одна строка, и false, если строка не
  затрагивается.

• FETCH устанавливает FOUND true, если возвращает строку, и false,
  если строка не возвращается.

• Оператор MOVE устанавливает FOUND true, если он успешно перемещает
  курсор, в противном случае — false.

• FOREACH FOR или FOREACH устанавливает FOUND true, если он
  повторяется один или несколько раз, в противном случае — false.
  FOUND устанавливается таким образом при выходе из цикла; внутри
  выполнения цикла FOUND не изменяется оператором цикла, хотя он может
  быть изменен выполнением других операторов в теле цикла.

• Операторы RETURN QUERY и RETURN QUERY EXECUTE устанавливают FOUND
  true, если запрос возвращает хотя бы одну строку, и false, если
  строка не возвращается.

Другие операторы PL/pgSQL не изменяют состояние FOUND. В частности,
обратите внимание, что EXECUTE изменяет вывод GET DIAGNOSTICS, но не
меняет FOUND.

FOUND — локальная переменная в каждой функции PL/pgSQL ; любые
изменения в ней влияют только на текущую функцию.

Пустой оператор

Иногда полезен пустой оператор.
Например, когда одно плечо условия if / then /
else намеренно пусто. Для этого используйте оператор NULL :

NULL;

Например, следующие два фрагмента кода эквивалентны:

BEGIN
    y := x / 0;
EXCEPTION
    WHEN division_by_zero THEN
        NULL;  -- ignore the error
END;

BEGIN
    y := x / 0;
EXCEPTION
    WHEN division_by_zero THEN  -- ignore the error
END;

Что предпочтительнее, это вопрос вкуса.

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

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

Управляющие структуры, вероятно, самая полезная (и важная) часть PL/pgSQL.
С управляющими структурами PL/pgSQL вы можете манипулировать
данными QHB очень гибким и мощным способом.

Возврат из функции

Доступны две команды, которые позволяют вам возвращать данные из
функции: RETURN и RETURN NEXT.

RETURN

RETURN expression;

RETURN с выражением завершает функцию и возвращает значение expression
вызывающей стороне. Эта форма используется для функций PL/pgSQL,
которые не возвращают набор.

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

Если вы объявили функцию с выходными параметрами, напишите просто RETURN
без выражения. Текущие значения переменных выходного параметра будут
возвращены.

Если вы объявили функцию, возвращающую void, оператор RETURN может быть
использован для досрочного выхода из функции; но не пишите выражение
после RETURN.

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

Несколько примеров:

-- functions returning a scalar type
RETURN 1 + 2;
RETURN scalar_var;

-- functions returning a composite type
RETURN composite_type_var;
RETURN (1, 2, 'three'::text);  -- must cast columns to correct types

RETURN NEXT и RETURN QUERY

RETURN NEXT expression;
RETURN QUERY query;
RETURN QUERY EXECUTE command-string [ USING expression [, ... ] ];

Когда объявляется, что функция PL/pgSQL возвращает SETOF sometype,
процедура, которой нужно следовать, немного отличается. В этом случае
отдельные возвращаемые элементы указываются с помощью последовательности
команд RETURN NEXT или RETURN QUERY, а затем используется последняя
команда RETURN без аргумента, указывающая, что функция завершилась.
RETURN NEXT может использоваться как со скалярными, так и с составными
типами данных; с составным типом результата будет возвращена вся «таблица»
результатов. RETURN QUERY добавляет результаты выполнения
запроса к набору результатов функции. RETURN NEXT и RETURN QUERY можно
свободно смешивать в одной функции, возвращающей множество, и в этом
случае их результаты будут объединены.

RETURN NEXT и RETURN QUERY фактически не возвращаются из функции — они
просто добавляют ноль или более строк в набор результатов функции. Затем
выполнение продолжается со следующего оператора в функции PL/pgSQL.
По мере выполнения последовательных команд RETURN NEXT или RETURN QUERY
набор результатов формируется. Окончательный RETURN, который не должен
иметь аргументов, заставляет элемент управления выйти из функции (или вы
можете просто позволить элементу управления достигнуть конца функции).

RETURN QUERY имеет вариант RETURN QUERY EXECUTE, который указывает, что
запрос должен выполняться динамически. Выражения параметров могут быть
вставлены в вычисляемую строку запроса через USING, точно так же, как в
команде EXECUTE.

Если вы объявили функцию с выходными параметрами, напишите просто RETURN
NEXT без выражения. При каждом выполнении текущие значения переменной (или переменных)
выходных параметров будут сохраняться для последующего возврата в
виде строки результата. Обратите внимание, что вы должны объявить
функцию как возвращающую SETOF record когда есть несколько выходных
параметров, или SETOF sometype когда есть только один выходной параметр
типа sometype, чтобы создать функцию, возвращающую множество с
выходными параметрами.

Вот пример функции, использующей RETURN NEXT :

CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT);
INSERT INTO foo VALUES (1, 2, 'three');
INSERT INTO foo VALUES (4, 5, 'six');

CREATE OR REPLACE FUNCTION get_all_foo() RETURNS SETOF foo AS
$BODY$
DECLARE
    r foo%rowtype;
BEGIN
    FOR r IN
        SELECT * FROM foo WHERE fooid > 0
    LOOP
        -- can do some processing here
        RETURN NEXT r; -- return current row of SELECT
    END LOOP;
    RETURN;
END
$BODY$
LANGUAGE plpgsql;

SELECT * FROM get_all_foo();

Вот пример функции, использующей RETURN QUERY :

CREATE FUNCTION get_available_flightid(date) RETURNS SETOF integer AS
$BODY$
BEGIN
    RETURN QUERY SELECT flightid
                   FROM flight
                  WHERE flightdate >= $1
                    AND flightdate < ($1 + 1);

    -- Since execution is not finished, we can check whether rows were returned
    -- and raise exception if not.
    IF NOT FOUND THEN
        RAISE EXCEPTION 'No flight at %.', $1;
    END IF;

    RETURN;
 END
$BODY$
LANGUAGE plpgsql;

-- Returns available flights or raises exception if there are no
-- available flights.
SELECT * FROM get_available_flightid(CURRENT_DATE);

Заметка
Текущая реализация RETURN NEXT и RETURN QUERY сохраняет весь набор
результатов перед возвратом из функции, как обсуждалось выше. Это
означает, что если функция PL/pgSQL выдает очень большой набор
результатов, производительность может быть низкой: данные будут записаны
на диск во избежание исчерпания памяти, но сама функция не вернется,
пока не будет сгенерирован весь набор результатов. Будущая версия
PL/pgSQL может позволить пользователям определять функции, возвращающие
множество, которые не имеют этого ограничения. В настоящее время точка,
с которой данные начинают записываться на диск, контролируется
переменной конфигурации work_mem. Администраторам, которые имеют
достаточно памяти для хранения больших наборов результатов в памяти,
следует рассмотреть возможность увеличения этого параметра.

Возврат из процедуры

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

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

Вызов процедуры

Функция, процедура или блок PL/pgSQL могут вызывать процедуру,
используя CALL. Выходные параметры обрабатываются иначе, чем CALL
работает в простом SQL. Каждый параметр INOUT процедуры должен
соответствовать переменной в операторе CALL, и все, что возвращает
процедура, присваивается этой переменной после ее возврата. Например:

CREATE PROCEDURE triple(INOUT x int)
LANGUAGE plpgsql
AS $$
BEGIN
    x := x * 3;
END;
$$;

DO $$
DECLARE myvar int := 5;
BEGIN
  CALL triple(myvar);
  RAISE NOTICE 'myvar = %', myvar;  -- prints 15
END
$$;

Условные операторы

Операторы IF и CASE позволяют выполнять альтернативные команды в
зависимости от определенных условий. PL/pgSQL имеет три формы IF:

• IF ... THEN ... END IF

• IF ... THEN ... ELSE ... END IF

• IF ... THEN ... ELSIF ... THEN ... ELSE ... END IF

и две формы CASE :

• CASE ... WHEN ... THEN ... ELSE ... END CASE

• CASE WHEN ... THEN ... ELSE ... END CASE

IF-THEN

IF boolean-expression THEN
    statements
END IF;

IF-THEN оператор являются простейшей формой IF. Операторы между THEN и
END IF будут выполнены, если условие истинно. В противном случае они
пропускаются.

Пример:

IF v_user_id <> 0 THEN
    UPDATE users SET email = v_email WHERE user_id = v_user_id;
END IF;

IF-THEN-ELSE

IF boolean-expression THEN
    statements
ELSE
    statements
END IF;

Операторы IF-THEN-ELSE добавляют к IF-THEN, позволяя вам указать
альтернативный набор операторов, которые должны выполняться, если
условие не выполняется. (Обратите внимание, что это включает случай,
когда условие оценивается как NULL).

Примеры:

IF parentid IS NULL OR parentid = ''
THEN
    RETURN fullname;
ELSE
    RETURN hp_true_filename(parentid) || '/' || fullname;
END IF;

IF v_count > 0 THEN
    INSERT INTO users_count (count) VALUES (v_count);
    RETURN 't';
ELSE
    RETURN 'f';
END IF;

IF-THEN-ELSIF

IF boolean-expression THEN
    statements
[ ELSIF boolean-expression THEN
    statements
[ ELSIF boolean-expression THEN
    statements
    ...
]
]
[ ELSE
    statements ]
END IF;

Иногда есть больше, чем просто две альтернативы. IF-THEN-ELSIF
предоставляет удобный метод проверки нескольких альтернатив по очереди.
Условия IF проверяются последовательно, пока не будет найдено первое
истинное условие. Затем выполняются соответствующие операторы, после
чего управление переходит к следующему оператору после END IF. (Любые
последующие условия IF не проверяются). Если ни одно из условий IF
выполняется, выполняется блок ELSE (если есть).

Вот пример:

IF number = 0 THEN
    result := 'zero';
ELSIF number > 0 THEN
    result := 'positive';
ELSIF number < 0 THEN
    result := 'negative';
ELSE
    -- hmm, the only other possibility is that number is null
    result := 'NULL';
END IF;

Ключевое слово ELSIF также может быть написано как ELSEIF.

Альтернативный способ выполнения той же задачи — вложение операторов
IF-THEN-ELSE, как в следующем примере:

IF demo_row.sex = 'm' THEN
    pretty_sex := 'man';
ELSE
    IF demo_row.sex = 'f' THEN
        pretty_sex := 'woman';
    END IF;
END IF;

Однако этот метод требует написания соответствующего END IF для каждого
IF, поэтому он намного более громоздкий, чем использование ELSIF когда
есть много альтернатив.

Простой CASE

CASE search-expression
    WHEN expression [, expression [ ... ]] THEN
      statements
  [ WHEN expression [, expression [ ... ]] THEN
      statements
    ... ]
  [ ELSE
      statements ]
END CASE;

Простая форма CASE обеспечивает условное выполнение, основанное на
равенстве операндов. search-expression оценивается (один раз) и
последовательно сравнивается с каждым expression в предложениях WHEN.
Если совпадение найдено, то соответствующие statements выполняются, а
затем управление переходит к следующему оператору после END CASE.
(Последующие выражения WHEN не оцениваются). Если совпадений не найдено,
выполняются операторы ELSE; но если ELSE нет, то возникает
исключение CASE_NOT_FOUND.

Простой пример:

CASE x
    WHEN 1, 2 THEN
        msg := 'one or two';
    ELSE
        msg := 'other value than one or two';
END CASE;

Поисковое выражение CASE

CASE
    WHEN boolean-expression THEN
      statements
  [ WHEN boolean-expression THEN
      statements
    ... ]
  [ ELSE
      statements ]
END CASE;

Поисковое выражение CASE обеспечивает условное выполнение, основанное на
истинности логических выражений. boolean-expression каждого предложения
WHEN вычисляется по очереди, пока не будет найдено одно, которое выдает
true. Затем выполняются соответствующие statements, а затем управление
переходит к следующему оператору после END CASE. (Последующие выражения
WHEN не оцениваются). Если истинный результат не найден, выполняются
statements в блоке ELSE ; но если ELSE нет, то возникает исключение
CASE_NOT_FOUND.

Пример:

CASE
    WHEN x BETWEEN 0 AND 10 THEN
        msg := 'value is between zero and ten';
    WHEN x BETWEEN 11 AND 20 THEN
        msg := 'value is between eleven and twenty';
END CASE;

Эта форма CASE полностью эквивалентна IF-THEN-ELSIF, за исключением
правила, согласно которому достижение пропущенного предложения ELSE
приводит к ошибке, а не к бездействию.

Простые циклы

С помощью операторов LOOP, EXIT, CONTINUE, WHILE, FOR и FOREACH вы
можете настроить функцию PL/pgSQL на повторение ряда команд.

LOOP

[ <<label>> ]
LOOP
    statements
END LOOP [ label ];

LOOP определяет безусловный цикл, который повторяется бесконечно, пока
не будет завершен с помощью оператора EXIT или RETURN. Необязательная
label может использоваться операторами EXIT и CONTINUE во вложенных
циклах, чтобы указать, к какому циклу относятся эти операторы.

EXIT

EXIT [ label ] [ WHEN boolean-expression ];

Если label не указана, самый внутренний цикл завершается, а затем
выполняется оператор, следующий за END LOOP. Если указана label, это
должна быть метка текущего или некоторого внешнего уровня вложенного
цикла или блока. Затем именованный цикл или блок завершается, и
управление продолжается оператором после соответствующего END цикла /
блока.

Если указано WHEN, выход из цикла происходит только в том случае, если
boolean-expression имеет значение true. В противном случае управление
передается оператору после EXIT.

EXIT может использоваться со всеми типами циклов; он не ограничен
использованием с безусловными циклами.

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

Примеры:

LOOP
    -- some computations
    IF count > 0 THEN
        EXIT;  -- exit loop
    END IF;
END LOOP;

LOOP
    -- some computations
    EXIT WHEN count > 0;  -- same result as previous example
END LOOP;

<<ablock>>
BEGIN
    -- some computations
    IF stocks > 100000 THEN
        EXIT ablock;  -- causes exit from the BEGIN block
    END IF;
    -- computations here will be skipped when stocks > 100000
END;

CONTINUE

CONTINUE [ label ] [ WHEN boolean-expression ];

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

Если указано WHEN, следующая итерация цикла начинается только в том
случае, если boolean-expression имеет значение true. В противном случае
управление переходит к оператору после CONTINUE.

CONTINUE можно использовать со всеми типами циклов; он не ограничен
использованием с безусловными циклами.

Примеры:

LOOP
    -- some computations
    EXIT WHEN count > 100;
    CONTINUE WHEN count < 50;
    -- some computations for count IN [50 .. 100]
END LOOP;

WHILE

[ <<label>> ]
WHILE boolean-expression LOOP
    statements
END LOOP [ label ];

Оператор WHILE повторяет последовательность операторов до тех пор, пока
boolean-expression оценивается как true. Выражение проверяется перед
каждой записью в теле цикла.

Например:

WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
    -- some computations here
END LOOP;

WHILE NOT done LOOP
    -- some computations here
END LOOP;

FOR (целочисленный вариант)

[ <<label>> ]
FOR name IN [ REVERSE ] expression .. expression [ BY expression ] LOOP
    statements
END LOOP [ label ];

Эта форма FOR создает цикл, который перебирает диапазон целых значений.
Имя переменной автоматически определяется как тип integer и существует
только внутри цикла (любое существующее определение имени переменной
игнорируется в цикле). Два выражения, дающие нижнюю и верхнюю границы
диапазона, оцениваются один раз при входе в цикл. Если предложение BY не
указано, шаг итерации равен 1, в противном случае это значение,
указанное в предложении BY, которое снова оценивается один раз при
входе в цикл. Если указано REVERSE то значение шага вычитается, а не
добавляется после каждой итерации.

Некоторые примеры целочисленных циклов FOR :

FOR i IN 1..10 LOOP
    -- i will take on the values 1,2,3,4,5,6,7,8,9,10 within the loop
END LOOP;

FOR i IN REVERSE 10..1 LOOP
    -- i will take on the values 10,9,8,7,6,5,4,3,2,1 within the loop
END LOOP;

FOR i IN REVERSE 10..1 BY 2 LOOP
    -- i will take on the values 10,8,6,4,2 within the loop
END LOOP;

Если нижняя граница больше, чем верхняя граница (или меньше, в
случае REVERSE), тело цикла вообще не выполняется. Ошибка не возникает.

Если к циклу FOR прикреплена label то на целочисленную переменную цикла
можно ссылаться с помощью квалифицированного имени, используя эту label.

Цикл по результатам запроса

Используя цикл FOR другого типа, вы можете перебирать результаты запроса
и соответствующим образом манипулировать этими данными. Синтаксис:

[ <<label>> ]
FOR target IN query LOOP
    statements
END LOOP [ label ];

target — это переменная записи, переменная строки или разделенный
запятыми список скалярных переменных. target назначается последовательно
каждой строке, полученной в результате query и тело цикла выполняется
для каждой строки.

Пример:

CREATE FUNCTION refresh_mviews() RETURNS integer AS $$
DECLARE
    mviews RECORD;
BEGIN
    RAISE NOTICE 'Refreshing all materialized views...';

    FOR mviews IN
       SELECT n.nspname AS mv_schema,
              c.relname AS mv_name,
              pg_catalog.pg_get_userbyid(c.relowner) AS owner
         FROM pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace)
        WHERE c.relkind = 'm'
     ORDER BY 1
    LOOP

        -- Now "mviews" has one record with information about the materialized view

        RAISE NOTICE 'Refreshing materialized view %.% (owner: %)...',
                     quote_ident(mviews.mv_schema),
                     quote_ident(mviews.mv_name),
                     quote_ident(mviews.owner);
        EXECUTE format('REFRESH MATERIALIZED VIEW %I.%I', mviews.mv_schema, mviews.mv_name);
    END LOOP;

    RAISE NOTICE 'Done refreshing materialized views.';
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

Если цикл завершается оператором EXIT, последнее назначенное значение
строки все еще доступно после цикла.

Запрос, используемый в операторе FOR этого типа, может быть любой командой
SQL, которая возвращает строки вызывающей стороне: SELECT является
наиболее распространенным случаем, но вы также можете использовать
INSERT, UPDATE или DELETE с предложением RETURNING. Некоторые
служебные команды, такие как EXPLAIN, тоже будут работать.

Переменные PL/pgSQL подставляются в текст запроса, и план запроса
кэшируется для возможного повторного использования, как подробно описано
в разделе Подстановка переменных и разделе Планирование кэширования.

FOR-IN-EXECUTE — это еще один способ перебора строк:

[ <<label>> ]
FOR target IN EXECUTE text_expression [ USING expression [, ... ] ] LOOP
    statements
END LOOP [ label ];

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

Другой способ указать запрос, результаты которого должны быть повторены,
объявить его как курсор. Это описано в разделе Цикл по результату курсора.

Цикл по массивам

Цикл FOREACH во многом похож на цикл FOR, но вместо итерации по
строкам, возвращаемым запросом SQL, он выполняет итерацию по элементам
значения массива. (Как правило, FOREACH предназначен для циклического
прохождения по компонентам составного выражения; в будущем могут быть
добавлены варианты циклического перемещения по композитам, кроме
массивов). Оператор FOREACH для циклического перемещения по массиву:

[ <<label>> ]
FOREACH target [ SLICE number ] IN ARRAY expression LOOP
    statements
END LOOP [ label ];

Без SLICE или, если SLICE 0, цикл перебирает отдельные элементы
массива, созданного путем вычисления expression. target переменная
назначается каждому значению элемента в последовательности, и тело цикла
выполняется для каждого элемента. Вот пример зацикливания элементов
целочисленного массива:

CREATE FUNCTION sum(int[]) RETURNS int8 AS $$
DECLARE
  s int8 := 0;
  x int;
BEGIN
  FOREACH x IN ARRAY $1
  LOOP
    s := s + x;
  END LOOP;
  RETURN s;
END;
$$ LANGUAGE plpgsql;

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

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

CREATE FUNCTION scan_rows(int[]) RETURNS void AS $$
DECLARE
  x int[];
BEGIN
  FOREACH x SLICE 1 IN ARRAY $1
  LOOP
    RAISE NOTICE 'row = %', x;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

SELECT scan_rows(ARRAY[[1,2,3],[4,5,6],[7,8,9],[10,11,12]]);

NOTICE:  row = {1,2,3}
NOTICE:  row = {4,5,6}
NOTICE:  row = {7,8,9}
NOTICE:  row = {10,11,12}

Ошибки захвата

По умолчанию любая ошибка, возникающая в функции PL/pgSQL, прерывает
выполнение функции, а также окружающей транзакции. Вы можете
перехватывать ошибки и восстанавливать их, используя блок BEGIN с
предложением EXCEPTION. Синтаксис является расширением нормального
синтаксиса для блока BEGIN :

[ <<label>> ]
[ DECLARE
    declarations ]
BEGIN
    statements
EXCEPTION
    WHEN condition [ OR condition ... ] THEN
        handler_statements
    [ WHEN condition [ OR condition ... ] THEN
          handler_statements
      ... ]
END;

Если ошибки не возникает, эта форма блока просто выполняет все операторы
statements, а затем управление переходит к следующему оператору после
END. Но если в операторах возникает ошибка, дальнейшая обработка
statements прекращается, и управление переходит в список EXCEPTION. В
списке ищется первое условие condition соответствующее возникшей ошибке. Если
совпадение найдено, выполняются соответствующие
handler_statements, а затем управление переходит к следующему
оператору после END. Если совпадение не найдено, ошибка
распространяется так, как если бы предложение EXCEPTION вообще не было:
ошибка может быть перехвачена включающим блоком с помощью EXCEPTION,
или, если его нет, он прерывает обработку функции.

Имена condition могут быть любыми из указанных в Коды ошибок QHB. Название
категории соответствует любой ошибке в ее категории. Имя специального
условия OTHERS соответствует каждому типу ошибки, кроме QUERY_CANCELED
и ASSERT_FAILURE. (Можно, но часто неразумно, отлавливать эти два типа
ошибок по имени). Имена условий не чувствительны к регистру. Кроме того,
условие ошибки может быть указано кодом SQLSTATE; например это
эквивалентно:

WHEN division_by_zero THEN ...
WHEN SQLSTATE '22012' THEN ...

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

Когда в предложении EXCEPTION обнаружена ошибка, локальные переменные
функции PL/pgSQL остаются такими же, какими они были при возникновении
ошибки, но все изменения в постоянном состоянии базы данных в блоке
откатываются. В качестве примера рассмотрим этот фрагмент:

INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones');
BEGIN
    UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones';
    x := x + 1;
    y := x / 0;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'caught division_by_zero';
        RETURN x;
END;

Когда элемент управления достигает назначения для y, он потерпит
неудачу с ошибкой Division_by_zero. Это будет поймано предложением
EXCEPTION. Значение, возвращаемое в операторе RETURN будет увеличенным
значением x, но влияние команды UPDATE будет отменено. Однако команда
INSERT предшествующая блоку, не откатывается, поэтому в результате база
данных содержит Tom Jones, а не Joe Jones.

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

Пример 2. Исключения с UPDATE/INSERT

В этом примере используется обработка исключений для выполнения UPDATE
или INSERT, в зависимости от ситуации. Рекомендуется, чтобы приложения
использовали INSERT с ON CONFLICT DO UPDATE а не использовали этот
шаблон. Этот пример служит главным образом для иллюстрации использования
структур потока управления PL/pgSQL :

CREATE TABLE db (a INT PRIMARY KEY, b TEXT);

CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
$$
BEGIN
    LOOP
        -- first try to update the key
        UPDATE db SET b = data WHERE a = key;
        IF found THEN
            RETURN;
        END IF;
        -- not there, so try to insert the key
        -- if someone else inserts the same key concurrently,
        -- we could get a unique-key failure
        BEGIN
            INSERT INTO db(a,b) VALUES (key, data);
            RETURN;
        EXCEPTION WHEN unique_violation THEN
            -- Do nothing, and loop to try the UPDATE again.
        END;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

SELECT merge_db(1, 'david');
SELECT merge_db(1, 'dennis');

Это кодирование предполагает, что ошибка unique_violation вызвана
INSERT, а не, скажем, INSERT в триггерной функции таблицы. Это может
также привести к неправильной работе, если в таблице имеется более
одного уникального индекса, поскольку он будет повторять операцию
независимо от того, какой индекс вызвал ошибку. Больше безопасности
можно было бы получить, используя функции, обсуждаемые далее, чтобы
проверить, была ли ожидаемая ошибка в ловушке.

Получение информации об ошибке

Обработчикам исключений часто требуется идентифицировать конкретную
возникшую ошибку. Есть два способа получить информацию о текущем
исключении в PL/pgSQL: специальные переменные и команда GET STACKED
DIAGNOSTICS.

В обработчике исключений специальная переменная SQLSTATE содержит код
ошибки, соответствующий SQLSTATE исключению . Специальная переменная SQLERRM содержит
сообщение об ошибке, связанной с исключением. Эти переменные не
определены вне обработчиков исключений.

Внутри обработчика исключений можно также получить информацию о текущем
исключении с помощью команды GET STACKED DIAGNOSTICS, которая имеет
вид:

GET STACKED DIAGNOSTICS variable { = | := } item [, ... ];

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

Таблица 2. Элементы диагностики ошибок

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

Вот пример:

DECLARE
  text_var1 text;
  text_var2 text;
  text_var3 text;
BEGIN
  -- some processing which might cause an exception
  ...
EXCEPTION WHEN OTHERS THEN
  GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT,
                          text_var2 = PG_EXCEPTION_DETAIL,
                          text_var3 = PG_EXCEPTION_HINT;
END;

Получение информации о месте выполнения

Команда GET DIAGNOSTICS, ранее описанная в разделе Получение статуса результата, извлекает
информацию о текущем состоянии выполнения (в то время как команда GET
STACKED DIAGNOSTICS рассмотренная выше, сообщает информацию о состоянии
выполнения на момент предыдущей ошибки). Его элемент состояния
PG_CONTEXT полезен для определения текущего местоположения выполнения.
PG_CONTEXT возвращает текстовую строку со строками (строками),
описывающими стек вызовов. Первая строка ссылается на текущую функцию и
текущую команду GET DIAGNOSTICS. Вторая и любые последующие строки
относятся к вызывающим функциям дальше по стеку вызовов. Например:

CREATE OR REPLACE FUNCTION outer_func() RETURNS integer AS $$
BEGIN
  RETURN inner_func();
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION inner_func() RETURNS integer AS $$
DECLARE
  stack text;
BEGIN
  GET DIAGNOSTICS stack = PG_CONTEXT;
  RAISE NOTICE E'--- Call Stack ---n%', stack;
  RETURN 1;
END;
$$ LANGUAGE plpgsql;

SELECT outer_func();

NOTICE:  --- Call Stack ---
PL/pgSQL function inner_func() line 5 at GET DIAGNOSTICS
PL/pgSQL function outer_func() line 3 at RETURN
CONTEXT:  PL/pgSQL function outer_func() line 3 at RETURN
 outer_func
 ------------
           1
(1 row)

GET STACKED DIAGNOSTICS … PG_EXCEPTION_CONTEXT возвращает тот же вид
трассировки стека, но описывает местоположение, в котором была
обнаружена ошибка, а не текущее местоположение.

Курсоры

Вместо одновременного выполнения всего запроса можно установить курсор,
который инкапсулирует запрос, и затем прочитать результат запроса по
несколько строк за раз. Одна из причин этого состоит в том, чтобы
избежать переполнения памяти, когда результат содержит большое
количество строк. (Однако пользователям PL/pgSQL обычно не нужно
беспокоиться об этом, поскольку циклы FOR автоматически используют
курсор внутри, чтобы избежать проблем с памятью). Более интересным
является возвращение ссылки на курсор, созданный функцией, что позволяет
звонящий, чтобы прочитать строки. Это обеспечивает эффективный способ
возврата больших наборов строк из функций.

Объявление переменных курсора

Весь доступ к курсорам в PL/pgSQL осуществляется через переменные
курсора, которые всегда имеют специальный тип данных refcursor. Один из
способов создать курсорную переменную — просто объявить ее как
переменную типа refcursor. Другой способ — использовать синтаксис
объявления курсора, который в общем случае:

name [ [ NO ] SCROLL ] CURSOR [ ( arguments ) ] FOR query;

(FOR может быть заменен на IS для совместимости с Oracle). Если указан
SCROLL, курсор будет способен прокручиваться назад; если не указан NO
SCROLL, обратные выборки будут отклонены; если ни одна из спецификаций
не появляется, это зависит от запроса, будут ли разрешены обратные
выборки. arguments, если указан, представляет собой разделенный
запятыми список типов name datatype пар, которые определяют имена,
которые должны быть заменены значениями параметров в данном запросе.
Фактические значения для замены этих имен будут указаны позже, когда
курсор будет открыт.

Несколько примеров:

DECLARE
    curs1 refcursor;
    curs2 CURSOR FOR SELECT * FROM tenk1;
    curs3 CURSOR (key integer) FOR SELECT * FROM tenk1 WHERE unique1 = key;

Все три из этих переменных имеют тип данных refcursor, но первая
может использоваться с любым запросом, в то время как вторая имеет
полностью определенный запрос, уже связанный с ним, а последняя имеет
параметризованный запрос, связанный с ним. ( key будет заменен
целочисленным значением параметра при открытии курсора). Переменная
curs1 называется несвязанной, поскольку она не связана ни с каким
конкретным запросом.

Открытие курсоров

Прежде чем курсор можно использовать для извлечения строк, его
необходимо открыть. (Это действие эквивалентно команде SQL DECLARE
CURSOR). PL/pgSQL имеет три формы оператора OPEN, две из которых
используют несвязанные переменные курсора, а третья использует связанную
переменную курсора.

Заметка
Переменные связанного курсора также можно использовать без явного
открытия курсора с помощью оператора FOR описанного в разделе Цикл по результату курсора.

OPEN FOR query

OPEN unbound_cursorvar [ [ NO ] SCROLL ] FOR query;

Переменная курсора открывается и получает заданный запрос для
выполнения. Курсор уже нельзя открыть, и он должен быть объявлен как
несвязанная переменная курсора (то есть как простая переменная refcursor
). Запрос должен быть SELECT или чем-то еще, что возвращает строки
(например, EXPLAIN ). Запрос обрабатывается так же, как и другие команды
SQL в PL/pgSQL : имена переменных PL/pgSQL заменяются, и план
запроса кэшируется для возможного повторного использования. Когда
переменная PL/pgSQL подставляется в запрос курсора, подставляется
значение, которое она имеет во время OPEN ; Последующие изменения в
переменной не влияют на поведение курсора. Параметры SCROLL и NO SCROLL
имеют те же значения, что и для привязанного курсора.

Пример:

OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;

OPEN FOR EXECUTE

OPEN unbound_cursorvar [ [ NO ] SCROLL ] FOR EXECUTE query_string
                                     [ USING expression [, ... ] ];

Переменная курсора открывается и получает заданный запрос для
выполнения. Курсор уже нельзя открыть, и он должен быть объявлен как
несвязанная переменная курсора (то есть как простая переменная refcursor
). Запрос указывается как строковое выражение, так же, как в команде
EXECUTE. Как обычно, это обеспечивает гибкость, поэтому план запроса
может варьироваться от одного прогона к следующему (см. раздел Планирование кэширования
), а также означает, что в командной строке не производится подстановка
переменных. Как и в случае EXECUTE, значения параметров могут быть
вставлены в динамическую команду через format() и USING. Параметры
SCROLL и NO SCROLL имеют те же значения, что и для привязанного курсора.

Пример:

OPEN curs1 FOR EXECUTE format('SELECT * FROM %I WHERE col1 = $1',tabname) USING keyvalue;

В этом примере имя таблицы вставляется в запрос через format().
Значение сравнения для col1 вставляется через параметр USING, поэтому
не нужно col1.

Открытие связанного курсора

OPEN bound_cursorvar [ ( [ argument_name := ] argument_value [, ...] ) ];

Эта форма OPEN используется для открытия переменной курсора, запрос
которой был связан с ней, когда она была объявлена. Курсор уже не может
быть открыт. Список фактических выражений значений аргументов должен
появляться тогда и только тогда, когда указывалось, что курсор принимает
аргументы. Эти значения будут подставлены в запрос.

План запроса для привязанного курсора всегда считается кэшируемым; в
этом случае нет эквивалента EXECUTE. Обратите внимание, что SCROLL и NO
SCROLL не могут быть указаны в OPEN, поскольку поведение прокрутки
курсора уже было определено.

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

Примеры (они используют примеры объявления курсора выше):

OPEN curs2;
OPEN curs3(42);
OPEN curs3(key := 42);

Поскольку подстановка переменных выполняется в запросе связанного
курсора, на самом деле существует два способа передачи значений в
курсор: либо с явным аргументом OPEN, либо неявным образом путем ссылки
на переменную PL/pgSQL в запросе. Однако в него будут подставлены
только переменные, объявленные до объявления ограниченного курсора. В
любом случае передаваемое значение определяется во время OPEN.
Например, другой способ получить тот же эффект, что и в приведенном выше
примере с curs3 :

DECLARE
    key integer;
    curs4 CURSOR FOR SELECT * FROM tenk1 WHERE unique1 = key;
BEGIN
    key := 42;
    OPEN curs4;

Использование курсоров

Когда курсор открыт, им можно манипулировать с помощью описанных здесь
операторов.

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

Все порталы неявно закрываются в конце транзакции. Поэтому значение
refcursor можно использовать для ссылки на открытый курсор только до
конца транзакции.

FETCH

FETCH [ direction { FROM | IN } ] cursor INTO target;

FETCH извлекает следующую строку из курсора в цель, которая может быть
переменной строки, переменной записи или списком простых переменных,
разделенных запятыми, подобно SELECT INTO. Если следующей строки нет,
для цели устанавливается значение NULL. Как и в случае SELECT INTO,
специальная переменная FOUND может быть проверена, чтобы увидеть, была
ли получена строка или нет.

Предложение direction может быть любым из вариантов, разрешенных в
команде SQL FETCH, кроме тех, которые могут извлекать более одной
строки; а именно, это может быть NEXT, PRIOR, FIRST, LAST, ABSOLUTE
count, RELATIVE count, FORWARD или BACKWARD. direction пропуска
совпадает с указанием NEXT. В формах, использующих count, count может
быть любым целочисленным выражением (в отличие от команды SQL FETCH,
которая допускает только целочисленную константу). Значения direction
которые требуют перемещения назад, могут потерпеть неудачу, если курсор
не был объявлен или открыт с помощью опции SCROLL.

cursor должен быть именем переменной refcursor которая ссылается на
портал открытого курсора.

Примеры:

FETCH curs1 INTO rowvar;
FETCH curs2 INTO foo, bar, baz;
FETCH LAST FROM curs3 INTO x, y;
FETCH RELATIVE -2 FROM curs4 INTO x;

MOVE

MOVE [ direction { FROM | IN } ] cursor;

MOVE перемещает курсор без получения каких-либо данных. MOVE работает
точно так же, как команда FETCH, за исключением того, что он только
перемещает курсор и не возвращает перемещенную строку. Как и в случае
SELECT INTO, специальная переменная FOUND может быть проверена, чтобы
увидеть, была ли следующая строка для перемещения.

Примеры:

MOVE curs1;
MOVE LAST FROM curs3;
MOVE RELATIVE -2 FROM curs4;
MOVE FORWARD 2 FROM curs4;

UPDATE/DELETE WHERE CURRENT OF

UPDATE table SET ... WHERE CURRENT OF cursor;
DELETE FROM table WHERE CURRENT OF cursor;

Когда курсор расположен в строке таблицы, эту строку можно обновить или
удалить, используя курсор для идентификации строки. Существуют
ограничения на то, каким может быть запрос курсора (в частности, нет
группировки), и лучше всего использовать FOR UPDATE в курсоре. Для
получения дополнительной информации см. Справочную страницу DECLARE.

Пример:

UPDATE foo SET dataval = myval WHERE CURRENT OF curs1;

CLOSE

CLOSE cursor;

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

Пример:

CLOSE curs1;

Возврат курсора

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

Имя портала, используемое для курсора, может быть указано программистом
или сгенерировано автоматически. Чтобы указать имя портала, просто
присвойте строку переменной refcursor перед ее открытием. Строковое
значение переменной refcursor будет использоваться OPEN в качестве имени
базового портала. Однако, если переменная refcursor имеет значение null,
OPEN автоматически генерирует имя, которое не конфликтует с каким-либо
существующим порталом, и присваивает его переменной refcursor.

Заметка
Связанная переменная курсора инициализируется строковым значением,
представляющим его имя, так что имя портала совпадает с именем
переменной курсора, если только программист не переопределит его
присваиванием перед открытием курсора. Но несвязанная переменная курсора
по умолчанию изначально имеет нулевое значение, поэтому она получит
автоматически сгенерированное уникальное имя, если оно не будет
переопределено.

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

CREATE TABLE test (col text);
INSERT INTO test VALUES ('123');

CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS '
BEGIN
    OPEN $1 FOR SELECT col FROM test;
    RETURN $1;
END;
' LANGUAGE plpgsql;

BEGIN;
SELECT reffunc('funccursor');
FETCH ALL IN funccursor;
COMMIT;

В следующем примере используется автоматическая генерация имени курсора:

CREATE FUNCTION reffunc2() RETURNS refcursor AS '
DECLARE
    ref refcursor;
BEGIN
    OPEN ref FOR SELECT col FROM test;
    RETURN ref;
END;
' LANGUAGE plpgsql;

-- need to be in a transaction to use cursors.
BEGIN;
SELECT reffunc2();

      reffunc2
--------------------
 <unnamed cursor 1>
(1 row)

FETCH ALL IN "<unnamed cursor 1>";
COMMIT;

В следующем примере показан один способ вернуть несколько курсоров из
одной функции:

CREATE FUNCTION myfunc(refcursor, refcursor) RETURNS SETOF refcursor AS $$
BEGIN
    OPEN $1 FOR SELECT * FROM table_1;
    RETURN NEXT $1;
    OPEN $2 FOR SELECT * FROM table_2;
    RETURN NEXT $2;
END;
$$ LANGUAGE plpgsql;

-- need to be in a transaction to use cursors.
BEGIN;

SELECT * FROM myfunc('a', 'b');

FETCH ALL FROM a;
FETCH ALL FROM b;
COMMIT;

Цикл по результату курсора

Существует вариант оператора FOR который позволяет перебирать строки,
возвращаемые курсором. Синтаксис:

[ <<label>> ]
FOR recordvar IN bound_cursorvar [ ( [ argument_name := ] argument_value [, ...] ) ] LOOP
    statements
END LOOP [ label ];

Переменная курсора должна быть привязана к какому-либо запросу, когда
она была объявлена, и ее уже нельзя открыть. Оператор FOR автоматически
открывает курсор и снова закрывает курсор при выходе из цикла. Список
фактических выражений значений аргументов должен появляться тогда и
только тогда, когда указывалось, что курсор принимает аргументы. Эти
значения будут подставлены в запросе точно так же, как во время OPEN
(см. раздел Открытие связанного курсора).

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

Управление транзакциями

В процедурах, вызываемых командой CALL, а также в блоках анонимного кода
(команда DO ), можно завершать транзакции с помощью команд COMMIT и
ROLLBACK. Новая транзакция запускается автоматически после завершения
транзакции с использованием этих команд, поэтому отдельной команды START
TRANSACTION нет. (Обратите внимание, что BEGIN и END имеют разные значения
в PL/pgSQL).

Простой пример:

CREATE PROCEDURE transaction_test1()
LANGUAGE plpgsql
AS $$
BEGIN
    FOR i IN 0..9 LOOP
        INSERT INTO test1 (a) VALUES (i);
        IF i % 2 = 0 THEN
            COMMIT;
        ELSE
            ROLLBACK;
        END IF;
    END LOOP;
END
$$;

CALL transaction_test1();

Новая транзакция начинается с характеристик транзакции по умолчанию,
таких как уровень изоляции транзакции. В случаях, когда транзакции
фиксируются в цикле, может быть желательно автоматически начинать новые
транзакции с теми же характеристиками, что и предыдущая. Команды COMMIT
AND CHAIN и ROLLBACK AND CHAIN выполняют это.

Управление транзакциями возможно только в CALL или DO из верхнего уровня
или во вложенных CALL или DO без какой-либо другой промежуточной
команды. Например, если стеком вызовов является
CALL proc1() → CALL proc2() → CALL proc3(), то вторая и третья процедуры могут выполнять
действия по управлению транзакциями. Но если стеком вызовов является
CALL proc1() → SELECT func2() → CALL proc3(), то последняя процедура не
может осуществлять управление транзакциями из-за промежуточного SELECT.

Особые соображения применимы к циклам курсора. Рассмотрим этот пример:

CREATE PROCEDURE transaction_test2()
LANGUAGE plpgsql
AS $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN SELECT * FROM test2 ORDER BY x LOOP
        INSERT INTO test1 (a) VALUES (r.x);
        COMMIT;
    END LOOP;
END;
$$;

CALL transaction_test2();

Обычно курсоры автоматически закрываются при фиксации транзакции. Однако
курсор, созданный как часть цикла, подобного этому, автоматически
преобразуется в удерживаемый курсор с помощью первого COMMIT или
ROLLBACK. Это означает, что курсор полностью вычисляется в первом
COMMIT или ROLLBACK а не по строкам. Курсор по-прежнему автоматически
удаляется после цикла, поэтому он в большинстве случаев невидим для
пользователя.

Команды транзакции не допускаются в циклах курсора, управляемых
командами, которые не предназначены только для чтения (например, UPDATE
… RETURNING).

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

Ошибки и сообщения

Вывод ошибок и сообщений

Используйте оператор RAISE чтобы выводить расширенные сообщения и
обрабатывать ошибки.

RAISE [ level ] 'format' [, expression [, ... ]] [ USING option = expression [, ... ] ];
RAISE [ level ] condition_name [ USING option = expression [, ... ] ];
RAISE [ level ] SQLSTATE 'sqlstate' [ USING option = expression [, ... ] ];
RAISE [ level ] USING option = expression [, ... ];
RAISE ;

Параметр level указывает серьезность ошибки. Допустимые уровни: DEBUG,
LOG, INFO, NOTICE, WARNING и EXCEPTION, по умолчанию используется
EXCEPTION. EXCEPTION вызывает ошибку (которая обычно прерывает текущую
транзакцию); другие уровни генерируют только сообщения с различными
уровнями приоритета. Независимо от того, передаются ли сообщения
определенного приоритета клиенту, записываются ли они в журнал сервера,
или и то, и другое контролируются переменными конфигурации
log_min_messages и client_min_messages. См. главу Конфигурация сервера для получения
дополнительной информации.

После level если он есть, вы можете написать format (который должен быть
простым строковым литералом, а не выражением). Строка формата определяет
текст сообщения об ошибке, о которой будет сообщено. За форматной
строкой могут следовать необязательные выражения аргумента, которые
будут вставлены в сообщение. Внутри строки формата % заменяется
строковым представлением значения следующего необязательного аргумента.
Напишите %% чтобы получить буквальный %. Количество аргументов должно
соответствовать количеству % заполнителей в строке формата, иначе при
компиляции функции возникает ошибка.

В этом примере значение v_job_id заменит % в строке:

RAISE NOTICE 'Calling cs_create_job(%)', v_job_id;

Вы можете прикрепить дополнительную информацию к отчету об ошибке,
написав USING а затем option = expression items. Каждое expression может
быть любым строковым выражением. Допустимые ключевые слова option:

Этот пример прервет транзакцию с данным сообщением об ошибке и
подсказкой:

RAISE EXCEPTION 'Nonexistent ID --> %', user_id
      USING HINT = 'Please check your user ID';

Эти два примера показывают эквивалентные способы установки SQLSTATE:

RAISE 'Duplicate user ID: %', user_id USING ERRCODE = 'unique_violation';
RAISE 'Duplicate user ID: %', user_id USING ERRCODE = '23505';

Существует второй синтаксис RAISE в котором основным аргументом является
имя условия или SQLSTATE, о котором необходимо сообщить, например:

RAISE division_by_zero;
RAISE SQLSTATE '22012';

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

RAISE unique_violation USING MESSAGE = 'Duplicate user ID: ' || user_id;

Еще один вариант — написать RAISE USING или RAISE level USING и
поместить все остальное в список USING.

Последний вариант RAISE не имеет параметров вообще. Эта форма может
использоваться только внутри предложения EXCEPTION блока BEGIN; это
приводит к тому, что ошибка, обрабатываемая в данный момент, будет
переброшена.

Заметка
До PostgreSQL 9.1 RAISE без параметров интерпретировалось как повторное
генерирование ошибки из блока, содержащего активный обработчик
исключений. Таким образом, предложение EXCEPTION, вложенное в этот
обработчик, не может его перехватить, даже если RAISE находится в блоке
вложенного предложения EXCEPTION. Это было сочтено удивительным, а
также несовместимым с Oracle PL/SQL.

Если ни имя условия, ни SQLSTATE не указаны в команде RAISE EXCEPTION,
по умолчанию используется ERRCODE_RAISE_EXCEPTION ( P0001 ). Если
текст сообщения не указан, по умолчанию используется имя условия или
SQLSTATE в качестве текста сообщения.

Заметка
При указании кода ошибки с помощью кода SQLSTATE вы не ограничены
предварительно определенными кодами ошибок, но можете выбрать любой код
ошибки, состоящий из пяти цифр и / или букв ASCII в верхнем регистре,
кроме 00000. Рекомендуется избегать выдачи кодов ошибок, которые
заканчиваются тремя нулями, потому что это коды категорий, которые могут
быть захвачены только путем захвата всей категории.

Проверка утверждений

Оператор ASSERT является удобным сокращением для вставки проверок
отладки в функции PL/pgSQL.

ASSERT condition [, message ];

condition является логическим выражением, которое, как ожидается, всегда
будет иметь значение true; если это так, оператор ASSERT больше ничего
не делает. Если результатом является ложь или ASSERT_FAILURE, то
возникает исключение ASSERT_FAILURE. (Если ошибка возникает при
оценке condition, она сообщается как нормальная ошибка).

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

Тестирование утверждений можно включить или выключить с помощью
параметра конфигурации plpgsql.check_asserts, который принимает
логическое значение; по умолчанию включено. Если этот параметр off то
операторы ASSERT ничего не делают.

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

Триггерные функции

PL/pgSQL может использоваться для определения триггерных функций при
изменении данных или событий базы данных. Триггерная функция создается с
помощью команды CREATE FUNCTION, объявляющей ее как функцию без
аргументов и возвращаемого типа trigger (для триггеров изменения данных)
или event_trigger (для триггеров событий базы данных). Специальные
локальные переменные с именем *TG_

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

Триггеры при изменении данных

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

Когда в качестве триггера вызывается функция PL/pgSQL, в блоке
верхнего уровня автоматически создаются несколько специальных
переменных. Вот они:

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

Триггеры уровня строки срабатывают до того, как они могут вернуть ноль, чтобы
сигнализировать диспетчеру триггеров пропустить оставшуюся часть
операции для этой строки (т.е. последующие триггеры не запускаются, и
INSERT/UPDATE/DELETE для этой строки не происходит). Если
возвращается ненулевое значение, то операция продолжается с этим
значением строки. Возвращение значения строки, отличного от исходного
значения NEW изменяет строку, которая будет вставлена или обновлена.
Таким образом, если триггерная функция хочет, чтобы инициирующее
действие успешно выполнялось без изменения значения строки, необходимо
вернуть NEW (или равное ему значение). Чтобы изменить строку, подлежащую
сохранению, можно заменить отдельные значения непосредственно в NEW и
вернуть измененный NEW, или создать полную новую запись / строку для
возврата. В случае до-триггера на DELETE, возвращаемое значение не
имеет прямого эффекта, но оно должно быть ненулевым, чтобы позволить
действию триггера продолжаться. Обратите внимание, что NEW является
нулем в триггерах DELETE, поэтому возвращать его обычно не имеет
смысла. Обычная идиома в триггерах DELETE — возвращать OLD.

INSTEAD OF (которые всегда являются триггерами уровня строки и могут
использоваться только в представлениях) могут возвращать ноль, чтобы
указать, что они не выполняли никаких обновлений и что оставшаяся часть
операции для этой строки должна быть пропущена (т.е. последующие
триггеры не срабатывают, и строка не учитывается в состоянии, затронутом
строками для окружающего INSERT/UPDATE/DELETE). В противном случае
должно быть возвращено ненулевое значение, чтобы сигнализировать, что
триггер выполнил запрошенную операцию. Для операций INSERT и UPDATE
возвращаемое значение должно быть NEW, которое триггерная функция может
изменить для поддержки INSERT RETURNING и UPDATE RETURNING (это также
повлияет на значение строки, передаваемое любым последующим триггерам
или передаваемое в специальную ссылку EXCLUDED alias в пределах Оператор
INSERT с предложением ON CONFLICT DO UPDATE). Для операций DELETE
возвращаемое значение должно быть OLD.

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

Пример 3 показывает вариант функции триггера в PL/pgSQL.

Пример 3. Функция запуска PL/pgSQL

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

CREATE TABLE emp (
    empname text,
    salary integer,
    last_date timestamp,
    last_user text
);

CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
    BEGIN
        -- Check that empname and salary are given
        IF NEW.empname IS NULL THEN
            RAISE EXCEPTION 'empname cannot be null';
        END IF;
        IF NEW.salary IS NULL THEN
            RAISE EXCEPTION '% cannot have null salary', NEW.empname;
        END IF;

        -- Who works for us when they must pay for it?
        IF NEW.salary < 0 THEN
            RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;
        END IF;

        -- Remember who changed the payroll when
        NEW.last_date := current_timestamp;
        NEW.last_user := current_user;
        RETURN NEW;
    END;
$emp_stamp$ LANGUAGE plpgsql;

CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
    FOR EACH ROW EXECUTE FUNCTION emp_stamp();

Другой способ регистрации изменений в таблице заключается в создании
новой таблицы, содержащей строку для каждой происходящей вставки,
обновления или удаления. Этот подход можно рассматривать как аудит
изменений в таблице. Пример 4 показывает вариант функции запуска
аудита в PL/pgSQL.

Пример 4. Функция запуска PL/pgSQL для аудита

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

CREATE TABLE emp (
    empname           text NOT NULL,
    salary            integer
);

CREATE TABLE emp_audit(
    operation         char(1)   NOT NULL,
    stamp             timestamp NOT NULL,
    userid            text      NOT NULL,
    empname           text      NOT NULL,
    salary integer
);

CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
    BEGIN
        --
        -- Create a row in emp_audit to reflect the operation performed on emp,
        -- making use of the special variable TG_OP to work out the operation.
        --
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO emp_audit SELECT 'I', now(), user, NEW.*;
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
$emp_audit$ LANGUAGE plpgsql;

CREATE TRIGGER emp_audit
AFTER INSERT OR UPDATE OR DELETE ON emp
    FOR EACH ROW EXECUTE FUNCTION process_emp_audit();

Вариант предыдущего примера использует представление, соединяющее
основную таблицу с таблицей аудита, чтобы показать, когда каждая запись
последний раз изменялась. Этот подход по-прежнему регистрирует полный
контрольный журнал изменений таблицы, но также представляет упрощенное
представление контрольного журнала, показывая только последнюю
измененную временную метку, полученную из контрольного журнала для
каждой записи. Пример 5 показывает вариант триггера аудита для
представления в PL/pgSQL.

Пример 5. Функция запуска PL/pgSQL View для аудита

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

CREATE TABLE emp (
    empname           text PRIMARY KEY,
    salary            integer
);

CREATE TABLE emp_audit(
    operation         char(1)   NOT NULL,
    userid            text      NOT NULL,
    empname           text      NOT NULL,
    salary            integer,
    stamp             timestamp NOT NULL
);

CREATE VIEW emp_view AS
    SELECT e.empname,
           e.salary,
           max(ea.stamp) AS last_updated
      FROM emp e
      LEFT JOIN emp_audit ea ON ea.empname = e.empname
     GROUP BY 1, 2;

CREATE OR REPLACE FUNCTION update_emp_view() RETURNS TRIGGER AS $$
    BEGIN
        --
        -- Perform the required operation on emp, and create a row in emp_audit
        -- to reflect the change made to emp.
        --
        IF (TG_OP = 'DELETE') THEN
            DELETE FROM emp WHERE empname = OLD.empname;
            IF NOT FOUND THEN RETURN NULL; END IF;

            OLD.last_updated = now();
            INSERT INTO emp_audit VALUES('D', user, OLD.*);
            RETURN OLD;
        ELSIF (TG_OP = 'UPDATE') THEN
            UPDATE emp SET salary = NEW.salary WHERE empname = OLD.empname;
            IF NOT FOUND THEN RETURN NULL; END IF;

            NEW.last_updated = now();
            INSERT INTO emp_audit VALUES('U', user, NEW.*);
            RETURN NEW;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO emp VALUES(NEW.empname, NEW.salary);

            NEW.last_updated = now();
            INSERT INTO emp_audit VALUES('I', user, NEW.*);
            RETURN NEW;
        END IF;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER emp_audit
INSTEAD OF INSERT OR UPDATE OR DELETE ON emp_view
    FOR EACH ROW EXECUTE FUNCTION update_emp_view();

Одним из применений триггеров является ведение сводной таблицы другой
таблицы. Результирующая сводка может использоваться вместо исходной
таблицы для определенных запросов — часто с значительно сокращенным
временем выполнения. Этот метод обычно используется в хранилищах данных,
где таблицы измеренных или наблюдаемых данных (так называемые таблицы
фактов) могут быть чрезвычайно большими. В примере 6 показан вариант
функции триггера в PL/pgSQL, которая поддерживает сводную таблицу для
таблицы фактов в хранилище данных.

Пример 6. Функция запуска PL/pgSQL для ведения сводной таблицы

Схема, подробно описанная здесь, частично основана на примере магазина
продуктов из набора инструментов хранилища данных Ральфа Кимбалла.

--
-- Main tables - time dimension and sales fact.
--
CREATE TABLE time_dimension (
    time_key                    integer NOT NULL,
    day_of_week                 integer NOT NULL,
    day_of_month                integer NOT NULL,
    month                       integer NOT NULL,
    quarter                     integer NOT NULL,
    year                        integer NOT NULL
);
CREATE UNIQUE INDEX time_dimension_key ON time_dimension(time_key);

CREATE TABLE sales_fact (
    time_key                    integer NOT NULL,
    product_key                 integer NOT NULL,
    store_key                   integer NOT NULL,
    amount_sold                 numeric(12,2) NOT NULL,
    units_sold                  integer NOT NULL,
    amount_cost                 numeric(12,2) NOT NULL
);
CREATE INDEX sales_fact_time ON sales_fact(time_key);

--
-- Summary table - sales by time.
--
CREATE TABLE sales_summary_bytime (
    time_key                    integer NOT NULL,
    amount_sold                 numeric(15,2) NOT NULL,
    units_sold                  numeric(12) NOT NULL,
    amount_cost                 numeric(15,2) NOT NULL
);
CREATE UNIQUE INDEX sales_summary_bytime_key ON sales_summary_bytime(time_key);

--
-- Function and trigger to amend summarized column(s) on UPDATE, INSERT, DELETE.
--
CREATE OR REPLACE FUNCTION maint_sales_summary_bytime() RETURNS TRIGGER
AS $maint_sales_summary_bytime$
    DECLARE
        delta_time_key          integer;
        delta_amount_sold       numeric(15,2);
        delta_units_sold        numeric(12);
        delta_amount_cost       numeric(15,2);
    BEGIN

        -- Work out the increment/decrement amount(s).
        IF (TG_OP = 'DELETE') THEN

            delta_time_key = OLD.time_key;
            delta_amount_sold = -1 * OLD.amount_sold;
            delta_units_sold = -1 * OLD.units_sold;
            delta_amount_cost = -1 * OLD.amount_cost;

        ELSIF (TG_OP = 'UPDATE') THEN

            -- forbid updates that change the time_key -
            -- (probably not too onerous, as DELETE + INSERT is how most
            -- changes will be made).
            IF ( OLD.time_key != NEW.time_key) THEN
                RAISE EXCEPTION 'Update of time_key : % -> % not allowed',
                                                      OLD.time_key, NEW.time_key;
            END IF;

            delta_time_key = OLD.time_key;
            delta_amount_sold = NEW.amount_sold - OLD.amount_sold;
            delta_units_sold = NEW.units_sold - OLD.units_sold;
            delta_amount_cost = NEW.amount_cost - OLD.amount_cost;

        ELSIF (TG_OP = 'INSERT') THEN

            delta_time_key = NEW.time_key;
            delta_amount_sold = NEW.amount_sold;
            delta_units_sold = NEW.units_sold;
            delta_amount_cost = NEW.amount_cost;

        END IF;


        -- Insert or update the summary row with the new values.
        <<insert_update>>
        LOOP
            UPDATE sales_summary_bytime
                SET amount_sold = amount_sold + delta_amount_sold,
                    units_sold = units_sold + delta_units_sold,
                    amount_cost = amount_cost + delta_amount_cost
                WHERE time_key = delta_time_key;

            EXIT insert_update WHEN found;

            BEGIN
                INSERT INTO sales_summary_bytime (
                            time_key,
                            amount_sold,
                            units_sold,
                            amount_cost)
                    VALUES (
                            delta_time_key,
                            delta_amount_sold,
                            delta_units_sold,
                            delta_amount_cost
                           );

                EXIT insert_update;

            EXCEPTION
                WHEN UNIQUE_VIOLATION THEN
                    -- do nothing
            END;
        END LOOP insert_update;

        RETURN NULL;

    END;
$maint_sales_summary_bytime$ LANGUAGE plpgsql;

CREATE TRIGGER maint_sales_summary_bytime
AFTER INSERT OR UPDATE OR DELETE ON sales_fact
    FOR EACH ROW EXECUTE FUNCTION maint_sales_summary_bytime();

INSERT INTO sales_fact VALUES(1,1,1,10,3,15);
INSERT INTO sales_fact VALUES(1,2,1,20,5,35);
INSERT INTO sales_fact VALUES(2,2,1,40,15,135);
INSERT INTO sales_fact VALUES(2,3,1,10,1,13);
SELECT * FROM sales_summary_bytime;
DELETE FROM sales_fact WHERE product_key = 1;
SELECT * FROM sales_summary_bytime;
UPDATE sales_fact SET units_sold = units_sold * 2;
SELECT * FROM sales_summary_bytime;

Триггеры AFTER также могут использовать таблицы переходов для проверки
всего набора строк, измененных оператором запуска. Команда CREATE
TRIGGER назначает имена одной или обеим таблицам переходов, а затем
функция может ссылаться на эти имена, как если бы они были временными
таблицами только для чтения (см. Пример 7).

Пример 7. Аудит с таблицами переходов

Этот пример дает те же результаты, что и Пример 4, но вместо
использования триггера, который срабатывает для каждой строки, он
использует триггер, который срабатывает один раз для каждого оператора,
после сбора соответствующей информации в таблице переходов. Это может
быть значительно быстрее, чем подход с триггером строки, когда оператор
вызова изменил много строк. Обратите внимание, что мы должны сделать
отдельное объявление триггера для каждого типа события, так как
предложения REFERENCING должны быть разными для каждого случая. Но это
не мешает нам использовать одну функцию триггера, если мы выберем. (На
практике может быть лучше использовать три отдельные функции и избегать
тестов во время выполнения на TG_OP).

CREATE TABLE emp (
    empname           text NOT NULL,
    salary            integer
);

CREATE TABLE emp_audit(
    operation         char(1)   NOT NULL,
    stamp             timestamp NOT NULL,
    userid            text      NOT NULL,
    empname           text      NOT NULL,
    salary integer
);

CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
    BEGIN
        --
        -- Create rows in emp_audit to reflect the operations performed on emp,
        -- making use of the special variable TG_OP to work out the operation.
        --
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO emp_audit
                SELECT 'D', now(), user, o.* FROM old_table o;
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO emp_audit
                SELECT 'U', now(), user, n.* FROM new_table n;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO emp_audit
                SELECT 'I', now(), user, n.* FROM new_table n;
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
$emp_audit$ LANGUAGE plpgsql;

CREATE TRIGGER emp_audit_ins
    AFTER INSERT ON emp
    REFERENCING NEW TABLE AS new_table
    FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();
CREATE TRIGGER emp_audit_upd
    AFTER UPDATE ON emp
    REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
    FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();
CREATE TRIGGER emp_audit_del
    AFTER DELETE ON emp
    REFERENCING OLD TABLE AS old_table
    FOR EACH STATEMENT EXECUTE FUNCTION process_emp_audit();

Триггеры на события

PL/pgSQL может использоваться для определения триггеров событий.
QHB требует, чтобы функция, вызываемая как триггер события, была
объявлена как функция без аргументов и возвращаемого типа event_trigger.

Когда функция PL/pgSQL вызывается как триггер события, в блоке
верхнего уровня автоматически создаются несколько специальных
переменных. Они есть:

TG_EVENT

• Тип данных text; строка, представляющая событие, для которого
  запускается триггер.

TG_TAG

• Тип данных text; переменная, содержащая тег команды, для которого
  срабатывает триггер.

Пример 8 показывает пример функции триггера событий в PL/pgSQL.

Пример 8. Функция запуска событий PL/pgSQL

Этот пример триггера просто вызывает сообщение NOTICE каждый раз, когда
выполняется поддерживаемая команда.

CREATE OR REPLACE FUNCTION snitch() RETURNS event_trigger AS $$
BEGIN
    RAISE NOTICE 'snitch: % %', tg_event, tg_tag;
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER snitch ON ddl_command_start EXECUTE FUNCTION snitch();

PL/pgSQL под капотом

В этом разделе обсуждаются некоторые детали реализации, которые часто
важно знать пользователям PL/pgSQL.

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

Операторы SQL и выражения в функции PL/pgSQL могут ссылаться на
переменные и параметры функции. За кулисами PL/pgSQL заменяет
параметры запроса для таких ссылок. Параметры будут заменены только в
тех местах, где синтаксически разрешена ссылка на параметр или столбец.
В качестве крайнего случая рассмотрим пример плохого стиля
программирования:

INSERT INTO foo (foo) VALUES (foo);

Первое вхождение foo должно быть синтаксически именем таблицы, поэтому
оно не будет подставлено, даже если функция имеет переменную с именем
foo. Второе вхождение должно быть именем столбца таблицы, поэтому оно
также не будет подставлено. Только третье вхождение является кандидатом
на ссылку на переменную функции.

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

INSERT INTO dest (col) SELECT foo + bar FROM src;

Здесь dest и src должны быть именами таблиц, а col должен быть столбцом
dest, но foo и bar могут быть либо переменными функции, либо столбцами
src.

По умолчанию PL/pgSQL сообщит об ошибке, если имя в выражении SQL
может ссылаться либо на переменную, либо на столбец таблицы. Вы можете
решить эту проблему, переименовав переменную или столбец, или
квалифицируя неоднозначную ссылку, или указав PL/pgSQL, какую
интерпретацию выбрать.

Самое простое решение — переименовать переменную или столбец. Общим
правилом кодирования является использование другого соглашения об именах
для переменных PL/pgSQL, чем для имен столбцов. Например, если вы
последовательно называете переменные функции v_something, в то
время как ни одно из имен столбцов не начинается с v_,
никаких конфликтов не будет.

В качестве альтернативы вы можете квалифицировать неоднозначные ссылки,
чтобы прояснить их. В приведенном выше примере src.foo будет однозначной
ссылкой на столбец таблицы. Чтобы создать однозначную ссылку на
переменную, объявите ее в помеченном блоке и используйте метку блока
(см. раздел Структура PL/pgSQL). Например,

<<block>>
DECLARE
    foo int;
BEGIN
    foo := ...;
    INSERT INTO dest (col) SELECT block.foo + bar FROM src;

Здесь block.foo означает переменную, даже если в src есть столбец foo.
Параметры функции, а также специальные переменные, такие как FOUND,
могут быть квалифицированы по имени функции, поскольку они неявно
объявляются во внешнем блоке, помеченном именем функции.

Иногда нецелесообразно исправлять все неоднозначные ссылки в большом
объеме кода PL/pgSQL. В таких случаях вы можете указать, что
PL/pgSQL должен разрешать неоднозначные ссылки как переменную или как столбец
таблицы (который совместим с некоторыми другими системами, такими как
Oracle),

Чтобы изменить это поведение в масштабе всей системы, установите для
параметра конфигурации plpgsql.variable_conflict один из параметров
error, use_variable или use_column (где error — заводская настройка
по умолчанию). Этот параметр влияет на последующие компиляции операторов
в функциях PL/pgSQL, но не на операторы, уже скомпилированные в
текущем сеансе. Поскольку изменение этого параметра может привести к
неожиданным изменениям в поведении функций PL/pgSQL, его может
изменить только суперпользователь.

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

#variable_conflict error
#variable_conflict use_variable
#variable_conflict use_column

Эти команды влияют только на функцию, в которой они написаны, и
переопределяют настройку plpgsql.variable_conflict. Примером является

CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $$
    #variable_conflict use_variable
    DECLARE
        curtime timestamp := now();
    BEGIN
        UPDATE users SET last_modified = curtime, comment = comment
          WHERE users.id = id;
    END;
$$ LANGUAGE plpgsql;

В команде UPDATE curtime, comment и id будут ссылаться на переменную
функции и параметры независимо от того, есть ли у users столбцы с этими
именами. Обратите внимание, что нам нужно было квалифицировать ссылку на
users.id в WHERE, чтобы она ссылалась на столбец таблицы. Но нам не
нужно было указывать ссылку на comment как цель в списке UPDATE, потому
что синтаксически это должен быть столбец users. Мы можем написать одну
и ту же функцию, не завися от настройки variable_conflict следующим
образом:

CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $$
    <<fn>>
    DECLARE
        curtime timestamp := now();
    BEGIN
        UPDATE users SET last_modified = fn.curtime, comment = stamp_user.comment
          WHERE users.id = stamp_user.id;
    END;
$$ LANGUAGE plpgsql;

Подстановка переменных не происходит в командной строке, данной EXECUTE
или в одном из ее вариантов. Если вам нужно вставить переменное значение
в такую команду, сделайте это как часть построения строкового значения
или используйте USING, как показано в разделе Выполнение динамических команд.

Подстановка переменных в настоящее время работает только в командах
SELECT, INSERT, UPDATE и DELETE, поскольку основной механизм SQL
допускает параметры запроса только в этих командах. Чтобы использовать
непостоянное имя или значение в других типах операторов (обычно
называемых служебными операторами), необходимо создать служебный
оператор в виде строки и EXECUTE.

Планирование кэширования

Интерпретатор PL/pgSQL анализирует исходный текст функции и создает
внутреннее двоичное дерево инструкций при первом вызове функции (в
каждом сеансе). Дерево команд полностью переводит структуру операторов
PL/pgSQL, но отдельные выражения SQL и команды SQL, используемые в
функции, не переводятся немедленно.

Поскольку каждое выражение и команда SQL сначала выполняются в функции,
интерпретатор PL/pgSQL анализирует и анализирует команду, чтобы
создать подготовленный оператор, используя функцию SPI_prepare
менеджера SPI. Последующие посещения этого выражения или команды
повторно используют подготовленное утверждение. Таким образом, функция с
условными путями кода, которые редко посещаются, никогда не будет нести
затраты на анализ тех команд, которые никогда не выполняются в текущем
сеансе. Недостатком является то, что ошибки в определенном выражении или
команде не могут быть обнаружены, пока эта часть функции не будет
достигнута во время выполнения. (Тривиальные синтаксические ошибки будут
обнаружены во время начального этапа анализа, но что-либо более глубокое
не будет обнаружено до выполнения).

PL/pgSQL (или, точнее, менеджер SPI) может, кроме того, попытаться
кэшировать план выполнения, связанный с любым конкретным подготовленным
оператором. Если кэшированный план не используется, то новый план
выполнения генерируется при каждом посещении оператора, и текущие
значения параметров (то есть значения переменных PL/pgSQL ) можно
использовать для оптимизации выбранного плана. Если инструкция не имеет
параметров или выполняется много раз, менеджер SPI рассмотрит
возможность создания общего плана, который не зависит от конкретных
значений параметров, и кэширования его для повторного использования.
Обычно это происходит, только если план выполнения не очень чувствителен
к значениям переменных PL/pgSQL, на которые есть ссылки. Если это так,
генерация плана каждый раз является чистой победой. См. PREPARE для
получения дополнительной информации о поведении подготовленных
операторов.

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

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

Если одна и та же функция используется в качестве триггера для
нескольких таблиц, PL/pgSQL подготавливает и кэширует операторы
независимо для каждой такой таблицы, то есть существует кэш для каждой
функции триггера и комбинации таблиц, а не только для каждой функции.
Это облегчает некоторые проблемы с различными типами данных; например,
триггерная функция сможет успешно работать со столбцом с именем key даже
если она имеет разные типы в разных таблицах.

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

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

CREATE FUNCTION logfunc1(logtxt text) RETURNS void AS $$
    BEGIN
        INSERT INTO logtable VALUES (logtxt, 'now');
    END;
$$ LANGUAGE plpgsql;

и:

CREATE FUNCTION logfunc2(logtxt text) RETURNS void AS $$
    DECLARE
        curtime timestamp;
    BEGIN
        curtime := 'now';
        INSERT INTO logtable VALUES (logtxt, curtime);
    END;
$$ LANGUAGE plpgsql;

В случае logfunc1 главный анализатор QHB знает, анализируя INSERT
что строка ’now’ должна интерпретироваться как timestamp, потому что
целевой столбец logtable относится к этому типу. Таким образом, ’now’
будет преобразовано в постоянную timestamp при анализе INSERT, а затем
будет использоваться во всех logfunc1 в течение всего времени сеанса.
Излишне говорить, что это не то, что хотел программист. Лучшая идея —
использовать функцию now() или current_timestamp.

В случае logfunc2 основной анализатор QHB не знает, каким должен
быть тип ’now’ и поэтому возвращает значение данных типа text содержащее
строку now. Во время последующего присвоения локальной переменной
curtime интерпретатор PL/pgSQL преобразует эту строку в тип timestamp
, вызывая функции text_out и timestamp_in для преобразования. Таким
образом, вычисленная метка времени обновляется при каждом выполнении,
как того ожидает программист. Несмотря на то, что это работает должным
образом, это не очень эффективно, поэтому использование функции now()
все равно было бы лучшей идеей.

Советы по разработке на PL/pgSQL

Хороший способ разработки в PL/pgSQL — использовать выбранный вами
текстовый редактор для создания своих функций, а в другом окне
использовать psql для загрузки и тестирования этих функций. Если вы
делаете это таким образом, хорошей идеей будет написать функцию,
используя CREATE OR REPLACE FUNCTION. Таким образом, вы можете просто
перезагрузить файл, чтобы обновить определение функции. Например:

CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $$
          ....
$$ LANGUAGE plpgsql;

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

i filename.sql

а затем немедленно выполните команды SQL для проверки функции.

Еще один хороший способ разработки на PL/pgSQL — это инструмент для
доступа к базе данных с графическим интерфейсом, который облегчает
разработку на процедурном языке. Одним из примеров такого инструмента
является pgAdmin, хотя существуют и другие. Эти инструменты часто
предоставляют удобные функции, такие как экранирование одинарных кавычек
и упрощение воссоздания и отладки функций.

Обработка кавычек

Код функции PL/pgSQL указывается в CREATE FUNCTION как строковый
литерал. Если вы пишете строковый литерал обычным способом с окружающими
одинарными кавычками, то любые одинарные кавычки внутри тела функции
должны быть удвоены; аналогично, любые обратные слеши должны быть
удвоены (при условии использования синтаксиса escape-строки). Удвоение
кавычек в лучшем случае утомительно, а в более сложных случаях код может
стать совершенно непонятным, потому что вы можете легко найти себе
полдюжины или более соседних кавычек. Вместо этого рекомендуется
написать тело функции в виде строкового литерала, заключенного в
«кавычки» (см. раздел Строковые константы с экранированием знаками доллара). При способе с использованием
знаков доллара вы никогда не удваиваете любые кавычки, а вместо этого
стараетесь выбирать разные разделители долларовых кавычек для каждого
необходимого уровня вложенности. Например, можно написать команду
CREATE FUNCTION как:

CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $PROC$
          ....
$PROC$ LANGUAGE plpgsql;

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

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

одинарные кавычки

• Чтобы начать и закончить тело функции, например:

CREATE FUNCTION foo() RETURNS integer AS '
          ....
' LANGUAGE plpgsql;

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

парные кавычки

• Для строковых литералов внутри тела функции, например:

a_output := ''Blah'';
SELECT * FROM users WHERE f_name=''foobar'';

При подходе «экранирование знаками доллара» вы просто пишете:

a_output := 'Blah';
SELECT * FROM users WHERE f_name='foobar';

это именно то, что анализатор PL/pgSQL будет видеть в любом
случае.

4 кавычки

• Когда вам нужна одиночная кавычка в строковой константе внутри тела
  функции, например:

a_output := a_output || '' AND name LIKE ''''foobar'''' AND xyz''

Значение, добавленное к a_output, будет: AND name LIKE ’foobar’
AND xyz.

В подходе «экранирование знаками доллара» вы написали бы:

a_output := a_output || $$ AND name LIKE 'foobar' AND xyz$$

будьте осторожны, чтобы любые разделители долларовых кавычек вокруг
этого не были просто $$.

6 кавычек

• Когда одна кавычка в строке внутри тела функции находится рядом с
  концом этой строковой константы, например:

a_output := a_output || '' AND name LIKE ''''foobar''''''

Значение, добавленное к a_output будет: AND name LIKE ’foobar’.

В подходе «экранирование знаками доллара» это становится:

a_output := a_output || $$ AND name LIKE 'foobar'$$

10 кавычек

• Когда вам нужно две одинарные кавычки в строковой константе (на
  которую приходится 8 кавычек), и это рядом с концом этой строковой
  константы (еще 2). Это, вероятно, понадобится вам, только если вы
  пишете функцию, которая генерирует другие функции, как в
  примере 10. Например:

a_output := a_output || '' if v_'' ||
    referrer_keys.kind || '' like ''''''''''
    || referrer_keys.key_string || ''''''''''
    then return ''''''  || referrer_keys.referrer_type
    || ''''''; end if;'';

Значение a_output тогда будет:

if v_... like ''...'' then return ''...''; end if;

В подходе «экранирование знаками доллара» это становится:

a_output := a_output || $$ if v_$$ || referrer_keys.kind || $$ like '$$
    || referrer_keys.key_string || $$'
    then return '$$  || referrer_keys.referrer_type
    || $$'; end if;$$;

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

Дополнительные проверки во время компиляции и во время выполнения

Чтобы помочь пользователю найти примеры простых, но распространенных
проблем до того, как они причинят вред, PL/pgSQL предоставляет
дополнительные checks. Когда они включены, в зависимости от
конфигурации они могут использоваться для выдачи WARNING или ERROR во
время компиляции функции. Функция, получившая WARNING может быть
выполнена без выдачи дополнительных сообщений, поэтому рекомендуется
выполнять тестирование в отдельной среде разработки.

При необходимости установка plpgsql.extra_warnings или
plpgsql.extra_errors в значение «all» рекомендуется в средах разработки
и/или тестирования.

Эти дополнительные проверки включены через переменные конфигурации
plpgsql.extra_warnings для предупреждений и plpgsql.extra_errors для
ошибок. Оба могут быть установлены в список проверок, разделенных
запятыми, «none» или «all». По умолчанию установлено значение «none».
В настоящее время список доступных проверок включает в себя:

shadowed_variables

• Проверяет, скрывает ли объявление ранее определенную переменную.

strict_multi_assignment

• Некоторые команды PL/pgSQL позволяют присваивать значения более
  чем одной переменной одновременно, например, SELECT INTO. Как
  правило, число целевых переменных и количество исходных переменных
  должны совпадать, хотя PL/pgSQL будет использовать NULL для
  пропущенных значений, а дополнительные переменные игнорируются.
  Включение этой проверки приведет к тому, что PL/pgSQL будет
  выдавать WARNING или ERROR всякий раз, когда число целевых
  переменных и количество исходных переменных различаются.

too_many_rows

• Включение этой проверки заставит PL/pgSQL проверять, возвращает ли
  данный запрос более одной строки, когда используется предложение
  INTO. Поскольку оператор INTO будет когда-либо использовать только
  одну строку, запрос, возвращающий несколько строк, как правило, либо
  неэффективен, либо недетерминирован, и, следовательно, вероятно,
  является ошибкой.

В следующем примере показано влияние plpgsql.extra_warnings
установленного на shadowed_variables:

SET plpgsql.extra_warnings TO 'shadowed_variables';

CREATE FUNCTION foo(f1 int) RETURNS int AS $$
DECLARE
f1 int;
BEGIN
RETURN f1;
END
$$ LANGUAGE plpgsql;
WARNING:  variable "f1" shadows a previously defined variable
LINE 3: f1 int;
        ^
CREATE FUNCTION

В приведенном ниже примере показаны эффекты установки
plpgsql.extra_warnings в значение strict_multi_assignment:

SET plpgsql.extra_warnings TO 'strict_multi_assignment';

CREATE OR REPLACE FUNCTION public.foo()
 RETURNS void
 LANGUAGE plpgsql
AS $$
DECLARE
  x int;
  y int;
BEGIN
  SELECT 1 INTO x, y;
  SELECT 1, 2 INTO x, y;
  SELECT 1, 2, 3 INTO x, y;
END;
$$;

SELECT foo();
WARNING:  number of source and target fields in assignment does not match
DETAIL:  strict_multi_assignment check of extra_warnings is active.
HINT:  Make sure the query returns the exact list of columns.
WARNING:  number of source and target fields in assignment does not match
DETAIL:  strict_multi_assignment check of extra_warnings is active.
HINT:  Make sure the query returns the exact list of columns.

 foo
-----

(1 row)

Портирование из Oracle PL/SQL

В этом разделе объясняются различия между языком PL/pgSQL QHB и
языком PL/SQL Oracle, чтобы помочь разработчикам, которые переносят
приложения из Oracle® в QHB.

PL/pgSQL во многом похож на PL/SQL. Это блочно-структурированный
императивный язык, и все переменные должны быть объявлены. Присвоения,
циклы и условия аналогичны. Основные различия, которые следует учитывать
при переносе с PL/SQL на PL/pgSQL:

• Если имя, используемое в команде SQL, может быть либо именем столбца
  таблицы, либо ссылкой на переменную функции, PL/SQL обрабатывает
  его как имя столбца. Это соответствует поведению PL/pgSQL
  plpgsql.variable_conflict = use_column, которое не является
  значением по умолчанию, как описано в разделе Подстановка переменных. Во-первых,
  во-первых, часто лучше избегать таких неоднозначностей, но если вам
  нужно портировать большой объем кода, который зависит от этого
  поведения, настройка variable_conflict может быть лучшим решением.

• В QHB тело функции должно быть записано как строковый
  литерал. Поэтому вам нужно использовать знаки доллара или
  экранировать одинарные кавычки в теле функции.
  (См. раздел Обработка кавычек).

• Имена типов данных часто нуждаются в переводе. Например, в Oracle
  строковые значения обычно объявляются как имеющие тип varchar2,
  который является нестандартным типом SQL. В QHB вместо этого
  используйте тип varchar или text. Аналогично, замените тип number на
  numeric или используйте другой тип числовых данных, если есть более
  подходящий.

• Вместо пакетов используйте схемы для организации ваших функций в
  группы.

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

• Целочисленные циклы FOR с REVERSE работают по-разному: PL/SQL
  ведет обратный отсчет от второго числа к первому, а PL/pgSQL ведет
  обратный отсчет от первого числа ко второму, что требует замены
  границ цикла при переносе. Эта несовместимость вызывает сожаление,
  но вряд ли изменится. (См. раздел FOR (целочисленный вариант)).

• Циклы FOR запросов (кроме курсоров) также работают по-другому:
  целевая переменная (и) должна быть объявлена, тогда как PL/SQL
  всегда объявляет их неявно. Преимущество этого состоит в том, что
  значения переменных все еще доступны после выхода из цикла.

• Существуют различные нотационные различия для использования
  переменных курсора.

Примеры портирования

В примере 9 показано, как перенести простую функцию из PL/SQL в PL/pgSQL.

Пример 9. Портирование простой функции из PL/SQL в PL/pgSQL

Вот функция Oracle PL/SQL :

CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar2,
                                                  v_version varchar2)
RETURN varchar2 IS
BEGIN
    IF v_version IS NULL THEN
        RETURN v_name;
    END IF;
    RETURN v_name || '/' || v_version;
END;
/
show errors;

Давайте рассмотрим эту функцию и посмотрим на различия по сравнению с
PL/pgSQL :

• Имя типа varchar2 должно быть изменено на varchar или text. В
  примерах в этом разделе мы будем использовать varchar, но text
  часто является лучшим выбором, если вам не нужны определенные
  ограничения длины строки.

• Ключевое слово RETURN в прототипе функции (а не в теле функции)
  становится RETURNS в QHB. Кроме того, IS становится AS, и
  вам нужно добавить предложение LANGUAGE, поскольку PL/pgSQL не
  является единственным возможным языком функций.

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

• Команда show errors не существует в QHB и не нужна, поскольку
  ошибки сообщаются автоматически.

Вот как будет выглядеть эта функция при портировании на QHB:

CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar,
                                                  v_version varchar)
RETURNS varchar AS $$
BEGIN
    IF v_version IS NULL THEN
        RETURN v_name;
    END IF;
    RETURN v_name || '/' || v_version;
END;
$$ LANGUAGE plpgsql;

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

Пример 10. Портирование функции, которая создает другую функцию из
PL/SQL в PL/pgSQL

Следующая процедура извлекает строки из SELECT и строит большую функцию
с результатами в операторах IF для эффективности.

Это версия Oracle:

CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc IS
    CURSOR referrer_keys IS
        SELECT * FROM cs_referrer_keys
        ORDER BY try_order;
    func_cmd VARCHAR(4000);
BEGIN
    func_cmd := 'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host IN VARCHAR2,
                 v_domain IN VARCHAR2, v_url IN VARCHAR2) RETURN VARCHAR2 IS BEGIN';

    FOR referrer_key IN referrer_keys LOOP
        func_cmd := func_cmd ||
          ' IF v_' || referrer_key.kind
          || ' LIKE ''' || referrer_key.key_string
          || ''' THEN RETURN ''' || referrer_key.referrer_type
          || '''; END IF;';
    END LOOP;

    func_cmd := func_cmd || ' RETURN NULL; END;';

    EXECUTE IMMEDIATE func_cmd;
END;
/
show errors;

Вот как эта функция может оказаться в QHB:

CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc() AS $func$
DECLARE
    referrer_keys CURSOR IS
        SELECT * FROM cs_referrer_keys
        ORDER BY try_order;
    func_body text;
    func_cmd text;
BEGIN
    func_body := 'BEGIN';

    FOR referrer_key IN referrer_keys LOOP
        func_body := func_body ||
          ' IF v_' || referrer_key.kind
          || ' LIKE ' || quote_literal(referrer_key.key_string)
          || ' THEN RETURN ' || quote_literal(referrer_key.referrer_type)
          || '; END IF;' ;
    END LOOP;

    func_body := func_body || ' RETURN NULL; END;';

    func_cmd :=
      'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host varchar,
                                                        v_domain varchar,
                                                        v_url varchar)
        RETURNS varchar AS '
      || quote_literal(func_body)
      || ' LANGUAGE plpgsql;' ;

    EXECUTE func_cmd;
END;
$func$ LANGUAGE plpgsql;

Обратите внимание, что тело функции quote_literal отдельно и передается
через quote_literal чтобы удвоить любые кавычки в нем. Этот метод
необходим, потому что мы не можем безопасно использовать долларовые
кавычки для определения новой функции: мы не знаем наверняка, какие
строки будут интерполированы из поля referrer_key.key_string. (Здесь
мы предполагаем, что referrer_key.kind всегда можно доверять, чтобы он
всегда был host, domain или url, но referrer_key.key_string может
быть чем угодно, в частности он может содержать знаки доллара). Эта
функция на самом деле является улучшением оригинала Oracle, потому что
он не будет генерировать неработающий код, когда
referrer_key.key_string или referrer_key.referrer_type содержат
кавычки.

В примере 11 показано, как перенести функцию с параметрами OUT и
обработкой строк. QHB не имеет встроенной функции instr, но вы
можете создать ее, используя комбинацию других функций. В разделе Приложение
описана реализация instr в PL/pgSQL, которую вы можете
использовать для упрощения переноса.

Пример 11. Перенос процедуры с параметрами String Manipulation и OUT
из PL/SQL в PL/pgSQL

Следующая процедура Oracle PL/SQL используется для анализа URL-адреса
и возврата нескольких элементов (хост, путь и запрос).

Это версия Oracle:

CREATE OR REPLACE PROCEDURE cs_parse_url(
    v_url IN VARCHAR2,
    v_host OUT VARCHAR2,  -- This will be passed back
    v_path OUT VARCHAR2,  -- This one too
    v_query OUT VARCHAR2) -- And this one
IS
    a_pos1 INTEGER;
    a_pos2 INTEGER;
BEGIN
    v_host := NULL;
    v_path := NULL;
    v_query := NULL;
    a_pos1 := instr(v_url, '//');

    IF a_pos1 = 0 THEN
        RETURN;
    END IF;
    a_pos2 := instr(v_url, '/', a_pos1 + 2);
    IF a_pos2 = 0 THEN
        v_host := substr(v_url, a_pos1 + 2);
        v_path := '/';
        RETURN;
    END IF;

    v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2);
    a_pos1 := instr(v_url, '?', a_pos2 + 1);

    IF a_pos1 = 0 THEN
        v_path := substr(v_url, a_pos2);
        RETURN;
    END IF;

    v_path := substr(v_url, a_pos2, a_pos1 - a_pos2);
    v_query := substr(v_url, a_pos1 + 1);
END;
/
show errors;

Вот возможный перевод на PL/pgSQL :

CREATE OR REPLACE FUNCTION cs_parse_url(
    v_url IN VARCHAR,
    v_host OUT VARCHAR,  -- This will be passed back
    v_path OUT VARCHAR,  -- This one too
    v_query OUT VARCHAR) -- And this one
AS $$
DECLARE
    a_pos1 INTEGER;
    a_pos2 INTEGER;
BEGIN
    v_host := NULL;
    v_path := NULL;
    v_query := NULL;
    a_pos1 := instr(v_url, '//');

    IF a_pos1 = 0 THEN
        RETURN;
    END IF;
    a_pos2 := instr(v_url, '/', a_pos1 + 2);
    IF a_pos2 = 0 THEN
        v_host := substr(v_url, a_pos1 + 2);
        v_path := '/';
        RETURN;
    END IF;

    v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2);
    a_pos1 := instr(v_url, '?', a_pos2 + 1);

    IF a_pos1 = 0 THEN
        v_path := substr(v_url, a_pos2);
        RETURN;
    END IF;

    v_path := substr(v_url, a_pos2, a_pos1 - a_pos2);
    v_query := substr(v_url, a_pos1 + 1);
END;
$$ LANGUAGE plpgsql;

Эту функцию можно использовать так:

SELECT * FROM cs_parse_url('http://foobar.com/query.cgi?baz');

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

Пример 12. Портирование процедуры из PL/SQL в PL/pgSQL

Версия Oracle:

CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id IN INTEGER) IS
    a_running_job_count INTEGER;
BEGIN
    LOCK TABLE cs_jobs IN EXCLUSIVE MODE;

    SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL;

    IF a_running_job_count > 0 THEN
        COMMIT; -- free lock
        raise_application_error(-20000,
                 'Unable to create a new job: a job is currently running.');
    END IF;

    DELETE FROM cs_active_job;
    INSERT INTO cs_active_job(job_id) VALUES (v_job_id);

    BEGIN
        INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, now());
    EXCEPTION
        WHEN dup_val_on_index THEN NULL; -- don't worry if it already exists
    END;
    COMMIT;
END;
/
show errors

Вот как мы могли бы перенести эту процедуру в PL/pgSQL :

CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id integer) AS $$
DECLARE
    a_running_job_count integer;
BEGIN
    LOCK TABLE cs_jobs IN EXCLUSIVE MODE;

    SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL;

    IF a_running_job_count > 0 THEN
        COMMIT; -- free lock
        RAISE EXCEPTION 'Unable to create a new job: a job is currently running'; -- [^1]
    END IF;

    DELETE FROM cs_active_job;
    INSERT INTO cs_active_job(job_id) VALUES (v_job_id);

    BEGIN
        INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, now());
    EXCEPTION
        WHEN unique_violation THEN -- [^2]
            -- don't worry if it already exists
    END;
    COMMIT;
END;
$$ LANGUAGE plpgsql;

Другие вещи для наблюдения

В этом разделе объясняются некоторые другие вещи, на которые нужно
обратить внимание при портировании функций Oracle PL/SQL на QHB.

Неявный откат после исключений

В PL/pgSQL, когда исключение отлавливается предложением EXCEPTION,
все изменения в базе данных, так как блок BEGIN автоматически
откатывается. То есть поведение эквивалентно тому, что вы получите в
Oracle:

BEGIN
    SAVEPOINT s1;
    ... code here ...
EXCEPTION
    WHEN ... THEN
        ROLLBACK TO s1;
        ... code here ...
    WHEN ... THEN
        ROLLBACK TO s1;
        ... code here ...
END;

Если вы переводите процедуру Oracle, которая использует SAVEPOINT и
ROLLBACK TO в этом стиле, ваша задача проста: просто опустите SAVEPOINT
и ROLLBACK TO. Если у вас есть процедура, в которой SAVEPOINT и
ROLLBACK TO используются по-разному, вам понадобится определенная мысль.

EXECUTE

Версия EXECUTE для PL/pgSQL работает аналогично версии PL/SQL, но
вы должны помнить, что используйте quote_literal и quote_ident как
описано в разделе Выполнение динамических команд. Конструкции типа EXECUTE 'SELECT * FROM $1';
не будет надежно работать, если вы не используете эти функции.

Оптимизация функций PL/pgSQL

QHB предоставляет вам два модификатора создания функции для
оптимизации выполнения: « изменчивость » (всегда ли функция возвращает
один и тот же результат, если даны одни и те же аргументы) и «строгость»
(возвращает ли функция ноль, если какой-либо аргумент равен нулю).
Обратитесь к справочной странице CREATE FUNCTION за подробностями.

При использовании этих атрибутов оптимизации ваш оператор CREATE
FUNCTION может выглядеть примерно так:

CREATE FUNCTION foo(...) RETURNS integer AS $$
...
$$ LANGUAGE plpgsql STRICT IMMUTABLE;

Приложение

Этот раздел содержит код для набора Oracle-совместимых функций instr
которые вы можете использовать для упрощения процесса переноса.

-
-- instr functions that mimic Oracle's counterpart
-- Syntax: instr(string1, string2 [, n [, m]])
-- where [] denotes optional parameters.
--
-- Search string1, beginning at the nth character, for the mth occurrence
-- of string2.  If n is negative, search backwards, starting at the abs(n)'th
-- character from the end of string1.
-- If n is not passed, assume 1 (search starts at first character).
-- If m is not passed, assume 1 (find first occurrence).
-- Returns starting index of string2 in string1, or 0 if string2 is not found.
--

CREATE FUNCTION instr(varchar, varchar) RETURNS integer AS $$
BEGIN
    RETURN instr($1, $2, 1);
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;


CREATE FUNCTION instr(string varchar, string_to_search_for varchar,
                      beg_index integer)
RETURNS integer AS $$
DECLARE
    pos integer NOT NULL DEFAULT 0;
    temp_str varchar;
    beg integer;
    length integer;
    ss_length integer;
BEGIN
    IF beg_index > 0 THEN
        temp_str := substring(string FROM beg_index);
        pos := position(string_to_search_for IN temp_str);

        IF pos = 0 THEN
            RETURN 0;
        ELSE
            RETURN pos + beg_index - 1;
        END IF;
    ELSIF beg_index < 0 THEN
        ss_length := char_length(string_to_search_for);
        length := char_length(string);
        beg := length + 1 + beg_index;

        WHILE beg > 0 LOOP
            temp_str := substring(string FROM beg FOR ss_length);
            IF string_to_search_for = temp_str THEN
                RETURN beg;
            END IF;

            beg := beg - 1;
        END LOOP;

        RETURN 0;
    ELSE
        RETURN 0;
    END IF;
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;


CREATE FUNCTION instr(string varchar, string_to_search_for varchar,
                      beg_index integer, occur_index integer)
RETURNS integer AS $$
DECLARE
    pos integer NOT NULL DEFAULT 0;
    occur_number integer NOT NULL DEFAULT 0;
    temp_str varchar;
    beg integer;
    i integer;
    length integer;
    ss_length integer;
BEGIN
    IF occur_index <= 0 THEN
        RAISE 'argument ''%'' is out of range', occur_index
          USING ERRCODE = '22003';
    END IF;

    IF beg_index > 0 THEN
        beg := beg_index - 1;
        FOR i IN 1..occur_index LOOP
            temp_str := substring(string FROM beg + 1);
            pos := position(string_to_search_for IN temp_str);
            IF pos = 0 THEN
                RETURN 0;
            END IF;
            beg := beg + pos;
        END LOOP;

        RETURN beg;
    ELSIF beg_index < 0 THEN
        ss_length := char_length(string_to_search_for);
        length := char_length(string);
        beg := length + 1 + beg_index;

        WHILE beg > 0 LOOP
            temp_str := substring(string FROM beg FOR ss_length);
            IF string_to_search_for = temp_str THEN
                occur_number := occur_number + 1;
                IF occur_number = occur_index THEN
                    RETURN beg;
                END IF;
            END IF;

            beg := beg - 1;
        END LOOP;

        RETURN 0;
    ELSE
        RETURN 0;
    END IF;
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;