Функции в PostgreSQL

Объявления переменных в функциях

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

Переменные в PL / Postgre SQL могут иметь любой тип данных SQL, в частности такие как целое число VARCHAR и CHAR.

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

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

Синтаксис объявления переменных выглядит следующим образом:

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

Условие по умолчанию, если оно и есть, определяет начальное значение присвоенной переменной, когда блок будет введен. Если условие по умолчанию не задано, то переменная инициализируется значением SQL NULL. Выбор константы предотвращает изменение значения после инициализации, таким образом, что его значение будет оставаться постоянным в течение всего срока действия блока. Возможность сортировки определяет параметры, используемые для переменной. Если указано условие NOT NULL, присваивание пустого значения приведёт к ошибке при выполнении. Все переменные, объявленные как NOT NULL должен иметь по умолчанию непустое значение. Знак «равно» (=) может быть использован вместо совместимого с PL / SQL знака «: =».

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

Примёр:

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

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

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

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

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

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

user_id integer;
name ALIAS FOR $n;

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

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

Примечание: Два данных примера не являются полностью похожими. В первом случае, «Промежуточный итог» может именоваться как sales_tax.subtotal, но во втором случае такого не будет. (Если бы мы дали название на внутреннем блоке, то промежуточные итоги могут ассоциироваться с этим названием.)

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

user_id integer;
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; он должен быть назначен во время выполнения функции. Окончательное значение этого параметра является его возврат. Например, пример расчёт налога на продажи может быть сделан следующим образом:

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

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

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

user_id integer;
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;

Такой приём эффективно создает анонимный тип записи результатов функции. Если дан RETURNS, он должен дать эту команду для записи.

Еще один способ объявить функцию в PL / PgSQL - возврат таблицы, например:

user_id integer;
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;

Именно он эквивалентен объявлению одного или более выходных параметров и некоторых типов определения RETURNS SETOF.

Когда возвращаемый тип функции в PL / PgSQL объявлен как полиморфный (любой элемент, любой массив, любой не массив, любой номер или классификация), создается специальный параметр $0. Его тип данных это фактически возвратный тип функции, выводящийся из фактических любых типов входных. Это позволяет функции получить доступ к его возврату. $0 инициализируется в ноль, и может быть изменен с помощью функции, так что он может быть использован для хранения возвращаемого значения, если это нужно, хотя и не обязательно. $0 также может быть предоставлена в виде псевдонима. Например, эта функция работает на любом типе данных, который имеет оператор «+:»

user_id integer;
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 не используется; Выходные параметры будут служить той же цели. Например:

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

Псевдоним

user_id integer;
newname ALIAS FOR oldname;

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

Пример:

user_id integer;
DECLARE
  prior ALIAS FOR old;
  updated ALIAS FOR new;

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

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

user_id integer;
variable%TYPE

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

user_id integer;
user_id users.user_id%TYPE;

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

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

Типы строк

user_id integer;
name table_name%ROWTYPE;
name composite_type_name;

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

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

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

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

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

user_id integer;
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 ... ;

Типы записей

user_id integer;
name RECORD;

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

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

Сопоставление переменных в PL / PgSQL

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

user_id integer;
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 для сравнения, в то время как второе использование будет использовать сортировки «С».

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

user_id integer;
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;

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

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

user_id integer;
DECLARE
    local_a text COLLATE "en_US";

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

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

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

Добавить комментарий

Ваш e-mail не будет опубликован.