Как написать функцию на PL/pgSQL?
В прошлых уроках мы изучали язык SQL, в основном это было связанно с запросами на выборку, теперь пришло время переходить к программированию в базах данных, поэтому сегодня мы с Вами будем учиться создавать функции на языке PL/pgSQL в СУБД PostgreSQL.
И начинаем как обычно с теории.
Что такое функции в SQL?
Функции – это объекты базы данных, которые используются для автоматизации и упрощения расчетов. Функции пишутся уже с использованием настоящего языка программирования в PostgreSQL — это PL/pgSQL.
PL/pgSQL – язык программирования, который используется в СУБД PostgreSQL для написания функций, триггеров и других управляющих конструкций.
Сразу скажу, что писать функции без базовых знаний SQL практически невозможно, поэтому советую ознакомиться с основами SQL, например, Вам помогут вот эти статьи:
Теперь давайте поговорим о том, для чего нам нужны эти самые функции. Как и в любом языке программирования есть «встроенные» функции, а также есть возможность писать свои «пользовательские» функции и PL/pgSQL не исключение, если быть точнее, то SQL не исключение, так как PL/pgSQL является расширением языка SQL.
В функциях мы можем легко прописывать условия, как и в других языках программирования, в функцию можно передавать параметры и соответственно она может нам возвращать результат, после выполнения действий с входящими параметрами. Одной из главных особенностей функций является то, что при обращении к базе данных из приложения все действия происходят на сервере, что гораздо быстрей, т.е. эффективней. Если не использовать вызов функций из приложения, то обращаться к базе данных придется несколько раз, что в свою очередь замедляет работу.
Также, например, можно написать функции, которые будут просто упрощать написание запросов на SQL. Другими словами, можно прописать в функции какое-нибудь действие, которое Вы часто используете при написании запросов и просто вызывать ее в самом запросе. Тем самым запрос становится намного короче, проще и быстрей выполняется, а также к запросу добавляется динамика, так как функции могут быть динамические, т.е. возвращать разный результат в зависимости от входящего параметра или изменений в базе.
В функциях в СУБД PostgreSQL можно использовать все операторы SQL такие как: INSERT, DELETE, UPDATE и другие. И Вы теперь представьте, что можно прописать в функции!? а потом просто вызвать ее одной строкой.
Создание функций на PL/pgSQL
Теперь давайте будем учиться писать эти самые функции. И для начала ниже представлен общий синтаксис написания функции в PL/pgSQL:
Тип возвращаемого значения может быть разный, например, numeric, integer, text или, например void это тип, который не возвращает значение, а функция просто отрабатывает (например, добавляет новые строки).
Сейчас давайте напишем простенькую функцию, пока без использования plpgsql. Допустим, у нас есть две таблицы с одинаковой строуктурой: test и test1:
Test1
и Test2
| id | number | name2 | tarif |
| 1 | 111 | mike1 | 2 |
| 2 | 222 | peter1 | 3 |
Например, мы часто используем в запросах объединение этих двух таблиц, для того чтобы подтянуть к таблице test1, столбец number из таблицы test2. Для упрощения всего этого давайте напишем простенькую функцию:
Теперь давайте вызовем нашу функцию в запросе, это проще простого:
где, «work».test_number(id) – это и есть вызов нашей функции. Мы передали ему параметр id, т.е. целую колонку, результат будет таким:
| id | name | flag | znach | number |
| 1 | mike | 1 | 10 | 111 |
| 2 | peter | 0 | 15 | 222 |
Если нужно кого-то конкретного подтянуть, можно написать вот так, т.е. передать параметр 1 (id=1).
Таким образом, у нас выведется одна строка:
| id | name | flag | znach | number |
| 1 | mike | 1 | 10 | 111 |
Здесь мы с Вами обошлись без всякого рода объединений, согласитесь так намного проще, запрос короче и выполняется быстрей.
Теперь попробуем написать функцию уже с использованием языка PL/pgSQL. Допустим, мы хотим знать, кто в нашей таблице соответствует тому или иному признаку. Например, кто из наших сотрудников работает. Определять будем по признаку flag (1 – работает; 0 – не работает). При этом нам не удобно вспоминать чему соответствует этот признак, например 1, поэтому давайте напишем функцию, чтобы видеть надпись, напротив того или иного сотрудника. Пишем функцию:
Здесь мы уже используем объявление переменных, условие и возвращение значения.
| id | name | flag | znach | status |
| 1 | mike | 1 | 10 | Он работает |
| 2 | peter | 0 | 15 | Не работает |
Как Вы уже, наверное, поняли, что возможности функций практически не ограничены, на языке PL/pgSQL можно написать очень серьезные функции, с трудными расчетами и так далее. В данной заметке мы с Вами рассмотрели только основы написания, а дальше зависит только о Вас. Удачи!
Определяемые пользователем функции
В языках программирования обычно имеется два типа подпрограмм:
определяемые пользователем функции (UDF).
Создание и выполнение определяемых пользователем функций
Определяемые пользователем функции создаются посредством инструкции CREATE FUNCTION, которая имеет следующий синтаксис:
Предложение RETURNS определяет тип данных значения, возвращаемого UDF. Это может быть почти любой стандартный тип данных, поддерживаемый системой баз данных, включая тип данных TABLE. Единственным типом данных, который нельзя указывать, является тип данных timestamp.
Определяемые пользователем функции могут быть либо скалярными, либо табличными. Скалярные функции возвращают атомарное (скалярное) значение. Это означает, что в предложении RETURNS скалярной функции указывается один из стандартных типов данных. Функция является табличной, если предложение RETURNS возвращает набор строк.
Параметр WITH ENCRYPTION в системном каталоге кодирует информацию, содержащую текст инструкции CREATE FUNCTION. Таким образом, предотвращается несанкционированный просмотр текста, который был использован для создания функции. Данная опция позволяет повысить безопасность системы баз данных.
Альтернативное предложение WITH SCHEMABINDING привязывает UDF к объектам базы данных, к которым эта функция обращается. После этого любая попытка модифицировать объект базы данных, к которому обращается функция, претерпевает неудачу. (Привязка функции к объектам базы данных, к которым она обращается, удаляется только при изменении функции, после чего параметр SCHEMABINDING больше не задан.)
Для того чтобы во время создания функции использовать предложение SCHEMABINDING, объекты базы данных, к которым обращается функция, должны удовлетворять следующим условиям:
все представления и другие UDF, к которым обращается определяемая функция, должны быть привязаны к схеме;
все объекты базы данных (таблицы, представления и UDF) должны быть в той же самой базе данных, что и определяемая функция.
Параметр block определяет блок BEGIN/END, содержащий реализацию функции. Последней инструкцией блока должна быть инструкция RETURN с аргументом. (Значением аргумента является возвращаемое функцией значение.) Внутри блока BEGIN/END разрешаются только следующие инструкции:
инструкции присвоения, такие как SET;
инструкции для управления ходом выполнения, такие как WHILE и IF;
инструкции DECLARE, объявляющие локальные переменные;
инструкции SELECT, содержащие списки столбцов выборки с выражениями, значения которых присваиваются переменным, являющимися локальными для данной функции;
инструкции INSERT, UPDATE и DELETE, которые изменяют переменные с типом данных TABLE, являющиеся локальными для данной функции.
По умолчанию инструкцию CREATE FUNCTION могут использовать только члены предопределенной роли сервера sysadmin и предопределенной роли базы данных db_owner или db_ddladmin. Но члены этих ролей могут присвоить это право другим пользователям с помощью инструкции GRANT CREATE FUNCTION.
В примере ниже показано создание функции ComputeCosts:
Функция ComputeCosts вычисляет дополнительные расходы, возникающие при увеличении бюджетов проектов. Единственный входной параметр, @percent, определяет процентное значение увеличения бюджетов. В блоке BEGIN/END сначала объявляются две локальные переменные: @addCosts и @sumBudget, а затем с помощью инструкции SELECT переменной @sumBudget присваивается общая сумма всех бюджетов. После этого функция вычисляет общие дополнительные расходы и посредством инструкции RETURN возвращает это значение.
Вызов определяемой пользователем функции
В примере ниже показан вызов функции ComputeCosts в инструкции SELECT:
Инструкция SELECT в примере отображает названия и номера всех проектов, бюджеты которых меньше, чем общие дополнительные расходы по всем проектам при заданном значении процентного увеличения.
В инструкциях Transact-SQL имена функций необходимо задавать, используя имена, состоящие из двух частей: schema name и function name, поэтому в примере мы использовали префикс схемы dbo.
Возвращающие табличное значение функции
Как уже упоминалось ранее, функция является возвращающей табличное значение, если ее предложение RETURNS возвращает набор строк. В зависимости от того, каким образом определено тело функции, возвращающие табличное значение функции классифицируются как встраиваемые (inline) и многоинструкционные (multistatement). Если в предложении RETURNS ключевое слово TABLE указывается без сопровождающего списка столбцов, такая функция является встроенной. Инструкция SELECT встраиваемой функции возвращает результирующий набор в виде переменной с типом данных TABLE.
Многоинструкционная возвращающая табличное значение функция содержит имя, определяющее внутреннюю переменную с типом данных TABLE. Этот тип данных указывается ключевым словом TABLE, которое следует за именем переменной. В эту переменную вставляются выбранные строки, и она служит возвращаемым значением функции.
Создание возвращающей табличное значение функции показано в примере ниже:
Функция EmployeesInProject отображает имена всех сотрудников, работающих над определенным проектом, номер которого задается входным параметром @projectNumber. Тогда как функция в общем случае возвращает набор строк, предложение RETURNS в определение данной функции содержит ключевое слово TABLE, указывающее, что функция возвращает табличное значение. (Обратите внимание на то, что в примере блок BEGIN/END необходимо опустить, а предложение RETURN содержит инструкцию SELECT.)
Использование функции Employees_in_Project приведено в примере ниже:
Возвращающие табличное значение функции и инструкция APPLY
Реляционная инструкция APPLY позволяет вызывать возвращающую табличное значение функцию для каждой строки табличного выражения. Эта инструкция задается в предложении FROM соответствующей инструкции SELECT таким же образом, как и инструкция JOIN. Инструкция APPLY может быть объединена с табличной функцией для получения результата, похожего на результирующий набор операции соединения двух таблиц. Существует две формы инструкции APPLY:
Инструкция CROSS APPLY возвращает те строки из внутреннего (левого) табличного выражения, которые совпадают с внешним (правым) табличным выражением. Таким образом, логически, инструкция CROSS APPLY функционирует так же, как и инструкция INNER JOIN.
Инструкция OUTER APPLY возвращает все строки из внутреннего (левого) табличного выражения. (Для тех строк, для которых нет совпадений во внешнем табличном выражении, он содержит значения NULL в столбцах внешнего табличного выражения.) Логически, инструкция OUTER APPLY эквивалентна инструкции LEFT OUTER JOIN.
Применение инструкции APPLY показано в примерах ниже:
Функция GetJob() возвращает набор строк с таблицы Works_on. В примере ниже этот результирующий набор «соединяется» предложением APPLY с содержимым таблицы Employee:
Результатом выполнения этих двух функций будут следующие две таблицы (отображаются после выполнения второй функции):
Второй запрос похожий на первый (но в нем используется инструкция OUTER APPLY), который логически соответствует операции внешнего соединения двух таблиц.
Возвращающие табличное значение параметры
Во всех версиях сервера, предшествующих SQL Server 2008, задача передачи подпрограмме множественных параметров была сопряжена со значительными сложностями. Для этого сначала нужно было создать временную таблицу, вставить в нее передаваемые значения, и только затем можно было вызывать подпрограмму. Начиная с версии SQL Server 2008, эта задача упрощена, благодаря возможности использования возвращающих табличное значение параметров, посредством которых результирующий набор может быть передан соответствующей подпрограмме.
Использование возвращающего табличное значение параметра показано в примере ниже:
В этом примере сначала определяется табличный тип departmentType. Это означает, что данный тип является типом данных TABLE, вследствие чего он разрешает вставку строк. В процедуре InsertProc объявляется переменная @Moscow с типом данных departmentType. (Предложение READONLY указывает, что содержимое этой таблицы нельзя изменять.) В последующем пакете в эту табличную переменную вставляются данные, после чего процедура запускается на выполнение. В процессе исполнения процедура вставляет строки из табличной переменной во временную таблицу #moscowTable. Вставленное содержимое временной таблицы выглядит следующим образом:
Использование возвращающих табличное значение параметров предоставляет следующие преимущества:
упрощается модель программирования подпрограмм;
уменьшается количество обращений к серверу и получений соответствующих ответов;
таблица результата может иметь произвольное количество строк.
Изменение структуры определяемых пользователями инструкций
Язык Transact-SQL также поддерживает инструкцию ALTER FUNCTION, которая модифицирует структуру определяемых пользователями инструкций (UDF). Эта инструкция обычно используется для удаления привязки функции к схеме. Все параметры инструкции ALTER FUNCTION имеют такое же значение, как и одноименные параметры инструкции CREATE FUNCTION.
Для удаления UDF применяется инструкция DROP FUNCTION. Удалить функцию может только ее владелец или член предопределенной роли db_owner или sysadmin.
Определяемые пользователем функции и среда CLR
В предыдущей статье мы рассмотрели способ создания хранимых процедур из управляемого кода среды CLR на языке C#. Этот подход можно использовать и для определяемых пользователем функций (UDF), с одним только различием, что для сохранения UDF в виде объекта базы данных используется инструкция CREATE FUNCTION, а не CREATE PROCEDURE. Кроме этого, определяемые пользователем функции также применяются в другом контексте, чем хранимые процедуры, поскольку UDF всегда возвращают значение.
В примере ниже показан исходный код определяемых пользователем функций (UDF), реализованный на языке C#:
В исходном коде определяемых пользователем функций в примере вычисляется новый бюджет проекта, увеличивая старый бюджет на определенное количество процентов. Вы можете использовать инструкцию CREATE ASSEMBLY для создания сборки CLR в базе данных, как это было показано ранее. Если вы прорабатывали примеры из предыдущей статьи и уже добавили сборку CLRStoredProcedures в базу данных, то вы можете обновить эту сборку, после ее перекомпиляции с новым классом (CLRStoredProcedures это имя моего проекта классов C#, в котором я добавлял определение хранимых процедур и функций, у вас сборка может называться иначе):
Инструкция CREATE FUNCTION в примере ниже сохраняет метод ComputeBudget в виде объекта базы данных, который в дальнейшем можно использовать в инструкциях для манипулирования данными.
Использование одной из таких инструкций, инструкции SELECT, показано в примере ниже:
Определяемую пользователем функцию можно поместить в разных местах инструкции SELECT. В примерах выше она вызывалась в предложениях WHERE, FROM и в списке выбора оператора SELECT.
Как написать функции в sql
Примечание
Синтаксис команды CREATE FUNCTION требует, чтобы тело функции было записано как строковая константа. Обычно для этого удобнее всего заключать строковую константу в доллары (см. Подраздел 4.1.2.4). Если вы решите использовать обычный синтаксис с заключением строки в апострофы, вам придётся дублировать апострофы ( ‘ ) и обратную косую черту ( \ ) (предполагается синтаксис спецпоследовательностей) в теле функции (см. Подраздел 4.1.2.1).
К аргументам SQL-функции можно обращаться в теле функции по именам или номерам. Ниже приведены примеры обоих вариантов.
Аргументы SQL-функции могут использоваться только как значения данных, но не как идентификаторы. Например, это приемлемо:
а это не будет работать:
Примечание
36.4.2. Функции SQL с базовыми типами
Мы также можем отказаться от имён аргументов и обращаться к ним по номерам:
Вот более полезная функция, которую можно использовать, чтобы дебетовать банковский счёт:
Пользователь может выполнить эту функцию, чтобы дебетовать счёт 17 на 100 долларов, так:
На практике обычно желательно получать от функции более полезный результат, чем константу 1, поэтому более реалистично такое определение:
Эта функция изменяет баланс и возвращает полученное значение. То же самое можно сделать в одной команде, применив RETURNING :
36.4.3. Функции SQL со сложными типами
В функциях с аргументами составных типов мы должны указывать не только, какой аргумент, но и какой атрибут (поле) этого аргумента нам нужен. Например, предположим, что emp — таблица, содержащая данные работников, и это же имя составного типа, представляющего каждую строку таблицы. Следующая функция double_salary вычисляет, каким было бы чьё-либо жалование в случае увеличения вдвое:
Однако это использование считается устаревшим, так как провоцирует путаницу. (Подробнее эти две записи составных значений строки таблицы описаны в Подразделе 8.16.5.)
Также возможно создать функцию, возвращающую составной тип. Например, эта функция возвращает одну строку emp :
В этом примере мы задали для каждого атрибута постоянное значение, но вместо этих констант можно подставить любые вычисления.
Учтите два важных требования относительно определения функции:
Порядок в списке выборки внутреннего запроса должен в точности совпадать с порядком следования столбцов в таблице, связанной с составным типом. (Имена столбцов, как показывает пример выше, для системы значения не имеют.)
Вы должны привести выражения в соответствие с определением составного типа, либо вы получите такие ошибки:
(ОШИБКА: функция, объявленная как возвращающая emp, возвращает varchar вместо text в столбце 1)
Ту же функцию можно определить другим способом:
Мы можем вызывать эту функцию напрямую, либо указав её в выражении значения:
либо обратившись к ней, как к табличной функции:
Второй способ более подробно описан в Подразделе 36.4.7.
Когда используется функция, возвращающая составной тип, может возникнуть желание получить из её результата только одно поле (атрибут). Это можно сделать, применяя такую запись:
Дополнительные скобки необходимы во избежание неоднозначности при разборе запроса. Если вы попытаетесь выполнить запрос без них, вы получите ошибку:
(ОШИБКА: синтаксическая ошибка (примерное положение: «.»))
Функциональную запись также можно использовать и для извлечения атрибутов:
Как рассказывалось в Подразделе 8.16.5, запись с указанием поля и функциональная запись являются равнозначными.
Ещё один вариант использования функции, возвращающей составной тип, заключается в передаче её результата другой функции, которая принимает этот тип строки на вход:
36.4.4. Функции SQL с выходными параметрами
Альтернативный способ описать результаты функции — определить её с выходными параметрами, как в этом примере:
Фактически здесь мы определили анонимный составной тип для результата функции. Показанный выше пример даёт тот же конечный результат, что и команды:
Но предыдущий вариант зачастую удобнее, так как он не требует отдельно заниматься определением составного типа. Заметьте, что имена, назначаемые выходным параметрам, не просто декоративные, а определяют имена столбцов анонимного составного типа. (Если вы опустите имя выходного параметра, система выберет имя сама.)
Заметьте, что выходные параметры не включаются в список аргументов при вызове такой функции из SQL. Это объясняется тем, что PostgreSQL определяет сигнатуру вызова функции, рассматривая только входные параметры. Это также значит, что при таких операциях, как удаление функции, в ссылках на функцию учитываются только типы входных параметров. Таким образом, удалить эту конкретную функцию можно любой из этих команд:
36.4.5. Функции SQL с переменным числом аргументов
На самом деле так вызвать эту функцию нельзя, или, по крайней мере, это не будет соответствовать определению функции. Параметру VARIADIC соответствуют одно или несколько вхождений типа его элемента, но не его собственного типа.
Но иногда бывает полезно передать функции с переменными параметрами уже подготовленный массив; особенно когда одна функция с переменными параметрами хочет передавать свой массив параметров другой. Также это более безопасный способ вызывать такую функцию, существующую в схеме, где могут создавать объекты недоверенные пользователи; см. Раздел 10.3. Это можно сделать, добавив VARIADIC в вызов:
Это предотвращает разворачивание переменного множества параметров функции в базовый тип, что позволяет сопоставить с ним значение типа массива. VARIADIC можно добавить только к последнему фактическому аргументу вызова функции.
Также указание VARIADIC даёт единственную возможность передать пустой массив функции с переменными параметрами, например, так:
А эти варианты нет:
36.4.6. Функции SQL со значениями аргументов по умолчанию
Функции могут быть объявлены со значениями по умолчанию для некоторых или всех входных аргументов. Значения по умолчанию подставляются, когда функция вызывается с недостаточным количеством фактических аргументов. Так как аргументы можно опускать только с конца списка фактических аргументов, все параметры после параметра со значением по умолчанию также получат значения по умолчанию. (Хотя запись с именованными аргументами могла бы ослабить это ограничение, оно всё же остаётся в силе, чтобы позиционные ссылки на аргументы оставались действительными.) Независимо от того, используете вы эту возможность или нет, она требует осторожности при вызове функций в базах данных, где одни пользователи не доверяют другим; см. Раздел 10.3.
Все функции SQL можно использовать в предложении FROM запросов, но наиболее полезно это для функций, возвращающих составные типы. Если функция объявлена как возвращающая базовый тип, она возвращает таблицу с одним столбцом. Если же функция объявлена как возвращающая составной тип, она возвращает таблицу со столбцами для каждого атрибута составного типа.
Как показывает этот пример, мы можем работать со столбцами результата функции так же, как если бы это были столбцы обычной таблицы.
Тогда в ответ мы получим:
Также возможно выдать несколько строк со столбцами, определяемыми выходными параметрами, следующим образом:
В этом примере не делается ничего такого, что мы не могли бы сделать, применив простое соединение, но для более сложных вычислений возможность поместить некоторую логику в функцию может быть весьма удобной.
В настоящее время функции, возвращающие множества, могут также вызываться в списке выборки запроса. Для каждой строки, которая генерируется самим запросом, вызывается функция, возвращающая множество, и для каждого элемента набора её результатов генерируется отдельная строка. Заметьте, однако, что эта возможность считается устаревшей и может быть ликвидирована в будущих выпусках. Предыдущий пример можно было бы также переписать с применением запросов следующим образом:
Примечание
Примечание
Например, предыдущий пример с суммой и произведением можно также переписать так:
36.4.10. Полиморфные функции SQL
(ОШИБКА: не удалось определить полиморфный тип, так как входные аргументы имеют тип «unknown»)
Функция с полиморфными аргументами может иметь фиксированный тип результата, однако обратное не допускается. Например:
(ОШИБКА: не удалось определить тип результата; ПОДРОБНОСТИ: Функция, возвращающая полиморфный тип, должна иметь минимум один полиморфный аргумент.)
Полиморфизм можно применять и с функциями, имеющими выходные аргументы. Например:
Полиморфизм также можно применять с функциями с переменными параметрами. Например:
36.4.11. Функции SQL с правилами сортировки
С другой стороны, если вы хотите, чтобы функция работала с определённым правилом сортировки, вне зависимости от того, с каким она была вызвана, вставьте предложения COLLATE где требуется в определении функции. Эта версия anyleast всегда будет сравнивать строки по правилам локали en_US :
Но заметьте, что при попытке применить правило к несортируемому типу данных, возникнет ошибка.
Если для фактических аргументов не удаётся определить общее правило сортировки, функция SQL считает, что им назначено правило сортировки по умолчанию для их типа данных (обычно это то же правило сортировки, что определено по умолчанию для базы данных, но оно может быть и другим для параметров доменных типов).
Поведение сортируемых параметров можно воспринимать как ограниченную форму полиморфизма, применимую только к текстовым типам данных.