Как вызвать функцию sql

5

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

В языках программирования обычно имеется два типа подпрограмм:

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

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

Создание и выполнение определяемых пользователем функций

Определяемые пользователем функции создаются посредством инструкции CREATE FUNCTION, которая имеет следующий синтаксис:

Параметр schema_name определяет имя схемы, которая назначается владельцем создаваемой UDF, а параметр function_name определяет имя этой функции. Параметр @param является входным параметром функции (формальным аргументом), чей тип данных определяется параметром type. Параметры функции — это значения, которые передаются вызывающим объектом определяемой пользователем функции для использования в ней. Параметр default определяет значение по умолчанию для соответствующего параметра функции. (Значением по умолчанию также может быть NULL.)

Предложение 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 возвращает это значение.

Вызов определяемой пользователем функции

Определенную пользователем функцию можно вызывать с помощью инструкций Transact-SQL, таких как SELECT, INSERT, UPDATE или DELETE. Вызов функции осуществляется, указывая ее имя с парой круглых скобок в конце, в которых можно задать один или несколько аргументов. Аргументы — это значения или выражения, которые передаются входным параметрам, определяемым сразу же после имени функции. При вызове функции, когда для ее параметров не определены значения по умолчанию, для всех этих параметров необходимо предоставить аргументы в том же самом порядке, в каком эти параметры определены в инструкции CREATE FUNCTION.

В примере ниже показан вызов функции 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:

Результатом выполнения этих двух функций будут следующие две таблицы (отображаются после выполнения второй функции):

В первом запросе примера результирующий набор табличной функции GetJob() «соединяется» с содержимым таблицы Employee посредством инструкции CROSS APPLY. Функция GetJob() играет роль правого ввода, а таблица 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

Use the CALL statement to execute a routine (a standalone procedure or function, or a procedure or function defined within a type or package) from within SQL.

The restrictions on user-defined function expressions specified in «Function Expressions» apply to the CALL statement as well.

Oracle Database PL/SQL Language Reference for information on creating such routine

You must have EXECUTE privilege on the standalone routine or on the type or package in which the routine is defined.

You can execute a routine in two ways. You can issue a call to the routine itself by name, by using the routine_clause , or you can invoke a routine inside the type of an expression, by using an object_access_expression .

Specify the name of the function or procedure being called, or a synonym that resolves to a function or procedure.

When you call a member function or procedure of a type, if the first argument ( SELF ) is a null IN OUT argument, then Oracle Database returns an error. If SELF is a null IN argument, then the database returns null. In both cases, the function or procedure is not invoked.

Restriction on Functions

If the routine is a function, then the INTO clause is required.

Specify the schema in which the standalone routine, or the package or type containing the routine, resides. If you do not specify schema , then Oracle Database assumes the routine is in your own schema.

type or package

Specify the type or package in which the routine is defined.

In a distributed database system, specify the name of the database containing the standalone routine, or the package or function containing the routine. If you omit dblink , then Oracle Database looks in your local database.

«Calling a Procedure: Example» for an example of calling a routine directly

If you have an expression of an object type, such as a type constructor or a bind variable, then you can use this form of expression to call a routine defined within the type. In this context, the object_access_expression is limited to method invocations.

«Object Access Expressions» for syntax and semantics of this form of expression, and «Calling a Procedure Using an Expression of an Object Type: Example» for an example of calling a routine using an expression of an object type

Specify one or more arguments to the routine, if the routine takes arguments. You can use positional, named, or mixed notation for argument . For example, all of the following notations are correct:

Restrictions on Applying Arguments to Routines

The argument is subject to the following restrictions:

The data types of the parameters passed by the CALL statement must be SQL data types. They cannot be PL/SQL-only data types such as BOOLEAN .

An argument cannot be a pseudocolumn or either of the object reference functions VALUE or REF .

Any argument that is an IN OUT or OUT argument of the routine must correspond to a host variable expression.

The number of arguments, including any return argument, is limited to 1000.

You cannot bind arguments of character and raw data types ( CHAR , VARCHAR2 , NCHAR , NVARCHAR2 , RAW , LONG RAW ) that are larger than 4K.

The INTO clause applies only to calls to functions. Specify which host variable will store the return value of the function.

Specify the value or condition of the host variable.

Pro*C/C++ Programmer’s Guide for more information on host variables and indicator variables

