User-defined function: Difference between revisions

Revision as of 09:20, 2 December 2022

Latest revision as of 16:32, 17 August 2025

(12 intermediate revisions by 8 users not shown)

Line 1:

{{Short description|Function provided by the user of a program or environment}}

A '''user-defined function''' ('''UDF''') is a [[function (programming)|function]] provided by the user of a program or environment, in a context where the usual assumption is that functions are built into the program or environment. UDFs are usually written for the requirement of its creator.

Line 7 ⟶ 8:

In the [[COBOL]] programming language, a user-defined function is an entity that is defined by the user by specifying a FUNCTION-ID paragraph. A user-defined function must return a value by specifying the RETURNING phrase of the procedure division header and they are invoked using the function-identifier syntax. See the ISO/IEC 1989:2014 Programming Language COBOL standard for details.

As of May 2022, the IBM Enterprise COBOL for [[z/OS]] 6.4 ([[IBM COBOL]]) compiler contains support for user-defined functions.

==Databases==

In [[relational database management system]]s, a user-defined function provides a mechanism for extending the functionality of the [[database server]] by adding a function, that can be evaluated in standard [[query language]] (usually [[SQL]]) statements. The [[SQL:2003ISO/IEC 9075|SQL standard]] standard distinguishes between [[Scalar (computing)|scalar]] and table functions. A scalar function returns only a single value (or [[null (SQL)|NULL]]), whereas a table function returns a (relational) table comprising zero or more rows, each row with one or more columns.

User-defined functions in SQL are declared using the <code>CREATE FUNCTION</code> statement. For example, a user-defined function that converts Celsius to Fahrenheit (a temperature scale used in USA) might be declared like this:

<syntaxhighlight lang="sql">

CREATE FUNCTION dbo.CtoF(Celsius FLOAT)

Line 19 ⟶ 20:

</syntaxhighlight>

Once created, a user-defined function may be used in [[expression (programming)|expressions]] in SQL statements. For example, it can be invoked where most other intrinsic functions are allowed. This also includes [[Select (SQL)|SELECT statements]], where the function can be used against data stored in tables in the database. Conceptually, the function is evaluated once per row in such usage. For example, assume a table named {{Code|Elements}}, with a row for each known chemical element. The table has a column named BoilingPoint for the boiling point of that element, in Celsius. The query

<syntaxhighlight lang="sql">

Line 26 ⟶ 27:

</syntaxhighlight>

would retrieve the name and the boiling point from each row. It invokes the <code>CtoF</code> user-defined function as declared above in order to convert the value in the column to a value in Fahrenheit.

Each user-defined function carries certain properties or characteristics. The SQL standard defines the following properties:

Line 49 ⟶ 50:

Performance Notes:

1.{{olist| On Microsoft SQL Server 2000 a table-valued function which "wraps" a View may be much faster than the View itself. The following MyFunction is an example of a "function-wrapper" which runs faster than the underlying view MyView:

<syntaxhighlight lang="tsql">

Line 83 ⟶ 84:

</syntaxhighlight>

2.| On Microsoft SQL Server 2005 the result of the same code execution is the opposite: view is executed faster than the "function-wrapper".}}

User-defined functions are subroutines made of one or more Transact-SQL statements that can be used to encapsulate code for reuse.

Line 106 ⟶ 107:

END

</syntaxhighlight>

Data type supported in Microsoft SQL Server 2000

Like a temporary table used to store results

Mostly used to define temporary variable of type (table) and the return value of a UDF

The scope is limited to function, stored procedure, or batch in which it is defined

Assignment operation is not allowed between (Table) variables

May be used in SELECT, INSERT, UPDATE, and DELETE

CREATE FUNCTION to create UDF

ALTER FUNCTION to change the characteristics of UDF

DROP FUNCTION to remove UDF

===Apache Hive===

Line 135 ⟶ 126:

|date=26 June 2015}}

</ref>

===Apache Doris===

Apache Doris, an open-source real-time analytical database, allows external users to contribute their own UDFs written in C++ to it.<ref>{{cite web |title=Apache Doris UDF |url=https://doris.apache.org/docs/dev/ecosystem/udf/contribute-udf?_highlight=udf |access-date=8 April 2023 |archive-date=10 April 2023 |archive-url=https://web.archive.org/web/20230410141729/https://doris.apache.org/docs/dev/ecosystem/udf/contribute-udf/?_highlight=udf |url-status=dead }}</ref>

==References==