summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/html/xfunc-sql.html
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 12:17:33 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 12:17:33 +0000
commit5e45211a64149b3c659b90ff2de6fa982a5a93ed (patch)
tree739caf8c461053357daa9f162bef34516c7bf452 /doc/src/sgml/html/xfunc-sql.html
parentInitial commit. (diff)
downloadpostgresql-15-5e45211a64149b3c659b90ff2de6fa982a5a93ed.tar.xz
postgresql-15-5e45211a64149b3c659b90ff2de6fa982a5a93ed.zip
Adding upstream version 15.5.upstream/15.5
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'doc/src/sgml/html/xfunc-sql.html')
-rw-r--r--doc/src/sgml/html/xfunc-sql.html1123
1 files changed, 1123 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..d63b57b
--- /dev/null
+++ b/doc/src/sgml/html/xfunc-sql.html
@@ -0,0 +1,1123 @@
+<?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>38.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 Vsnapshot" /><link rel="prev" href="xproc.html" title="38.4. User-Defined Procedures" /><link rel="next" href="xfunc-overload.html" title="38.6. Function Overloading" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">38.5. Query Language (<acronym class="acronym">SQL</acronym>) Functions</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="xproc.html" title="38.4. User-Defined Procedures">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="extend.html" title="Chapter 38. Extending SQL">Up</a></td><th width="60%" align="center">Chapter 38. Extending <acronym class="acronym">SQL</acronym></th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="xfunc-overload.html" title="38.6. Function Overloading">Next</a></td></tr></table><hr /></div><div class="sect1" id="XFUNC-SQL"><div class="titlepage"><div><div><h2 class="title" style="clear: both">38.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">38.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">38.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">38.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">38.5.4. <acronym class="acronym">SQL</acronym> Functions with Output Parameters</a></span></dt><dt><span class="sect2"><a href="xfunc-sql.html#XFUNC-OUTPUT-PARAMETERS-PROC">38.5.5. <acronym class="acronym">SQL</acronym> Procedures with Output Parameters</a></span></dt><dt><span class="sect2"><a href="xfunc-sql.html#XFUNC-SQL-VARIADIC-FUNCTIONS">38.5.6. <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">38.5.7. <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">38.5.8. <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">38.5.9. <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">38.5.10. <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">38.5.11. Polymorphic <acronym class="acronym">SQL</acronym> Functions</a></span></dt><dt><span class="sect2"><a href="xfunc-sql.html#id-1.8.3.8.21">38.5.12. <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>, <code class="command">DELETE</code>, and
+ <code class="command">MERGE</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 an 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 &lt; 0;
+' LANGUAGE SQL;
+
+SELECT clean_emp();
+
+ clean_emp
+-----------
+
+(1 row)
+</pre><p>
+ </p><p>
+ You can also write this as a procedure, thus avoiding the issue of the
+ return type. For example:
+</p><pre class="screen">
+CREATE PROCEDURE clean_emp() AS '
+ DELETE FROM emp
+ WHERE salary &lt; 0;
+' LANGUAGE SQL;
+
+CALL clean_emp();
+</pre><p>
+ In simple cases like this, the difference between a function returning
+ <code class="type">void</code> and a procedure is mostly stylistic. However,
+ procedures offer additional functionality such as transaction control
+ that is not available in functions. Also, procedures are SQL standard
+ whereas returning <code class="type">void</code> is a PostgreSQL extension.
+ </p><div class="note"><h3 class="title">Note</h3><p>
+ The entire body of an SQL function is parsed before any of it is
+ executed. While an 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 an 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">38.5.1. Arguments for <acronym class="acronym">SQL</acronym> Functions</h3></div></div></div><a id="id-1.8.3.8.10.2" class="indexterm"></a><p>
+ Arguments of an 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">38.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 an <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">38.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="38.5.8. SQL Functions as Table Sources">Section 38.5.8</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">38.5.4. <acronym class="acronym">SQL</acronym> Functions with Output Parameters</h3></div></div></div><a id="id-1.8.3.8.13.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="38.5.2. SQL Functions on Base Types">Section 38.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 below.
+ </p></div><div class="sect2" id="XFUNC-OUTPUT-PARAMETERS-PROC"><div class="titlepage"><div><div><h3 class="title">38.5.5. <acronym class="acronym">SQL</acronym> Procedures with Output Parameters</h3></div></div></div><a id="id-1.8.3.8.14.2" class="indexterm"></a><p>
+ Output parameters are also supported in procedures, but they work a bit
+ differently from functions. In <code class="command">CALL</code> commands,
+ output parameters must be included in the argument list.
+ For example, the bank account debiting routine from earlier could be
+ written like this:
+</p><pre class="programlisting">
+CREATE PROCEDURE tp1 (accountno integer, debit numeric, OUT new_balance numeric) AS $$
+ UPDATE bank
+ SET balance = balance - debit
+ WHERE accountno = tp1.accountno
+ RETURNING balance;
+$$ LANGUAGE SQL;
+</pre><p>
+ To call this procedure, an argument matching the <code class="literal">OUT</code>
+ parameter must be included. It's customary to write
+ <code class="literal">NULL</code>:
+</p><pre class="programlisting">
+CALL tp1(17, 100.0, NULL);
+</pre><p>
+ If you write something else, it must be an expression that is implicitly
+ coercible to the declared type of the parameter, just as for input
+ parameters. Note however that such an expression will not be evaluated.
+ </p><p>
+ When calling a procedure from <span class="application">PL/pgSQL</span>,
+ instead of writing <code class="literal">NULL</code> you must write a variable
+ that will receive the procedure's output. See <a class="xref" href="plpgsql-control-structures.html#PLPGSQL-STATEMENTS-CALLING-PROCEDURE" title="43.6.3. Calling a Procedure">Section 43.6.3</a> for details.
+ </p></div><div class="sect2" id="XFUNC-SQL-VARIADIC-FUNCTIONS"><div class="titlepage"><div><div><h3 class="title">38.5.6. <acronym class="acronym">SQL</acronym> Functions with Variable Numbers of Arguments</h3></div></div></div><a id="id-1.8.3.8.15.2" class="indexterm"></a><a id="id-1.8.3.8.15.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 =&gt; ARRAY[10, -1, 5, 4.4]);
+</pre><p>
+
+ but not these:
+
+</p><pre class="screen">
+SELECT mleast(arr =&gt; 10);
+SELECT mleast(arr =&gt; 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">38.5.7. <acronym class="acronym">SQL</acronym> Functions with Default Values for Arguments</h3></div></div></div><a id="id-1.8.3.8.16.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">38.5.8. <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">38.5.9. <acronym class="acronym">SQL</acronym> Functions Returning Sets</h3></div></div></div><a id="id-1.8.3.8.18.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 &gt; 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 &gt; 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 &gt; 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 &gt; 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">38.5.10. <acronym class="acronym">SQL</acronym> Functions Returning <code class="literal">TABLE</code></h3></div></div></div><a id="id-1.8.3.8.19.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">38.5.11. 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="38.2.5. Polymorphic Types">Section 38.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 &gt; $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.21"><div class="titlepage"><div><div><h3 class="title">38.5.12. <acronym class="acronym">SQL</acronym> Functions with Collations</h3></div></div></div><a id="id-1.8.3.8.21.2" class="indexterm"></a><p>
+ When an 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="24.2. Collation Support">Section 24.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 an 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 class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="xproc.html" title="38.4. User-Defined Procedures">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="extend.html" title="Chapter 38. Extending SQL">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="xfunc-overload.html" title="38.6. Function Overloading">Next</a></td></tr><tr><td width="40%" align="left" valign="top">38.4. User-Defined Procedures </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 38.6. Function Overloading</td></tr></table></div></body></html> \ No newline at end of file