Calling a Procedure: Example

The following statement removes the Entertainment department (created in «Inserting Sequence Values: Example» ) using uses the remove_dept procedure. See Oracle Database PL/SQL Language Reference for the example that creates this procedure.

Calling a Procedure Using an Expression of an Object Type: Example

The following examples show how call a procedure by using an expression of an object type in the CALL statement. The example uses the warehouse_typ object type in the order entry sample schema OE :

The next example shows how to use an external function to achieve the same thing:

User Defined Function In SQL Server-2020

In this article, we will understand User Defined Function in sql server with different types of function in sql server a) Scalar function b) Inline table-valued functions c) Multi-statement table-valued functions

User-defined Functions In SQL Server

User-defined functions also called UDF. In SQL server there are 3 different types of User-Defined functions

1. Scalar function.
2. Inline table-valued functions.
3. Multi-statement table-valued functions.

You can find these functions folder in SQL Server Management studio see below image

Scalar Functions

Why this function called scalar function because it returns a single value.

Example

In-built function with parameter

In-built function with 0 parameter

General Syntax for Scalar function:

How to create User-defined Scalar function in the SQL server?

In the above function, we calculate the age.

To call this function if you only use

Select function-name(parameter); then it will give an error to overcome this error we need to give a fully qualified name or 2 part name or at-least database owner and name append before the function name. In our case, dbo is a database owner.

Second example: you can use this in select clause as shown below

You can do this in store-procedure also but you can not use stored procedure in select clause and where clause.

How to alter function in the SQL server?

The alter keyword needs to use instead of create statement and whole function you need to write here.

How to delete or remove a function from the SQL server?

You need to use a drop keyword and function name to remove function in the SQL server.

Also, a fully qualified name or database administrator name needs to use along with function name if you use only function name then it will give an error.

Example

Inline Table-valued function

What is the table-valued function?

The table-valued function is a user-defined function that returns the table as a data type. The return type is a table and it is treated as a table.

In this type of function, we return a table and in previous we learn in that we return a single value.

General Syntax of table-valued function:

1. In the above general syntax, you will see returns return type is table means this function will return table.
2. In this function, the body is not enclosed in Begin and End block
3. Whatever table is returned that table structure specifically depends upon what select statement you used.
4. The table-valued function is treated as a table.

If you use, begin and end statements in this function create syntax you will get below error.

State 31, Procedure getEmployeeByGender, Line 7 [Batch Start Line 0]

-Incorrect syntax near ‘BEGIN’.

Example

In the above function, we pass gender as a parameter and this parameter is used in where clause in the below select statement.

To call this function we need to use below syntax.

Select * from function_name(parameter);

In inline-table-valued function when you call this function you can use where clause in that.

Where we can use Inline Table-Valued functions?

• Inline table-valued functions can be used to achieve the functionality of parametrized views
• You can use a table which is returned by inline table valued function for joins or to form join with another table.

Query By Using Table-valued function with another table

How to modify the table-valued function?

You can use alter keyword instead of create a word in function creation. The rest of the script is the same.

Multi-statement table-valued functions (MSTVF)

A multi-statement table-valued function is a table-valued function that returns the result of multiple statements.

It is useful because you can execute multiple queries inside that function.

In this type of function, you need to define a table variable and return that variable.

Also, you can create the structure of a table.

What is the difference between the table-valued function and multi-statement table-valued function?

• In the Inline-table-valued function, we don’t have the structure of a table in return statement whereas in a multi-statement table-valued function we have the structure of a table in returns clause.
• The inline table-valued function doesn’t have to begin and end block while MSTVF has begin and end block.
• An inline-table-valued function has better performance than MSTVF when you able to achieve function using inline-table-valued try to used over MSTVF.
• It is possible to update a table using inline-table-valued but can’t be using MSTVF

Functions Important Concept In SQL Server 2020

The function is either deterministic or non-deterministic at any given time in SQL.

Deterministic and Nondeterministic function:

It is a function that returns the same value or results at any time no matter how many times you run the same function with specific input and given the state of the database remains the same.

Ex. Square(), Avg(), Sum()

Note: all aggregate functions are deterministic functions.

Nondeterministic functions:

These are the function which may return different results each time they called with a specific set of input values even if the database state that they access remains the same.

Rand Function:

This function act as deterministic as well as non-deterministic based on the seed value.

