diff options
Diffstat (limited to 'doc/src/sgml/html/xfunc-sql.html')
| -rw-r--r-- | doc/src/sgml/html/xfunc-sql.html | 1079 |
1 files changed, 1079 insertions, 0 deletions
diff --git a/doc/src/sgml/html/xfunc-sql.html b/doc/src/sgml/html/xfunc-sql.html new file mode 100644 index 0000000..b92bbca --- /dev/null +++ b/doc/src/sgml/html/xfunc-sql.html @@ -0,0 +1,1079 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>37.5. Query Language (SQL) Functions</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets V1.79.1" /><link rel="prev" href="xproc.html" title="37.4. User-Defined Procedures" /><link rel="next" href="xfunc-overload.html" title="37.6. Function Overloading" /></head><body id="docContent" class="container-fluid col-10"><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">37.5. Query Language (<acronym xmlns="http://www.w3.org/1999/xhtml" class="acronym">SQL</acronym>) Functions</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="xproc.html" title="37.4. User-Defined Procedures">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="extend.html" title="Chapter 37. Extending SQL">Up</a></td><th width="60%" align="center">Chapter 37. Extending <acronym xmlns="http://www.w3.org/1999/xhtml" class="acronym">SQL</acronym></th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 13.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="xfunc-overload.html" title="37.6. Function Overloading">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="XFUNC-SQL"><div class="titlepage"><div><div><h2 class="title" style="clear: both">37.5. Query Language (<acronym class="acronym">SQL</acronym>) Functions</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="xfunc-sql.html#XFUNC-SQL-FUNCTION-ARGUMENTS">37.5.1. Arguments for <acronym class="acronym">SQL</acronym> Functions</a></span></dt><dt><span class="sect2"><a href="xfunc-sql.html#XFUNC-SQL-BASE-FUNCTIONS">37.5.2. <acronym class="acronym">SQL</acronym> Functions on Base Types</a></span></dt><dt><span class="sect2"><a href="xfunc-sql.html#XFUNC-SQL-COMPOSITE-FUNCTIONS">37.5.3. <acronym class="acronym">SQL</acronym> Functions on Composite Types</a></span></dt><dt><span class="sect2"><a href="xfunc-sql.html#XFUNC-OUTPUT-PARAMETERS">37.5.4. <acronym class="acronym">SQL</acronym> Functions with Output Parameters</a></span></dt><dt><span class="sect2"><a href="xfunc-sql.html#XFUNC-SQL-VARIADIC-FUNCTIONS">37.5.5. <acronym class="acronym">SQL</acronym> Functions with Variable Numbers of Arguments</a></span></dt><dt><span class="sect2"><a href="xfunc-sql.html#XFUNC-SQL-PARAMETER-DEFAULTS">37.5.6. <acronym class="acronym">SQL</acronym> Functions with Default Values for Arguments</a></span></dt><dt><span class="sect2"><a href="xfunc-sql.html#XFUNC-SQL-TABLE-FUNCTIONS">37.5.7. <acronym class="acronym">SQL</acronym> Functions as Table Sources</a></span></dt><dt><span class="sect2"><a href="xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-SET">37.5.8. <acronym class="acronym">SQL</acronym> Functions Returning Sets</a></span></dt><dt><span class="sect2"><a href="xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-TABLE">37.5.9. <acronym class="acronym">SQL</acronym> Functions Returning <code class="literal">TABLE</code></a></span></dt><dt><span class="sect2"><a href="xfunc-sql.html#XFUNC-SQL-POLYMORPHIC-FUNCTIONS">37.5.10. Polymorphic <acronym class="acronym">SQL</acronym> Functions</a></span></dt><dt><span class="sect2"><a href="xfunc-sql.html#id-1.8.3.8.19">37.5.11. <acronym class="acronym">SQL</acronym> Functions with Collations</a></span></dt></dl></div><a id="id-1.8.3.8.2" class="indexterm"></a><p> + SQL functions execute an arbitrary list of SQL statements, returning + the result of the last query in the list. + In the simple (non-set) + case, the first row of the last query's result will be returned. + (Bear in mind that <span class="quote">“<span class="quote">the first row</span>”</span> of a multirow + result is not well-defined unless you use <code class="literal">ORDER BY</code>.) + If the last query happens + to return no rows at all, the null value will be returned. + </p><p> + Alternatively, an SQL function can be declared to return a set (that is, + multiple rows) by specifying the function's return type as <code class="literal">SETOF + <em class="replaceable"><code>sometype</code></em></code>, or equivalently by declaring it as + <code class="literal">RETURNS TABLE(<em class="replaceable"><code>columns</code></em>)</code>. In this case + all rows of the last query's result are returned. Further details appear + below. + </p><p> + The body of an SQL function must be a list of SQL + statements separated by semicolons. A semicolon after the last + statement is optional. Unless the function is declared to return + <code class="type">void</code>, the last statement must be a <code class="command">SELECT</code>, + or an <code class="command">INSERT</code>, <code class="command">UPDATE</code>, or <code class="command">DELETE</code> + that has a <code class="literal">RETURNING</code> clause. + </p><p> + Any collection of commands in the <acronym class="acronym">SQL</acronym> + language can be packaged together and defined as a function. + Besides <code class="command">SELECT</code> queries, the commands can include data + modification queries (<code class="command">INSERT</code>, + <code class="command">UPDATE</code>, and <code class="command">DELETE</code>), as well as + other SQL commands. (You cannot use transaction control commands, e.g., + <code class="command">COMMIT</code>, <code class="command">SAVEPOINT</code>, and some utility + commands, e.g., <code class="literal">VACUUM</code>, in <acronym class="acronym">SQL</acronym> functions.) + However, the final command + must be a <code class="command">SELECT</code> or have a <code class="literal">RETURNING</code> + clause that returns whatever is + specified as the function's return type. Alternatively, if you + want to define a SQL function that performs actions but has no + useful value to return, you can define it as returning <code class="type">void</code>. + For example, this function removes rows with negative salaries from + the <code class="literal">emp</code> table: + +</p><pre class="screen"> +CREATE FUNCTION clean_emp() RETURNS void AS ' + DELETE FROM emp + WHERE salary < 0; +' LANGUAGE SQL; + +SELECT clean_emp(); + + clean_emp +----------- + +(1 row) +</pre><p> + </p><div class="note"><h3 class="title">Note</h3><p> + The entire body of a SQL function is parsed before any of it is + executed. While a SQL function can contain commands that alter + the system catalogs (e.g., <code class="command">CREATE TABLE</code>), the effects + of such commands will not be visible during parse analysis of + later commands in the function. Thus, for example, + <code class="literal">CREATE TABLE foo (...); INSERT INTO foo VALUES(...);</code> + will not work as desired if packaged up into a single SQL function, + since <code class="structname">foo</code> won't exist yet when the <code class="command">INSERT</code> + command is parsed. It's recommended to use <span class="application">PL/pgSQL</span> + instead of a SQL function in this type of situation. + </p></div><p> + The syntax of the <code class="command">CREATE FUNCTION</code> command requires + the function body to be written as a string constant. It is usually + most convenient to use dollar quoting (see <a class="xref" href="sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING" title="4.1.2.4. Dollar-Quoted String Constants">Section 4.1.2.4</a>) for the string constant. + If you choose to use regular single-quoted string constant syntax, + you must double single quote marks (<code class="literal">'</code>) and backslashes + (<code class="literal">\</code>) (assuming escape string syntax) in the body of + the function (see <a class="xref" href="sql-syntax-lexical.html#SQL-SYNTAX-STRINGS" title="4.1.2.1. String Constants">Section 4.1.2.1</a>). + </p><div class="sect2" id="XFUNC-SQL-FUNCTION-ARGUMENTS"><div class="titlepage"><div><div><h3 class="title">37.5.1. Arguments for <acronym class="acronym">SQL</acronym> Functions</h3></div></div></div><a id="id-1.8.3.8.9.2" class="indexterm"></a><p> + Arguments of a SQL function can be referenced in the function + body using either names or numbers. Examples of both methods appear + below. + </p><p> + To use a name, declare the function argument as having a name, and + then just write that name in the function body. If the argument name + is the same as any column name in the current SQL command within the + function, the column name will take precedence. To override this, + qualify the argument name with the name of the function itself, that is + <code class="literal"><em class="replaceable"><code>function_name</code></em>.<em class="replaceable"><code>argument_name</code></em></code>. + (If this would conflict with a qualified column name, again the column + name wins. You can avoid the ambiguity by choosing a different alias for + the table within the SQL command.) + </p><p> + In the older numeric approach, arguments are referenced using the syntax + <code class="literal">$<em class="replaceable"><code>n</code></em></code>: <code class="literal">$1</code> refers to the first input + argument, <code class="literal">$2</code> to the second, and so on. This will work + whether or not the particular argument was declared with a name. + </p><p> + If an argument is of a composite type, then the dot notation, + e.g., <code class="literal"><em class="replaceable"><code>argname</code></em>.<em class="replaceable"><code>fieldname</code></em></code> or + <code class="literal">$1.<em class="replaceable"><code>fieldname</code></em></code>, can be used to access attributes of the + argument. Again, you might need to qualify the argument's name with the + function name to make the form with an argument name unambiguous. + </p><p> + SQL function arguments can only be used as data values, + not as identifiers. Thus for example this is reasonable: +</p><pre class="programlisting"> +INSERT INTO mytable VALUES ($1); +</pre><p> +but this will not work: +</p><pre class="programlisting"> +INSERT INTO $1 VALUES (42); +</pre><p> + </p><div class="note"><h3 class="title">Note</h3><p> + The ability to use names to reference SQL function arguments was added + in <span class="productname">PostgreSQL</span> 9.2. Functions to be used in + older servers must use the <code class="literal">$<em class="replaceable"><code>n</code></em></code> notation. + </p></div></div><div class="sect2" id="XFUNC-SQL-BASE-FUNCTIONS"><div class="titlepage"><div><div><h3 class="title">37.5.2. <acronym class="acronym">SQL</acronym> Functions on Base Types</h3></div></div></div><p> + The simplest possible <acronym class="acronym">SQL</acronym> function has no arguments and + simply returns a base type, such as <code class="type">integer</code>: + +</p><pre class="screen"> +CREATE FUNCTION one() RETURNS integer AS $$ + SELECT 1 AS result; +$$ LANGUAGE SQL; + +-- Alternative syntax for string literal: +CREATE FUNCTION one() RETURNS integer AS ' + SELECT 1 AS result; +' LANGUAGE SQL; + +SELECT one(); + + one +----- + 1 +</pre><p> + </p><p> + Notice that we defined a column alias within the function body for the result of the function + (with the name <code class="literal">result</code>), but this column alias is not visible + outside the function. Hence, the result is labeled <code class="literal">one</code> + instead of <code class="literal">result</code>. + </p><p> + It is almost as easy to define <acronym class="acronym">SQL</acronym> functions + that take base types as arguments: + +</p><pre class="screen"> +CREATE FUNCTION add_em(x integer, y integer) RETURNS integer AS $$ + SELECT x + y; +$$ LANGUAGE SQL; + +SELECT add_em(1, 2) AS answer; + + answer +-------- + 3 +</pre><p> + </p><p> + Alternatively, we could dispense with names for the arguments and + use numbers: + +</p><pre class="screen"> +CREATE FUNCTION add_em(integer, integer) RETURNS integer AS $$ + SELECT $1 + $2; +$$ LANGUAGE SQL; + +SELECT add_em(1, 2) AS answer; + + answer +-------- + 3 +</pre><p> + </p><p> + Here is a more useful function, which might be used to debit a + bank account: + +</p><pre class="programlisting"> +CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS numeric AS $$ + UPDATE bank + SET balance = balance - debit + WHERE accountno = tf1.accountno; + SELECT 1; +$$ LANGUAGE SQL; +</pre><p> + + A user could execute this function to debit account 17 by $100.00 as + follows: + +</p><pre class="programlisting"> +SELECT tf1(17, 100.0); +</pre><p> + </p><p> + In this example, we chose the name <code class="literal">accountno</code> for the first + argument, but this is the same as the name of a column in the + <code class="literal">bank</code> table. Within the <code class="command">UPDATE</code> command, + <code class="literal">accountno</code> refers to the column <code class="literal">bank.accountno</code>, + so <code class="literal">tf1.accountno</code> must be used to refer to the argument. + We could of course avoid this by using a different name for the argument. + </p><p> + In practice one would probably like a more useful result from the + function than a constant 1, so a more likely definition + is: + +</p><pre class="programlisting"> +CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS numeric AS $$ + UPDATE bank + SET balance = balance - debit + WHERE accountno = tf1.accountno; + SELECT balance FROM bank WHERE accountno = tf1.accountno; +$$ LANGUAGE SQL; +</pre><p> + + which adjusts the balance and returns the new balance. + The same thing could be done in one command using <code class="literal">RETURNING</code>: + +</p><pre class="programlisting"> +CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS numeric AS $$ + UPDATE bank + SET balance = balance - debit + WHERE accountno = tf1.accountno + RETURNING balance; +$$ LANGUAGE SQL; +</pre><p> + </p><p> + If the final <code class="literal">SELECT</code> or <code class="literal">RETURNING</code> + clause in a <acronym class="acronym">SQL</acronym> function does not return exactly + the function's declared result + type, <span class="productname">PostgreSQL</span> will automatically cast + the value to the required type, if that is possible with an implicit + or assignment cast. Otherwise, you must write an explicit cast. + For example, suppose we wanted the + previous <code class="function">add_em</code> function to return + type <code class="type">float8</code> instead. It's sufficient to write + +</p><pre class="programlisting"> +CREATE FUNCTION add_em(integer, integer) RETURNS float8 AS $$ + SELECT $1 + $2; +$$ LANGUAGE SQL; +</pre><p> + + since the <code class="type">integer</code> sum can be implicitly cast + to <code class="type">float8</code>. + (See <a class="xref" href="typeconv.html" title="Chapter 10. Type Conversion">Chapter 10</a> or <a class="xref" href="sql-createcast.html" title="CREATE CAST"><span class="refentrytitle">CREATE CAST</span></a> + for more about casts.) + </p></div><div class="sect2" id="XFUNC-SQL-COMPOSITE-FUNCTIONS"><div class="titlepage"><div><div><h3 class="title">37.5.3. <acronym class="acronym">SQL</acronym> Functions on Composite Types</h3></div></div></div><p> + When writing functions with arguments of composite types, we must not + only specify which argument we want but also the desired attribute + (field) of that argument. For example, suppose that + <code class="type">emp</code> is a table containing employee data, and therefore + also the name of the composite type of each row of the table. Here + is a function <code class="function">double_salary</code> that computes what someone's + salary would be if it were doubled: + +</p><pre class="screen"> +CREATE TABLE emp ( + name text, + salary numeric, + age integer, + cubicle point +); + +INSERT INTO emp VALUES ('Bill', 4200, 45, '(2,1)'); + +CREATE FUNCTION double_salary(emp) RETURNS numeric AS $$ + SELECT $1.salary * 2 AS salary; +$$ LANGUAGE SQL; + +SELECT name, double_salary(emp.*) AS dream + FROM emp + WHERE emp.cubicle ~= point '(2,1)'; + + name | dream +------+------- + Bill | 8400 +</pre><p> + </p><p> + Notice the use of the syntax <code class="literal">$1.salary</code> + to select one field of the argument row value. Also notice + how the calling <code class="command">SELECT</code> command + uses <em class="replaceable"><code>table_name</code></em><code class="literal">.*</code> to select + the entire current row of a table as a composite value. The table + row can alternatively be referenced using just the table name, + like this: +</p><pre class="screen"> +SELECT name, double_salary(emp) AS dream + FROM emp + WHERE emp.cubicle ~= point '(2,1)'; +</pre><p> + but this usage is deprecated since it's easy to get confused. + (See <a class="xref" href="rowtypes.html#ROWTYPES-USAGE" title="8.16.5. Using Composite Types in Queries">Section 8.16.5</a> for details about these + two notations for the composite value of a table row.) + </p><p> + Sometimes it is handy to construct a composite argument value + on-the-fly. This can be done with the <code class="literal">ROW</code> construct. + For example, we could adjust the data being passed to the function: +</p><pre class="screen"> +SELECT name, double_salary(ROW(name, salary*1.1, age, cubicle)) AS dream + FROM emp; +</pre><p> + </p><p> + It is also possible to build a function that returns a composite type. + This is an example of a function + that returns a single <code class="type">emp</code> row: + +</p><pre class="programlisting"> +CREATE FUNCTION new_emp() RETURNS emp AS $$ + SELECT text 'None' AS name, + 1000.0 AS salary, + 25 AS age, + point '(2,2)' AS cubicle; +$$ LANGUAGE SQL; +</pre><p> + + In this example we have specified each of the attributes + with a constant value, but any computation + could have been substituted for these constants. + </p><p> + Note two important things about defining the function: + + </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p> + The select list order in the query must be exactly the same as + that in which the columns appear in the composite type. + (Naming the columns, as we did above, + is irrelevant to the system.) + </p></li><li class="listitem"><p> + We must ensure each expression's type can be cast to that of + the corresponding column of the composite type. + Otherwise we'll get errors like this: +</p><pre class="screen"> +<code class="computeroutput"> +ERROR: return type mismatch in function declared to return emp +DETAIL: Final statement returns text instead of point at column 4. +</code> +</pre><p> + As with the base-type case, the system will not insert explicit + casts automatically, only implicit or assignment casts. + </p></li></ul></div><p> + </p><p> + A different way to define the same function is: + +</p><pre class="programlisting"> +CREATE FUNCTION new_emp() RETURNS emp AS $$ + SELECT ROW('None', 1000.0, 25, '(2,2)')::emp; +$$ LANGUAGE SQL; +</pre><p> + + Here we wrote a <code class="command">SELECT</code> that returns just a single + column of the correct composite type. This isn't really better + in this situation, but it is a handy alternative in some cases + — for example, if we need to compute the result by calling + another function that returns the desired composite value. + Another example is that if we are trying to write a function that + returns a domain over composite, rather than a plain composite type, + it is always necessary to write it as returning a single column, + since there is no way to cause a coercion of the whole row result. + </p><p> + We could call this function directly either by using it in + a value expression: + +</p><pre class="screen"> +SELECT new_emp(); + + new_emp +-------------------------- + (None,1000.0,25,"(2,2)") +</pre><p> + + or by calling it as a table function: + +</p><pre class="screen"> +SELECT * FROM new_emp(); + + name | salary | age | cubicle +------+--------+-----+--------- + None | 1000.0 | 25 | (2,2) +</pre><p> + + The second way is described more fully in <a class="xref" href="xfunc-sql.html#XFUNC-SQL-TABLE-FUNCTIONS" title="37.5.7. SQL Functions as Table Sources">Section 37.5.7</a>. + </p><p> + When you use a function that returns a composite type, + you might want only one field (attribute) from its result. + You can do that with syntax like this: + +</p><pre class="screen"> +SELECT (new_emp()).name; + + name +------ + None +</pre><p> + + The extra parentheses are needed to keep the parser from getting + confused. If you try to do it without them, you get something like this: + +</p><pre class="screen"> +SELECT new_emp().name; +ERROR: syntax error at or near "." +LINE 1: SELECT new_emp().name; + ^ +</pre><p> + </p><p> + Another option is to use functional notation for extracting an attribute: + +</p><pre class="screen"> +SELECT name(new_emp()); + + name +------ + None +</pre><p> + + As explained in <a class="xref" href="rowtypes.html#ROWTYPES-USAGE" title="8.16.5. Using Composite Types in Queries">Section 8.16.5</a>, the field notation and + functional notation are equivalent. + </p><p> + Another way to use a function returning a composite type is to pass the + result to another function that accepts the correct row type as input: + +</p><pre class="screen"> +CREATE FUNCTION getname(emp) RETURNS text AS $$ + SELECT $1.name; +$$ LANGUAGE SQL; + +SELECT getname(new_emp()); + getname +--------- + None +(1 row) +</pre><p> + </p></div><div class="sect2" id="XFUNC-OUTPUT-PARAMETERS"><div class="titlepage"><div><div><h3 class="title">37.5.4. <acronym class="acronym">SQL</acronym> Functions with Output Parameters</h3></div></div></div><a id="id-1.8.3.8.12.2" class="indexterm"></a><p> + An alternative way of describing a function's results is to define it + with <em class="firstterm">output parameters</em>, as in this example: + +</p><pre class="screen"> +CREATE FUNCTION add_em (IN x int, IN y int, OUT sum int) +AS 'SELECT x + y' +LANGUAGE SQL; + +SELECT add_em(3,7); + add_em +-------- + 10 +(1 row) +</pre><p> + + This is not essentially different from the version of <code class="literal">add_em</code> + shown in <a class="xref" href="xfunc-sql.html#XFUNC-SQL-BASE-FUNCTIONS" title="37.5.2. SQL Functions on Base Types">Section 37.5.2</a>. The real value of + output parameters is that they provide a convenient way of defining + functions that return several columns. For example, + +</p><pre class="screen"> +CREATE FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product int) +AS 'SELECT x + y, x * y' +LANGUAGE SQL; + + SELECT * FROM sum_n_product(11,42); + sum | product +-----+--------- + 53 | 462 +(1 row) +</pre><p> + + What has essentially happened here is that we have created an anonymous + composite type for the result of the function. The above example has + the same end result as + +</p><pre class="screen"> +CREATE TYPE sum_prod AS (sum int, product int); + +CREATE FUNCTION sum_n_product (int, int) RETURNS sum_prod +AS 'SELECT $1 + $2, $1 * $2' +LANGUAGE SQL; +</pre><p> + + but not having to bother with the separate composite type definition + is often handy. Notice that the names attached to the output parameters + are not just decoration, but determine the column names of the anonymous + composite type. (If you omit a name for an output parameter, the + system will choose a name on its own.) + </p><p> + Notice that output parameters are not included in the calling argument + list when invoking such a function from SQL. This is because + <span class="productname">PostgreSQL</span> considers only the input + parameters to define the function's calling signature. That means + also that only the input parameters matter when referencing the function + for purposes such as dropping it. We could drop the above function + with either of + +</p><pre class="screen"> +DROP FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product int); +DROP FUNCTION sum_n_product (int, int); +</pre><p> + </p><p> + Parameters can be marked as <code class="literal">IN</code> (the default), + <code class="literal">OUT</code>, <code class="literal">INOUT</code>, or <code class="literal">VARIADIC</code>. + An <code class="literal">INOUT</code> + parameter serves as both an input parameter (part of the calling + argument list) and an output parameter (part of the result record type). + <code class="literal">VARIADIC</code> parameters are input parameters, but are treated + specially as described next. + </p></div><div class="sect2" id="XFUNC-SQL-VARIADIC-FUNCTIONS"><div class="titlepage"><div><div><h3 class="title">37.5.5. <acronym class="acronym">SQL</acronym> Functions with Variable Numbers of Arguments</h3></div></div></div><a id="id-1.8.3.8.13.2" class="indexterm"></a><a id="id-1.8.3.8.13.3" class="indexterm"></a><p> + <acronym class="acronym">SQL</acronym> functions can be declared to accept + variable numbers of arguments, so long as all the <span class="quote">“<span class="quote">optional</span>”</span> + arguments are of the same data type. The optional arguments will be + passed to the function as an array. The function is declared by + marking the last parameter as <code class="literal">VARIADIC</code>; this parameter + must be declared as being of an array type. For example: + +</p><pre class="screen"> +CREATE FUNCTION mleast(VARIADIC arr numeric[]) RETURNS numeric AS $$ + SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i); +$$ LANGUAGE SQL; + +SELECT mleast(10, -1, 5, 4.4); + mleast +-------- + -1 +(1 row) +</pre><p> + + Effectively, all the actual arguments at or beyond the + <code class="literal">VARIADIC</code> position are gathered up into a one-dimensional + array, as if you had written + +</p><pre class="screen"> +SELECT mleast(ARRAY[10, -1, 5, 4.4]); -- doesn't work +</pre><p> + + You can't actually write that, though — or at least, it will + not match this function definition. A parameter marked + <code class="literal">VARIADIC</code> matches one or more occurrences of its element + type, not of its own type. + </p><p> + Sometimes it is useful to be able to pass an already-constructed array + to a variadic function; this is particularly handy when one variadic + function wants to pass on its array parameter to another one. Also, + this is the only secure way to call a variadic function found in a schema + that permits untrusted users to create objects; see + <a class="xref" href="typeconv-func.html" title="10.3. Functions">Section 10.3</a>. You can do this by + specifying <code class="literal">VARIADIC</code> in the call: + +</p><pre class="screen"> +SELECT mleast(VARIADIC ARRAY[10, -1, 5, 4.4]); +</pre><p> + + This prevents expansion of the function's variadic parameter into its + element type, thereby allowing the array argument value to match + normally. <code class="literal">VARIADIC</code> can only be attached to the last + actual argument of a function call. + </p><p> + Specifying <code class="literal">VARIADIC</code> in the call is also the only way to + pass an empty array to a variadic function, for example: + +</p><pre class="screen"> +SELECT mleast(VARIADIC ARRAY[]::numeric[]); +</pre><p> + + Simply writing <code class="literal">SELECT mleast()</code> does not work because a + variadic parameter must match at least one actual argument. + (You could define a second function also named <code class="literal">mleast</code>, + with no parameters, if you wanted to allow such calls.) + </p><p> + The array element parameters generated from a variadic parameter are + treated as not having any names of their own. This means it is not + possible to call a variadic function using named arguments (<a class="xref" href="sql-syntax-calling-funcs.html" title="4.3. Calling Functions">Section 4.3</a>), except when you specify + <code class="literal">VARIADIC</code>. For example, this will work: + +</p><pre class="screen"> +SELECT mleast(VARIADIC arr => ARRAY[10, -1, 5, 4.4]); +</pre><p> + + but not these: + +</p><pre class="screen"> +SELECT mleast(arr => 10); +SELECT mleast(arr => ARRAY[10, -1, 5, 4.4]); +</pre><p> + </p></div><div class="sect2" id="XFUNC-SQL-PARAMETER-DEFAULTS"><div class="titlepage"><div><div><h3 class="title">37.5.6. <acronym class="acronym">SQL</acronym> Functions with Default Values for Arguments</h3></div></div></div><a id="id-1.8.3.8.14.2" class="indexterm"></a><p> + Functions can be declared with default values for some or all input + arguments. The default values are inserted whenever the function is + called with insufficiently many actual arguments. Since arguments + can only be omitted from the end of the actual argument list, all + parameters after a parameter with a default value have to have + default values as well. (Although the use of named argument notation + could allow this restriction to be relaxed, it's still enforced so that + positional argument notation works sensibly.) Whether or not you use it, + this capability creates a need for precautions when calling functions in + databases where some users mistrust other users; see + <a class="xref" href="typeconv-func.html" title="10.3. Functions">Section 10.3</a>. + </p><p> + For example: +</p><pre class="screen"> +CREATE FUNCTION foo(a int, b int DEFAULT 2, c int DEFAULT 3) +RETURNS int +LANGUAGE SQL +AS $$ + SELECT $1 + $2 + $3; +$$; + +SELECT foo(10, 20, 30); + foo +----- + 60 +(1 row) + +SELECT foo(10, 20); + foo +----- + 33 +(1 row) + +SELECT foo(10); + foo +----- + 15 +(1 row) + +SELECT foo(); -- fails since there is no default for the first argument +ERROR: function foo() does not exist +</pre><p> + The <code class="literal">=</code> sign can also be used in place of the + key word <code class="literal">DEFAULT</code>. + </p></div><div class="sect2" id="XFUNC-SQL-TABLE-FUNCTIONS"><div class="titlepage"><div><div><h3 class="title">37.5.7. <acronym class="acronym">SQL</acronym> Functions as Table Sources</h3></div></div></div><p> + All SQL functions can be used in the <code class="literal">FROM</code> clause of a query, + but it is particularly useful for functions returning composite types. + If the function is defined to return a base type, the table function + produces a one-column table. If the function is defined to return + a composite type, the table function produces a column for each attribute + of the composite type. + </p><p> + Here is an example: + +</p><pre class="screen"> +CREATE TABLE foo (fooid int, foosubid int, fooname text); +INSERT INTO foo VALUES (1, 1, 'Joe'); +INSERT INTO foo VALUES (1, 2, 'Ed'); +INSERT INTO foo VALUES (2, 1, 'Mary'); + +CREATE FUNCTION getfoo(int) RETURNS foo AS $$ + SELECT * FROM foo WHERE fooid = $1; +$$ LANGUAGE SQL; + +SELECT *, upper(fooname) FROM getfoo(1) AS t1; + + fooid | foosubid | fooname | upper +-------+----------+---------+------- + 1 | 1 | Joe | JOE +(1 row) +</pre><p> + + As the example shows, we can work with the columns of the function's + result just the same as if they were columns of a regular table. + </p><p> + Note that we only got one row out of the function. This is because + we did not use <code class="literal">SETOF</code>. That is described in the next section. + </p></div><div class="sect2" id="XFUNC-SQL-FUNCTIONS-RETURNING-SET"><div class="titlepage"><div><div><h3 class="title">37.5.8. <acronym class="acronym">SQL</acronym> Functions Returning Sets</h3></div></div></div><a id="id-1.8.3.8.16.2" class="indexterm"></a><p> + When an SQL function is declared as returning <code class="literal">SETOF + <em class="replaceable"><code>sometype</code></em></code>, the function's final + query is executed to completion, and each row it + outputs is returned as an element of the result set. + </p><p> + This feature is normally used when calling the function in the <code class="literal">FROM</code> + clause. In this case each row returned by the function becomes + a row of the table seen by the query. For example, assume that + table <code class="literal">foo</code> has the same contents as above, and we say: + +</p><pre class="programlisting"> +CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$ + SELECT * FROM foo WHERE fooid = $1; +$$ LANGUAGE SQL; + +SELECT * FROM getfoo(1) AS t1; +</pre><p> + + Then we would get: +</p><pre class="screen"> + fooid | foosubid | fooname +-------+----------+--------- + 1 | 1 | Joe + 1 | 2 | Ed +(2 rows) +</pre><p> + </p><p> + It is also possible to return multiple rows with the columns defined by + output parameters, like this: + +</p><pre class="programlisting"> +CREATE TABLE tab (y int, z int); +INSERT INTO tab VALUES (1, 2), (3, 4), (5, 6), (7, 8); + +CREATE FUNCTION sum_n_product_with_tab (x int, OUT sum int, OUT product int) +RETURNS SETOF record +AS $$ + SELECT $1 + tab.y, $1 * tab.y FROM tab; +$$ LANGUAGE SQL; + +SELECT * FROM sum_n_product_with_tab(10); + sum | product +-----+--------- + 11 | 10 + 13 | 30 + 15 | 50 + 17 | 70 +(4 rows) +</pre><p> + + The key point here is that you must write <code class="literal">RETURNS SETOF record</code> + to indicate that the function returns multiple rows instead of just one. + If there is only one output parameter, write that parameter's type + instead of <code class="type">record</code>. + </p><p> + It is frequently useful to construct a query's result by invoking a + set-returning function multiple times, with the parameters for each + invocation coming from successive rows of a table or subquery. The + preferred way to do this is to use the <code class="literal">LATERAL</code> key word, + which is described in <a class="xref" href="queries-table-expressions.html#QUERIES-LATERAL" title="7.2.1.5. LATERAL Subqueries">Section 7.2.1.5</a>. + Here is an example using a set-returning function to enumerate + elements of a tree structure: + +</p><pre class="screen"> +SELECT * FROM nodes; + name | parent +-----------+-------- + Top | + Child1 | Top + Child2 | Top + Child3 | Top + SubChild1 | Child1 + SubChild2 | Child1 +(6 rows) + +CREATE FUNCTION listchildren(text) RETURNS SETOF text AS $$ + SELECT name FROM nodes WHERE parent = $1 +$$ LANGUAGE SQL STABLE; + +SELECT * FROM listchildren('Top'); + listchildren +-------------- + Child1 + Child2 + Child3 +(3 rows) + +SELECT name, child FROM nodes, LATERAL listchildren(name) AS child; + name | child +--------+----------- + Top | Child1 + Top | Child2 + Top | Child3 + Child1 | SubChild1 + Child1 | SubChild2 +(5 rows) +</pre><p> + + This example does not do anything that we couldn't have done with a + simple join, but in more complex calculations the option to put + some of the work into a function can be quite convenient. + </p><p> + Functions returning sets can also be called in the select list + of a query. For each row that the query + generates by itself, the set-returning function is invoked, and an output + row is generated for each element of the function's result set. + The previous example could also be done with queries like + these: + +</p><pre class="screen"> +SELECT listchildren('Top'); + listchildren +-------------- + Child1 + Child2 + Child3 +(3 rows) + +SELECT name, listchildren(name) FROM nodes; + name | listchildren +--------+-------------- + Top | Child1 + Top | Child2 + Top | Child3 + Child1 | SubChild1 + Child1 | SubChild2 +(5 rows) +</pre><p> + + In the last <code class="command">SELECT</code>, + notice that no output row appears for <code class="literal">Child2</code>, <code class="literal">Child3</code>, etc. + This happens because <code class="function">listchildren</code> returns an empty set + for those arguments, so no result rows are generated. This is the same + behavior as we got from an inner join to the function result when using + the <code class="literal">LATERAL</code> syntax. + </p><p> + <span class="productname">PostgreSQL</span>'s behavior for a set-returning function in a + query's select list is almost exactly the same as if the set-returning + function had been written in a <code class="literal">LATERAL FROM</code>-clause item + instead. For example, +</p><pre class="programlisting"> +SELECT x, generate_series(1,5) AS g FROM tab; +</pre><p> + is almost equivalent to +</p><pre class="programlisting"> +SELECT x, g FROM tab, LATERAL generate_series(1,5) AS g; +</pre><p> + It would be exactly the same, except that in this specific example, + the planner could choose to put <code class="structname">g</code> on the outside of the + nested-loop join, since <code class="structname">g</code> has no actual lateral dependency + on <code class="structname">tab</code>. That would result in a different output row + order. Set-returning functions in the select list are always evaluated + as though they are on the inside of a nested-loop join with the rest of + the <code class="literal">FROM</code> clause, so that the function(s) are run to + completion before the next row from the <code class="literal">FROM</code> clause is + considered. + </p><p> + If there is more than one set-returning function in the query's select + list, the behavior is similar to what you get from putting the functions + into a single <code class="literal">LATERAL ROWS FROM( ... )</code> <code class="literal">FROM</code>-clause + item. For each row from the underlying query, there is an output row + using the first result from each function, then an output row using the + second result, and so on. If some of the set-returning functions + produce fewer outputs than others, null values are substituted for the + missing data, so that the total number of rows emitted for one + underlying row is the same as for the set-returning function that + produced the most outputs. Thus the set-returning functions + run <span class="quote">“<span class="quote">in lockstep</span>”</span> until they are all exhausted, and then + execution continues with the next underlying row. + </p><p> + Set-returning functions can be nested in a select list, although that is + not allowed in <code class="literal">FROM</code>-clause items. In such cases, each level + of nesting is treated separately, as though it were + a separate <code class="literal">LATERAL ROWS FROM( ... )</code> item. For example, in +</p><pre class="programlisting"> +SELECT srf1(srf2(x), srf3(y)), srf4(srf5(z)) FROM tab; +</pre><p> + the set-returning functions <code class="function">srf2</code>, <code class="function">srf3</code>, + and <code class="function">srf5</code> would be run in lockstep for each row + of <code class="structname">tab</code>, and then <code class="function">srf1</code> and <code class="function">srf4</code> + would be applied in lockstep to each row produced by the lower + functions. + </p><p> + Set-returning functions cannot be used within conditional-evaluation + constructs, such as <code class="literal">CASE</code> or <code class="literal">COALESCE</code>. For + example, consider +</p><pre class="programlisting"> +SELECT x, CASE WHEN x > 0 THEN generate_series(1, 5) ELSE 0 END FROM tab; +</pre><p> + It might seem that this should produce five repetitions of input rows + that have <code class="literal">x > 0</code>, and a single repetition of those that do + not; but actually, because <code class="function">generate_series(1, 5)</code> would be + run in an implicit <code class="literal">LATERAL FROM</code> item before + the <code class="literal">CASE</code> expression is ever evaluated, it would produce five + repetitions of every input row. To reduce confusion, such cases produce + a parse-time error instead. + </p><div class="note"><h3 class="title">Note</h3><p> + If a function's last command is <code class="command">INSERT</code>, <code class="command">UPDATE</code>, + or <code class="command">DELETE</code> with <code class="literal">RETURNING</code>, that command will + always be executed to completion, even if the function is not declared + with <code class="literal">SETOF</code> or the calling query does not fetch all the + result rows. Any extra rows produced by the <code class="literal">RETURNING</code> + clause are silently dropped, but the commanded table modifications + still happen (and are all completed before returning from the function). + </p></div><div class="note"><h3 class="title">Note</h3><p> + Before <span class="productname">PostgreSQL</span> 10, putting more than one + set-returning function in the same select list did not behave very + sensibly unless they always produced equal numbers of rows. Otherwise, + what you got was a number of output rows equal to the least common + multiple of the numbers of rows produced by the set-returning + functions. Also, nested set-returning functions did not work as + described above; instead, a set-returning function could have at most + one set-returning argument, and each nest of set-returning functions + was run independently. Also, conditional execution (set-returning + functions inside <code class="literal">CASE</code> etc) was previously allowed, + complicating things even more. + Use of the <code class="literal">LATERAL</code> syntax is recommended when writing + queries that need to work in older <span class="productname">PostgreSQL</span> versions, + because that will give consistent results across different versions. + If you have a query that is relying on conditional execution of a + set-returning function, you may be able to fix it by moving the + conditional test into a custom set-returning function. For example, +</p><pre class="programlisting"> +SELECT x, CASE WHEN y > 0 THEN generate_series(1, z) ELSE 5 END FROM tab; +</pre><p> + could become +</p><pre class="programlisting"> +CREATE FUNCTION case_generate_series(cond bool, start int, fin int, els int) + RETURNS SETOF int AS $$ +BEGIN + IF cond THEN + RETURN QUERY SELECT generate_series(start, fin); + ELSE + RETURN QUERY SELECT els; + END IF; +END$$ LANGUAGE plpgsql; + +SELECT x, case_generate_series(y > 0, 1, z, 5) FROM tab; +</pre><p> + This formulation will work the same in all versions + of <span class="productname">PostgreSQL</span>. + </p></div></div><div class="sect2" id="XFUNC-SQL-FUNCTIONS-RETURNING-TABLE"><div class="titlepage"><div><div><h3 class="title">37.5.9. <acronym class="acronym">SQL</acronym> Functions Returning <code class="literal">TABLE</code></h3></div></div></div><a id="id-1.8.3.8.17.2" class="indexterm"></a><p> + There is another way to declare a function as returning a set, + which is to use the syntax + <code class="literal">RETURNS TABLE(<em class="replaceable"><code>columns</code></em>)</code>. + This is equivalent to using one or more <code class="literal">OUT</code> parameters plus + marking the function as returning <code class="literal">SETOF record</code> (or + <code class="literal">SETOF</code> a single output parameter's type, as appropriate). + This notation is specified in recent versions of the SQL standard, and + thus may be more portable than using <code class="literal">SETOF</code>. + </p><p> + For example, the preceding sum-and-product example could also be + done this way: + +</p><pre class="programlisting"> +CREATE FUNCTION sum_n_product_with_tab (x int) +RETURNS TABLE(sum int, product int) AS $$ + SELECT $1 + tab.y, $1 * tab.y FROM tab; +$$ LANGUAGE SQL; +</pre><p> + + It is not allowed to use explicit <code class="literal">OUT</code> or <code class="literal">INOUT</code> + parameters with the <code class="literal">RETURNS TABLE</code> notation — you must + put all the output columns in the <code class="literal">TABLE</code> list. + </p></div><div class="sect2" id="XFUNC-SQL-POLYMORPHIC-FUNCTIONS"><div class="titlepage"><div><div><h3 class="title">37.5.10. Polymorphic <acronym class="acronym">SQL</acronym> Functions</h3></div></div></div><p> + <acronym class="acronym">SQL</acronym> functions can be declared to accept and + return the polymorphic types described in <a class="xref" href="extend-type-system.html#EXTEND-TYPES-POLYMORPHIC" title="37.2.5. Polymorphic Types">Section 37.2.5</a>. Here is a polymorphic + function <code class="function">make_array</code> that builds up an array + from two arbitrary data type elements: +</p><pre class="screen"> +CREATE FUNCTION make_array(anyelement, anyelement) RETURNS anyarray AS $$ + SELECT ARRAY[$1, $2]; +$$ LANGUAGE SQL; + +SELECT make_array(1, 2) AS intarray, make_array('a'::text, 'b') AS textarray; + intarray | textarray +----------+----------- + {1,2} | {a,b} +(1 row) +</pre><p> + </p><p> + Notice the use of the typecast <code class="literal">'a'::text</code> + to specify that the argument is of type <code class="type">text</code>. This is + required if the argument is just a string literal, since otherwise + it would be treated as type + <code class="type">unknown</code>, and array of <code class="type">unknown</code> is not a valid + type. + Without the typecast, you will get errors like this: +</p><pre class="screen"> +ERROR: could not determine polymorphic type because input has type unknown +</pre><p> + </p><p> + With <code class="function">make_array</code> declared as above, you must + provide two arguments that are of exactly the same data type; the + system will not attempt to resolve any type differences. Thus for + example this does not work: +</p><pre class="screen"> +SELECT make_array(1, 2.5) AS numericarray; +ERROR: function make_array(integer, numeric) does not exist +</pre><p> + An alternative approach is to use the <span class="quote">“<span class="quote">common</span>”</span> family of + polymorphic types, which allows the system to try to identify a + suitable common type: +</p><pre class="screen"> +CREATE FUNCTION make_array2(anycompatible, anycompatible) +RETURNS anycompatiblearray AS $$ + SELECT ARRAY[$1, $2]; +$$ LANGUAGE SQL; + +SELECT make_array2(1, 2.5) AS numericarray; + numericarray +-------------- + {1,2.5} +(1 row) +</pre><p> + Because the rules for common type resolution default to choosing + type <code class="type">text</code> when all inputs are of unknown types, this + also works: +</p><pre class="screen"> +SELECT make_array2('a', 'b') AS textarray; + textarray +----------- + {a,b} +(1 row) +</pre><p> + </p><p> + It is permitted to have polymorphic arguments with a fixed + return type, but the converse is not. For example: +</p><pre class="screen"> +CREATE FUNCTION is_greater(anyelement, anyelement) RETURNS boolean AS $$ + SELECT $1 > $2; +$$ LANGUAGE SQL; + +SELECT is_greater(1, 2); + is_greater +------------ + f +(1 row) + +CREATE FUNCTION invalid_func() RETURNS anyelement AS $$ + SELECT 1; +$$ LANGUAGE SQL; +ERROR: cannot determine result data type +DETAIL: A result of type anyelement requires at least one input of type anyelement, anyarray, anynonarray, anyenum, or anyrange. +</pre><p> + </p><p> + Polymorphism can be used with functions that have output arguments. + For example: +</p><pre class="screen"> +CREATE FUNCTION dup (f1 anyelement, OUT f2 anyelement, OUT f3 anyarray) +AS 'select $1, array[$1,$1]' LANGUAGE SQL; + +SELECT * FROM dup(22); + f2 | f3 +----+--------- + 22 | {22,22} +(1 row) +</pre><p> + </p><p> + Polymorphism can also be used with variadic functions. + For example: +</p><pre class="screen"> +CREATE FUNCTION anyleast (VARIADIC anyarray) RETURNS anyelement AS $$ + SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i); +$$ LANGUAGE SQL; + +SELECT anyleast(10, -1, 5, 4); + anyleast +---------- + -1 +(1 row) + +SELECT anyleast('abc'::text, 'def'); + anyleast +---------- + abc +(1 row) + +CREATE FUNCTION concat_values(text, VARIADIC anyarray) RETURNS text AS $$ + SELECT array_to_string($2, $1); +$$ LANGUAGE SQL; + +SELECT concat_values('|', 1, 4, 2); + concat_values +--------------- + 1|4|2 +(1 row) +</pre><p> + </p></div><div class="sect2" id="id-1.8.3.8.19"><div class="titlepage"><div><div><h3 class="title">37.5.11. <acronym class="acronym">SQL</acronym> Functions with Collations</h3></div></div></div><a id="id-1.8.3.8.19.2" class="indexterm"></a><p> + When a SQL function has one or more parameters of collatable data types, + a collation is identified for each function call depending on the + collations assigned to the actual arguments, as described in <a class="xref" href="collation.html" title="23.2. Collation Support">Section 23.2</a>. If a collation is successfully identified + (i.e., there are no conflicts of implicit collations among the arguments) + then all the collatable parameters are treated as having that collation + implicitly. This will affect the behavior of collation-sensitive + operations within the function. For example, using the + <code class="function">anyleast</code> function described above, the result of +</p><pre class="programlisting"> +SELECT anyleast('abc'::text, 'ABC'); +</pre><p> + will depend on the database's default collation. In <code class="literal">C</code> locale + the result will be <code class="literal">ABC</code>, but in many other locales it will + be <code class="literal">abc</code>. The collation to use can be forced by adding + a <code class="literal">COLLATE</code> clause to any of the arguments, for example +</p><pre class="programlisting"> +SELECT anyleast('abc'::text, 'ABC' COLLATE "C"); +</pre><p> + Alternatively, if you wish a function to operate with a particular + collation regardless of what it is called with, insert + <code class="literal">COLLATE</code> clauses as needed in the function definition. + This version of <code class="function">anyleast</code> would always use <code class="literal">en_US</code> + locale to compare strings: +</p><pre class="programlisting"> +CREATE FUNCTION anyleast (VARIADIC anyarray) RETURNS anyelement AS $$ + SELECT min($1[i] COLLATE "en_US") FROM generate_subscripts($1, 1) g(i); +$$ LANGUAGE SQL; +</pre><p> + But note that this will throw an error if applied to a non-collatable + data type. + </p><p> + If no common collation can be identified among the actual arguments, + then a SQL function treats its parameters as having their data types' + default collation (which is usually the database's default collation, + but could be different for parameters of domain types). + </p><p> + The behavior of collatable parameters can be thought of as a limited + form of polymorphism, applicable only to textual data types. + </p></div></div><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navfooter"><hr></hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="xproc.html" title="37.4. User-Defined Procedures">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="extend.html" title="Chapter 37. Extending SQL">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="xfunc-overload.html" title="37.6. Function Overloading">Next</a></td></tr><tr><td width="40%" align="left" valign="top">37.4. User-Defined Procedures </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 13.4 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 37.6. Function Overloading</td></tr></table></div></body></html>
\ No newline at end of file |