Encryption of function definition using with Encryption Option:

As we are able to encrypt the stored procedure similar way we can encrypt the function also in the sql server.

See below image we create a function first and after that, we call that function to see is this working fine or not.

After this, we call this function with the help of an in-built stored procedure which is sp_helptext and this procedure is used for getting the function text.

To avoid this and we have scenario we don’t want the end user to see our code then we need to encrypt this function and by using Encryption keyword we can do that see below code.

As we already create function we will alter and add encryption tag in that

After doing this when you run below command you will get an error.

Below is the error we got.

The text for object ‘fn_getEmployeeEmailByID’ is encrypted.

SchemaBinding Function:

Schema binding specifies that the function is bound to the database objects that it references. When you specify schema binding, the base object can not be modified or deleted in any way that affects our function definition.

To achive modification you need to modify your function first then you will able to modify a base object.

See above function create a statement in this we use ‘A’ table which, suppose accidentally anyone drops that table or modify column names or delete columns on which our function depends then our function will not execute properly and it will throw an error.

So to avoid this we use Schemabinding Option.

Now our function depends on the table ‘A’. I will drop that table A.

After drop table and run our function we get below error.

Msg 208, Level 16, State 1, Line 10 Invalid object name ‘A’.

To avoid this we need schema binding, for that, we need to use this with SchemaBinding

So, if I want schema binding we need to alter the function and execute that function the syntax for this is below.

By doing this we get an error which is below

Msg 4512, Level 16, State 3, Procedure getnameByID, Line 5 [Batch Start Line 1]

Cannot schema bind function ‘getnameByID’ because the name ‘A’ is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

To overcome this error we need to use table name in 2 parts like below dbo.A

After this, our function modified successfully and if we try to delete the table we get below error.

Msg 3729, Level 16, State 1, Line 9

Cannot DROP TABLE ‘A’ because it is being referenced by object ‘getnameByID’.

Как вызвать функцию sql

SQL-функции выполняют произвольный список операторов SQL и возвращают результат последнего запроса в списке. В простом случае (не с множеством) будет возвращена первая строка результата последнего запроса. (Помните, что понятие « первая строка » в наборе результатов с несколькими строками определено точно, только если присутствует ORDER BY .) Если последний запрос вообще не вернёт строки, будет возвращено значение NULL.

Кроме того, можно объявить SQL-функцию как возвращающую множество (то есть, несколько строк), указав в качестве возвращаемого типа функции SETOF некий_тип , либо объявив её с указанием RETURNS TABLE( столбцы ) . В этом случае будут возвращены все строки результата последнего запроса. Подробнее это описывается ниже.

Тело SQL-функции должно представлять собой список SQL-операторов, разделённых точкой с запятой. Точка с запятой после последнего оператора может отсутствовать. Если только функция не объявлена как возвращающая void , последним оператором должен быть SELECT , либо INSERT , UPDATE или DELETE с предложением RETURNING .

Примечание

Прежде чем начинается выполнение команд, разбирается всё тело SQL-функции. Когда SQL-функция содержит команды, модифицирующие системные каталоги (например, CREATE TABLE ), действие таких команд не будет проявляться на стадии анализа последующих команд этой функции. Так, например, команды CREATE TABLE foo (. ); INSERT INTO foo VALUES(. ); не будут работать, как ожидается, если их упаковать в одну SQL-функцию, так как foo не будет существовать к моменту разбору команды INSERT . В подобных ситуациях вместо SQL-функции рекомендуется использовать PL/pgSQL .

Синтаксис команды CREATE FUNCTION требует, чтобы тело функции было записано как строковая константа. Обычно для этого удобнее всего заключать строковую константу в доллары (см. Подраздел 4.1.2.4). Если вы решите использовать обычный синтаксис с заключением строки в апострофы, вам придётся дублировать апострофы ( ‘ ) и обратную косую черту ( \ ) (предполагается синтаксис спецпоследовательностей) в теле функции (см. Подраздел 4.1.2.1).

35.5.1. Аргументы SQL -функций

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

Чтобы использовать имя, объявите аргумент функции как именованный, а затем просто пишите это имя в теле функции. Если имя аргумента совпадает с именем какого-либо столбца в текущей SQL-команде внутри функции, имя столбца будет иметь приоритет. Чтобы всё же перекрыть имя столбца, дополните имя аргумента именем самой функции, то есть запишите его в виде имя_функции . имя_аргумента . (Если и это имя будет конфликтовать с полным именем столбца, снова выиграет имя столбца. Неоднозначности в этом случае вы можете избежать, выбрав другой псевдоним для таблицы в SQL-команде.)

Старый подход с нумерацией позволяет обращаться к аргументам, применяя запись $ n : $1 обозначает первый аргумент, $2 — второй и т. д. Это будет работать и в том случае, если данному аргументу назначено имя.

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

Аргументы SQL-функции могут использоваться только как значения данных, но не как идентификаторы. Например, это приемлемо:

а это не будет работать:

Примечание

Возможность обращаться к аргументам SQL-функций по именам появилась в PostgreSQL 9.2. В функциях, которые должны работать со старыми серверами, необходимо применять запись $ n .

35.5.2. Функции SQL с базовыми типами

Заметьте, что мы определили псевдоним столбца в теле функции для её результата (дали ему имя result ), но этот псевдоним не виден снаружи функции. Вследствие этого, столбец результата получил имя one , а не result .

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

Мы также можем отказаться от имён аргументов и обращаться к ним по номерам:

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

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

В этом примере мы выбрали имя accountno для первого аргумента, но это же имя имеет столбец в таблице bank . В команде UPDATE имя accountno относится к столбцу bank.accountno , так для обращения к аргументу нужно записать tf1.accountno . Конечно, мы могли бы избежать этого, выбрав другое имя для аргумента.

На практике обычно желательно получать от функции более полезный результат, чем константу 1, поэтому более реалистично такое определение:

Эта функция изменяет баланс и возвращает полученное значение. То же самое можно сделать в одной команде, применив RETURNING :

несмотря на то, что в других контекстах Postgres Pro выполнил бы неявное приведение для преобразования integer в float8 . Правильный вариант выглядит так:

35.5.3. Функции SQL со сложными типами

В функциях с аргументами составных типов мы должны указывать не только, какой аргумент, но и какой атрибут (поле) этого аргумента нам нужен. Например, предположим, что emp — таблица, содержащая данные работников, и это же имя составного типа, представляющего каждую строку таблицы. Следующая функция double_salary вычисляет, каким было бы чьё-либо жалование в случае увеличения вдвое:

Обратите внимание на запись $1.salary позволяющую выбрать одно поле из значения строки аргумента. Также заметьте, что в вызывающей команде SELECT указание имя_таблицы .* выбирает всю текущую строку таблицы как составное значение. На строку таблицы можно сослаться и просто по имени таблицы, например так:

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

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

Также возможно создать функцию, возвращающую составной тип. Например, эта функция возвращает одну строку emp :

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

Учтите два важных требования относительно определения функции:

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

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

Ту же функцию можно определить другим способом:

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

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

либо обратившись к ней, как к табличной функции:

Второй способ более подробно описан в Подразделе 35.5.7.

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

Дополнительные скобки необходимы во избежание неоднозначности при разборе запроса. Если вы попытаетесь выполнить запрос без них, вы получите ошибку:

(ОШИБКА: синтаксическая ошибка (примерное положение: «.»))

Функциональную запись также можно использовать и для извлечения атрибутов:

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

Ещё один вариант использования функции, возвращающей составной тип, заключается в передаче её результата другой функции, которая принимает этот тип строки на вход:

35.5.4. Функции SQL с выходными параметрами

Альтернативный способ описать результаты функции — определить её с выходными параметрами, как в этом примере:

Это по сути не отличается от версии add_em , показанной в Подразделе 35.5.2. Действительная ценность выходных параметров в том, что они позволяют удобным способом определить функции, возвращающие несколько столбцов. Например:

Фактически здесь мы определили анонимный составной тип для результата функции. Показанный выше пример даёт тот же конечный результат, что и команды:

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

Заметьте, что выходные параметры не включаются в список аргументов при вызове такой функции из SQL. Это объясняется тем, что Postgres Pro определяет сигнатуру вызова функции, рассматривая только входные параметры. Это также значит, что при таких операциях, как удаление функции, в ссылках на функцию учитываются только типы входных параметров. Таким образом, удалить эту конкретную функцию можно любой из этих команд:

Параметры функции могут быть объявлены как IN (по умолчанию), OUT , INOUT или VARIADIC . Параметр INOUT действует как входной (является частью списка аргументов при вызове) и как выходной (часть типа записи результата). Параметры VARIADIC являются входными, но обрабатывается специальным образом, как описано далее.

35.5.5. Функции SQL с переменным числом аргументов

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

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

Но иногда бывает полезно передать функции с переменными параметрами уже подготовленный массив; особенно когда одна функция с переменными параметрами хочет передавать свой массив параметров другой. Также это более безопасный способ вызывать такую функцию, существующую в схеме, где могут создавать объекты недоверенные пользователи; см. Раздел 10.3. Это можно сделать, добавив VARIADIC в вызов:

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

Также указание VARIADIC даёт единственную возможность передать пустой массив функции с переменными параметрами, например, так:

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

Элементы массива, создаваемые из переменных параметров, считаются не имеющими собственных имён. Это означает, что передать функции с переменными параметрами именованные аргументы нельзя (см. Раздел 4.3), если только при вызове не добавлено VARIADIC . Например, этот вариант будет работать:

А эти варианты нет:

35.5.6. Функции SQL со значениями аргументов по умолчанию

Функции могут быть объявлены со значениями по умолчанию для некоторых или всех входных аргументов. Значения по умолчанию подставляются, когда функция вызывается с недостаточным количеством фактических аргументов. Так как аргументы можно опускать только с конца списка фактических аргументов, все параметры после параметра со значением по умолчанию также получат значения по умолчанию. (Хотя запись с именованными аргументами могла бы ослабить это ограничение, оно всё же остаётся в силе, чтобы позиционные ссылки на аргументы оставались действительными.) Независимо от того, используете вы эту возможность или нет, она требует осторожности при вызове функций в базах данных, где одни пользователи не доверяют другим; см. Раздел 10.3.

(ОШИБКА: функция foo() не существует) Вместо ключевого слова DEFAULT можно использовать знак = .

35.5.7. Функции SQL , порождающие таблицы

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

Как показывает этот пример, мы можем работать со столбцами результата функции так же, как если бы это были столбцы обычной таблицы.

Заметьте, что мы получаем из данной функции только одну строку. Это объясняется тем, что мы не использовали указание SETOF . Оно описывается в следующем разделе.

35.5.8. Функции SQL , возвращающие множества

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

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

Тогда в ответ мы получим:

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

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

Часто бывает полезно сконструировать результат запроса, вызывая функцию, возвращающую множество, несколько раз, передавая при каждом вызове параметры из очередных строк таблицы или подзапроса. Для этого рекомендуется применить ключевое слово LATERAL , описываемое в Подразделе 7.2.1.5. Ниже приведён пример использования функции, возвращающей множество, для перечисления элементов древовидной структуры:

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

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

Заметьте, что в последней команде SELECT для Child2 , Child3 и т. д. строки не выдаются. Это происходит потому, что listchildren возвращает пустое множество для этих аргументов, так что строки результата не генерируются. Это же поведение мы получаем при внутреннем соединении с результатом функции с применением LATERAL .

Поведение Postgres Pro с функциями, возвращающими множества, в списке выборки запроса практически не отличается от поведения с такими функциями, помещёнными в предложение LATERAL FROM . Например, запрос:

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

Если в списке выборки запроса используются несколько функций, возвращающих запросы, они вычисляются примерно так же, как если бы они были помещены в один элемент LATERAL ROWS FROM( . ) предложения FROM . Для каждой строки из нижележащего запроса выдаётся строка с первым результатом каждой функции, а затем строка со вторым результатом и так далее. Если какие-либо из функций, возвращающих множества, выдают меньше результатов, чем другие, то вместо недостающих данных подставляются значения NULL, так что общее число строк, выдаваемых для одной нижележащей строки, равно числу строк, которое выдаёт функция с наибольшим количеством строк в возвращаемом множестве. Таким образом, функции, возвращающие множества, выполняются совместно, пока все их множества не будут исчерпаны, а затем выполнение продолжается со следующей нижележащей строкой.

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

возвращающие множества функции srf2 , srf3 и srf5 будут выполняться совместно для каждой строки tab , а затем srf1 и srf4 будут совместно применяться к каждой строке, произведённой нижними функциями.

Функции, возвращающие множества, нельзя использовать в конструкциях, вычисляемых по условию, например, CASE или COALESCE . Например, рассмотрите запрос

Может показаться, что он должен выдать пять экземпляров входных строк, в которых x > 0 , и по одному экземпляру остальных строк; но на деле, так как generate_series(1, 5) будет выполняться в неявном элементе LATERAL FROM до того, как выражение CASE вообще будет рассматриваться, должно было бы выдаваться пять экземпляров абсолютно всех выходных строк. Во избежание путаницы в таких случаях выдаётся ошибка при разборе запроса.

Примечание

Если последняя команда функции — INSERT , UPDATE или DELETE с RETURNING , эта команда будет всегда выполняться до завершения, даже если функция не объявлена с указанием SETOF или вызывающий запрос не выбирает все строки результата. Все дополнительные строки, выданные предложением RETURNING , просто игнорируются, но соответствующие изменения в таблице всё равно произойдут (и будут завершены до выхода из функции).

Примечание

В Postgres Pro до версии 10 при помещении нескольких функций, возвращающих множества, в один список выборки поведение было не очень разумным, если они возвращали не одинаковое число строк. В таких случаях число выходных строк равнялось наименьшему общему множителю количеств строк, возвращаемых этими функциями. Также и вложенные функции, возвращающие множества, работали не так, как описано выше; у такой функции мог быть максимум один аргумент, возвращающий множество, и каждая вложенность вычислялась независимо. Кроме того, ранее допускалось и условное выполнение (вычисление таких функций внутри CASE и т. п.), что ещё больше всё усложняло. При написании запросов, которые должны работать и со старыми версиями Postgres Pro , рекомендуется использовать синтаксис LATERAL , так как это гарантирует одинаковый результат с разными версиями. Если в вашем запросе используется условное вычисление функции, возвращающей множество, его можно исправить, переместив проверку условия в специально созданную функцию, возвращающую множество. Например:

можно заменить на

Это будет работать одинаково во всех версиях Postgres Pro .

35.5.9. Функции SQL , возвращающие таблицы ( TABLE )

Есть ещё один способ объявить функцию, возвращающую множества, — использовать синтаксис RETURNS TABLE( столбцы ) . Это равнозначно использованию одного или нескольких параметров OUT с объявлением функции как возвращающей SETOF record (или SETOF тип единственного параметра, если это применимо). Этот синтаксис описан в последних версиях стандарта SQL, так что этот вариант может быть более портируемым, чем SETOF .

Например, предыдущий пример с суммой и произведением можно также переписать так:

Запись RETURNS TABLE не позволяет явно указывать OUT и INOUT для параметров — все выходные столбцы необходимо записать в списке TABLE .

35.5.10. Полиморфные функции SQL

Обратите внимание на приведение типа ‘a’::text , определяющее, что аргумент имеет тип text . Оно необходимо, если аргумент задаётся просто строковой константой, так как иначе он будет воспринят как имеющий тип unknown , а массив типов unknown является недопустимым. Без этого приведения вы получите такую ошибку:

(ОШИБКА: не удалось определить полиморфный тип, так как входные аргументы имеют тип «unknown»)

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

(ОШИБКА: не удалось определить тип результата; ПОДРОБНОСТИ: Функция, возвращающая полиморфный тип, должна иметь минимум один полиморфный аргумент.)

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

Полиморфизм также можно применять с функциями с переменными параметрами. Например:

35.5.11. Функции SQL с правилами сортировки

Когда функция SQL принимает один или несколько параметров сортируемых типов данных, правило сортировки определяется при каждом вызове функции, в зависимости от правил сортировки, связанных с фактическими аргументами, как описано в Разделе 22.2. Если правило сортировки определено успешно (то есть не возникло конфликтов между неявно установленными правилами сортировки аргументов), оно неявно назначается для всех сортируемых параметров. Выбранное правило будет определять поведение операций, связанных с сортировкой, в данной функции. Например, для показанной выше функции anyleast , результат

будет зависеть от правила сортировки по умолчанию, заданного в базе данных. С локалью C результатом будет строка ABC , но со многими другими локалями это будет abc . Нужное правило сортировки можно установить принудительно, добавив предложение COLLATE к одному из аргументов функции, например:

С другой стороны, если вы хотите, чтобы функция работала с определённым правилом сортировки, вне зависимости от того, с каким она была вызвана, вставьте предложения COLLATE где требуется в определении функции. Эта версия anyleast всегда будет сравнивать строки по правилам локали en_US :

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

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

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