begriffs open source - ai-pg/blob - full-docs/src/sgml/html/xfunc-sql.html

   1 <?xml version="1.0" encoding="UTF-8" standalone="no"?>
   2 <!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>36.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="36.4. User-Defined Procedures" /><link rel="next" href="xfunc-overload.html" title="36.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">36.5. Query Language (<acronym class="acronym">SQL</acronym>) Functions</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="xproc.html" title="36.4. User-Defined Procedures">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="extend.html" title="Chapter 36. Extending SQL">Up</a></td><th width="60%" align="center">Chapter 36. Extending <acronym class="acronym">SQL</acronym></th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="xfunc-overload.html" title="36.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">36.5. Query Language (<acronym class="acronym">SQL</acronym>) Functions <a href="#XFUNC-SQL" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="xfunc-sql.html#XFUNC-SQL-FUNCTION-ARGUMENTS">36.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">36.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">36.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">36.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">36.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">36.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">36.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">36.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">36.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">36.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">36.5.11. Polymorphic <acronym class="acronym">SQL</acronym> Functions</a></span></dt><dt><span class="sect2"><a href="xfunc-sql.html#XFUNC-SQL-COLLATIONS">36.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>
   3     SQL functions execute an arbitrary list of SQL statements, returning
   4     the result of the last query in the list.
   5     In the simple (non-set)
   6     case, the first row of the last query's result will be returned.
   7     (Bear in mind that <span class="quote">“<span class="quote">the first row</span>”</span> of a multirow
   8     result is not well-defined unless you use <code class="literal">ORDER BY</code>.)
   9     If the last query happens
  10     to return no rows at all, the null value will be returned.
  11    </p><p>
  12     Alternatively, an SQL function can be declared to return a set (that is,
  13     multiple rows) by specifying the function's return type as <code class="literal">SETOF
  14     <em class="replaceable"><code>sometype</code></em></code>, or equivalently by declaring it as
  15     <code class="literal">RETURNS TABLE(<em class="replaceable"><code>columns</code></em>)</code>.  In this case
  16     all rows of the last query's result are returned.  Further details appear
  17     below.
  18    </p><p>
  19     The body of an SQL function must be a list of SQL
  20     statements separated by semicolons.  A semicolon after the last
  21     statement is optional.  Unless the function is declared to return
  22     <code class="type">void</code>, the last statement must be a <code class="command">SELECT</code>,
  23     or an <code class="command">INSERT</code>, <code class="command">UPDATE</code>,
  24     <code class="command">DELETE</code>, or <code class="command">MERGE</code>
  25     that has a <code class="literal">RETURNING</code> clause.
  26    </p><p>
  27      Any collection of commands in the  <acronym class="acronym">SQL</acronym>
  28      language can be packaged together and defined as a function.
  29      Besides <code class="command">SELECT</code> queries, the commands can include data
  30      modification queries (<code class="command">INSERT</code>,
  31      <code class="command">UPDATE</code>, <code class="command">DELETE</code>, and
  32      <code class="command">MERGE</code>), as well as
  33      other SQL commands. (You cannot use transaction control commands, e.g.,
  34      <code class="command">COMMIT</code>, <code class="command">SAVEPOINT</code>, and some utility
  35      commands, e.g.,  <code class="literal">VACUUM</code>, in <acronym class="acronym">SQL</acronym> functions.)
  36      However, the final command
  37      must be a <code class="command">SELECT</code> or have a <code class="literal">RETURNING</code>
  38      clause that returns whatever is
  39      specified as the function's return type.  Alternatively, if you
  40      want to define an SQL function that performs actions but has no
  41      useful value to return, you can define it as returning <code class="type">void</code>.
  42      For example, this function removes rows with negative salaries from
  43      the <code class="literal">emp</code> table:
  44
  45 </p><pre class="screen">
  46 CREATE FUNCTION clean_emp() RETURNS void AS '
  47     DELETE FROM emp
  48         WHERE salary &lt; 0;
  49 ' LANGUAGE SQL;
  50
  51 SELECT clean_emp();
  52
  53  clean_emp
  54 -----------
  55
  56 (1 row)
  57 </pre><p>
  58     </p><p>
  59      You can also write this as a procedure, thus avoiding the issue of the
  60      return type.  For example:
  61 </p><pre class="screen">
  62 CREATE PROCEDURE clean_emp() AS '
  63     DELETE FROM emp
  64         WHERE salary &lt; 0;
  65 ' LANGUAGE SQL;
  66
  67 CALL clean_emp();
  68 </pre><p>
  69      In simple cases like this, the difference between a function returning
  70      <code class="type">void</code> and a procedure is mostly stylistic.  However,
  71      procedures offer additional functionality such as transaction control
  72      that is not available in functions.  Also, procedures are SQL standard
  73      whereas returning <code class="type">void</code> is a PostgreSQL extension.
  74     </p><p>
  75     The syntax of the <code class="command">CREATE FUNCTION</code> command requires
  76     the function body to be written as a string constant.  It is usually
  77     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.
  78     If you choose to use regular single-quoted string constant syntax,
  79     you must double single quote marks (<code class="literal">'</code>) and backslashes
  80     (<code class="literal">\</code>) (assuming escape string syntax) in the body of
  81     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>).
  82    </p><div class="sect2" id="XFUNC-SQL-FUNCTION-ARGUMENTS"><div class="titlepage"><div><div><h3 class="title">36.5.1. Arguments for <acronym class="acronym">SQL</acronym> Functions <a href="#XFUNC-SQL-FUNCTION-ARGUMENTS" class="id_link">#</a></h3></div></div></div><a id="id-1.8.3.8.9.2" class="indexterm"></a><p>
  83      Arguments of an SQL function can be referenced in the function
  84      body using either names or numbers.  Examples of both methods appear
  85      below.
  86     </p><p>
  87      To use a name, declare the function argument as having a name, and
  88      then just write that name in the function body.  If the argument name
  89      is the same as any column name in the current SQL command within the
  90      function, the column name will take precedence.  To override this,
  91      qualify the argument name with the name of the function itself, that is
  92      <code class="literal"><em class="replaceable"><code>function_name</code></em>.<em class="replaceable"><code>argument_name</code></em></code>.
  93      (If this would conflict with a qualified column name, again the column
  94      name wins.  You can avoid the ambiguity by choosing a different alias for
  95      the table within the SQL command.)
  96     </p><p>
  97      In the older numeric approach, arguments are referenced using the syntax
  98      <code class="literal">$<em class="replaceable"><code>n</code></em></code>: <code class="literal">$1</code> refers to the first input
  99      argument, <code class="literal">$2</code> to the second, and so on.  This will work
 100      whether or not the particular argument was declared with a name.
 101     </p><p>
 102      If an argument is of a composite type, then the dot notation,
 103      e.g., <code class="literal"><em class="replaceable"><code>argname</code></em>.<em class="replaceable"><code>fieldname</code></em></code> or
 104      <code class="literal">$1.<em class="replaceable"><code>fieldname</code></em></code>, can be used to access attributes of the
 105      argument.  Again, you might need to qualify the argument's name with the
 106      function name to make the form with an argument name unambiguous.
 107     </p><p>
 108      SQL function arguments can only be used as data values,
 109      not as identifiers.  Thus for example this is reasonable:
 110 </p><pre class="programlisting">
 111 INSERT INTO mytable VALUES ($1);
 112 </pre><p>
 113 but this will not work:
 114 </p><pre class="programlisting">
 115 INSERT INTO $1 VALUES (42);
 116 </pre><p>
 117     </p><div class="note"><h3 class="title">Note</h3><p>
 118       The ability to use names to reference SQL function arguments was added
 119       in <span class="productname">PostgreSQL</span> 9.2.  Functions to be used in
 120       older servers must use the <code class="literal">$<em class="replaceable"><code>n</code></em></code> notation.
 121      </p></div></div><div class="sect2" id="XFUNC-SQL-BASE-FUNCTIONS"><div class="titlepage"><div><div><h3 class="title">36.5.2. <acronym class="acronym">SQL</acronym> Functions on Base Types <a href="#XFUNC-SQL-BASE-FUNCTIONS" class="id_link">#</a></h3></div></div></div><p>
 122      The simplest possible <acronym class="acronym">SQL</acronym> function has no arguments and
 123      simply returns a base type, such as <code class="type">integer</code>:
 124
 125 </p><pre class="screen">
 126 CREATE FUNCTION one() RETURNS integer AS $$
 127     SELECT 1 AS result;
 128 $$ LANGUAGE SQL;
 129
 130 -- Alternative syntax for string literal:
 131 CREATE FUNCTION one() RETURNS integer AS '
 132     SELECT 1 AS result;
 133 ' LANGUAGE SQL;
 134
 135 SELECT one();
 136
 137  one
 138 -----
 139    1
 140 </pre><p>
 141     </p><p>
 142      Notice that we defined a column alias within the function body for the result of the function
 143      (with  the  name <code class="literal">result</code>),  but this column alias is not visible
 144      outside the function.  Hence,  the  result  is labeled <code class="literal">one</code>
 145      instead of <code class="literal">result</code>.
 146     </p><p>
 147      It is almost as easy to define <acronym class="acronym">SQL</acronym> functions
 148      that take base types as arguments:
 149
 150 </p><pre class="screen">
 151 CREATE FUNCTION add_em(x integer, y integer) RETURNS integer AS $$
 152     SELECT x + y;
 153 $$ LANGUAGE SQL;
 154
 155 SELECT add_em(1, 2) AS answer;
 156
 157  answer
 158 --------
 159       3
 160 </pre><p>
 161     </p><p>
 162      Alternatively, we could dispense with names for the arguments and
 163      use numbers:
 164
 165 </p><pre class="screen">
 166 CREATE FUNCTION add_em(integer, integer) RETURNS integer AS $$
 167     SELECT $1 + $2;
 168 $$ LANGUAGE SQL;
 169
 170 SELECT add_em(1, 2) AS answer;
 171
 172  answer
 173 --------
 174       3
 175 </pre><p>
 176     </p><p>
 177      Here is a more useful function, which might be used to debit a
 178      bank account:
 179
 180 </p><pre class="programlisting">
 181 CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS numeric AS $$
 182     UPDATE bank
 183         SET balance = balance - debit
 184         WHERE accountno = tf1.accountno;
 185     SELECT 1;
 186 $$ LANGUAGE SQL;
 187 </pre><p>
 188
 189      A user could execute this function to debit account 17 by $100.00 as
 190      follows:
 191
 192 </p><pre class="programlisting">
 193 SELECT tf1(17, 100.0);
 194 </pre><p>
 195     </p><p>
 196      In this example, we chose the name <code class="literal">accountno</code> for the first
 197      argument, but this is the same as the name of a column in the
 198      <code class="literal">bank</code> table.  Within the <code class="command">UPDATE</code> command,
 199      <code class="literal">accountno</code> refers to the column <code class="literal">bank.accountno</code>,
 200      so <code class="literal">tf1.accountno</code> must be used to refer to the argument.
 201      We could of course avoid this by using a different name for the argument.
 202     </p><p>
 203      In practice one would probably like a more useful result from the
 204      function than a constant 1, so a more likely definition
 205      is:
 206
 207 </p><pre class="programlisting">
 208 CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS numeric AS $$
 209     UPDATE bank
 210         SET balance = balance - debit
 211         WHERE accountno = tf1.accountno;
 212     SELECT balance FROM bank WHERE accountno = tf1.accountno;
 213 $$ LANGUAGE SQL;
 214 </pre><p>
 215
 216      which adjusts the balance and returns the new balance.
 217      The same thing could be done in one command using <code class="literal">RETURNING</code>:
 218
 219 </p><pre class="programlisting">
 220 CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS numeric AS $$
 221     UPDATE bank
 222         SET balance = balance - debit
 223         WHERE accountno = tf1.accountno
 224     RETURNING balance;
 225 $$ LANGUAGE SQL;
 226 </pre><p>
 227     </p><p>
 228      If the final <code class="literal">SELECT</code> or <code class="literal">RETURNING</code>
 229      clause in an <acronym class="acronym">SQL</acronym> function does not return exactly
 230      the function's declared result
 231      type, <span class="productname">PostgreSQL</span> will automatically cast
 232      the value to the required type, if that is possible with an implicit
 233      or assignment cast.  Otherwise, you must write an explicit cast.
 234      For example, suppose we wanted the
 235      previous <code class="function">add_em</code> function to return
 236      type <code class="type">float8</code> instead.  It's sufficient to write
 237
 238 </p><pre class="programlisting">
 239 CREATE FUNCTION add_em(integer, integer) RETURNS float8 AS $$
 240     SELECT $1 + $2;
 241 $$ LANGUAGE SQL;
 242 </pre><p>
 243
 244      since the <code class="type">integer</code> sum can be implicitly cast
 245      to <code class="type">float8</code>.
 246      (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>
 247      for more about casts.)
 248     </p></div><div class="sect2" id="XFUNC-SQL-COMPOSITE-FUNCTIONS"><div class="titlepage"><div><div><h3 class="title">36.5.3. <acronym class="acronym">SQL</acronym> Functions on Composite Types <a href="#XFUNC-SQL-COMPOSITE-FUNCTIONS" class="id_link">#</a></h3></div></div></div><p>
 249      When writing functions with arguments of composite types, we must not
 250      only specify which argument we want but also the desired attribute
 251      (field) of that argument.  For example, suppose that
 252      <code class="type">emp</code> is a table containing employee data, and therefore
 253      also the name of the composite type of each row of the table.  Here
 254      is a function <code class="function">double_salary</code> that computes what someone's
 255      salary would be if it were doubled:
 256
 257 </p><pre class="screen">
 258 CREATE TABLE emp (
 259     name        text,
 260     salary      numeric,
 261     age         integer,
 262     cubicle     point
 263 );
 264
 265 INSERT INTO emp VALUES ('Bill', 4200, 45, '(2,1)');
 266
 267 CREATE FUNCTION double_salary(emp) RETURNS numeric AS $$
 268     SELECT $1.salary * 2 AS salary;
 269 $$ LANGUAGE SQL;
 270
 271 SELECT name, double_salary(emp.*) AS dream
 272     FROM emp
 273     WHERE emp.cubicle ~= point '(2,1)';
 274
 275  name | dream
 276 ------+-------
 277  Bill |  8400
 278 </pre><p>
 279     </p><p>
 280      Notice the use of the syntax <code class="literal">$1.salary</code>
 281      to select one field of the argument row value.  Also notice
 282      how the calling <code class="command">SELECT</code> command
 283      uses <em class="replaceable"><code>table_name</code></em><code class="literal">.*</code> to select
 284      the entire current row of a table as a composite value.  The table
 285      row can alternatively be referenced using just the table name,
 286      like this:
 287 </p><pre class="screen">
 288 SELECT name, double_salary(emp) AS dream
 289     FROM emp
 290     WHERE emp.cubicle ~= point '(2,1)';
 291 </pre><p>
 292      but this usage is deprecated since it's easy to get confused.
 293      (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
 294      two notations for the composite value of a table row.)
 295     </p><p>
 296      Sometimes it is handy to construct a composite argument value
 297      on-the-fly.  This can be done with the <code class="literal">ROW</code> construct.
 298      For example, we could adjust the data being passed to the function:
 299 </p><pre class="screen">
 300 SELECT name, double_salary(ROW(name, salary*1.1, age, cubicle)) AS dream
 301     FROM emp;
 302 </pre><p>
 303     </p><p>
 304      It is also possible to build a function that returns a composite type.
 305      This is an example of a function
 306      that returns a single <code class="type">emp</code> row:
 307
 308 </p><pre class="programlisting">
 309 CREATE FUNCTION new_emp() RETURNS emp AS $$
 310     SELECT text 'None' AS name,
 311         1000.0 AS salary,
 312         25 AS age,
 313         point '(2,2)' AS cubicle;
 314 $$ LANGUAGE SQL;
 315 </pre><p>
 316
 317      In this example we have specified each of  the  attributes
 318      with  a  constant value, but any computation
 319      could have been substituted for these constants.
 320     </p><p>
 321      Note two important things about defining the function:
 322
 323      </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
 324         The select list order in the query must be exactly the same as
 325         that in which the columns appear in the composite type.
 326         (Naming the columns, as we did above,
 327         is irrelevant to the system.)
 328        </p></li><li class="listitem"><p>
 329         We must ensure each expression's type can be cast to that of
 330         the corresponding column of the composite type.
 331         Otherwise we'll get errors like this:
 332 </p><pre class="screen">
 333 <code class="computeroutput">
 334 ERROR:  return type mismatch in function declared to return emp
 335 DETAIL:  Final statement returns text instead of point at column 4.
 336 </code>
 337 </pre><p>
 338         As with the base-type case, the system will not insert explicit
 339         casts automatically, only implicit or assignment casts.
 340        </p></li></ul></div><p>
 341     </p><p>
 342      A different way to define the same function is:
 343
 344 </p><pre class="programlisting">
 345 CREATE FUNCTION new_emp() RETURNS emp AS $$
 346     SELECT ROW('None', 1000.0, 25, '(2,2)')::emp;
 347 $$ LANGUAGE SQL;
 348 </pre><p>
 349
 350      Here we wrote a <code class="command">SELECT</code> that returns just a single
 351      column of the correct composite type.  This isn't really better
 352      in this situation, but it is a handy alternative in some cases
 353      — for example, if we need to compute the result by calling
 354      another function that returns the desired composite value.
 355      Another example is that if we are trying to write a function that
 356      returns a domain over composite, rather than a plain composite type,
 357      it is always necessary to write it as returning a single column,
 358      since there is no way to cause a coercion of the whole row result.
 359     </p><p>
 360      We could call this function directly either by using it in
 361      a value expression:
 362
 363 </p><pre class="screen">
 364 SELECT new_emp();
 365
 366          new_emp
 367 --------------------------
 368  (None,1000.0,25,"(2,2)")
 369 </pre><p>
 370
 371      or by calling it as a table function:
 372
 373 </p><pre class="screen">
 374 SELECT * FROM new_emp();
 375
 376  name | salary | age | cubicle
 377 ------+--------+-----+---------
 378  None | 1000.0 |  25 | (2,2)
 379 </pre><p>
 380
 381      The second way is described more fully in <a class="xref" href="xfunc-sql.html#XFUNC-SQL-TABLE-FUNCTIONS" title="36.5.8. SQL Functions as Table Sources">Section 36.5.8</a>.
 382     </p><p>
 383      When you use a function that returns a composite type,
 384      you might want only one field (attribute) from its result.
 385      You can do that with syntax like this:
 386
 387 </p><pre class="screen">
 388 SELECT (new_emp()).name;
 389
 390  name
 391 ------
 392  None
 393 </pre><p>
 394
 395      The extra parentheses are needed to keep the parser from getting
 396      confused.  If you try to do it without them, you get something like this:
 397
 398 </p><pre class="screen">
 399 SELECT new_emp().name;
 400 ERROR:  syntax error at or near "."
 401 LINE 1: SELECT new_emp().name;
 402                         ^
 403 </pre><p>
 404     </p><p>
 405      Another option is to use functional notation for extracting an attribute:
 406
 407 </p><pre class="screen">
 408 SELECT name(new_emp());
 409
 410  name
 411 ------
 412  None
 413 </pre><p>
 414
 415      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
 416      functional notation are equivalent.
 417     </p><p>
 418      Another way to use a function returning a composite type is to pass the
 419      result to another function that accepts the correct row type as input:
 420
 421 </p><pre class="screen">
 422 CREATE FUNCTION getname(emp) RETURNS text AS $$
 423     SELECT $1.name;
 424 $$ LANGUAGE SQL;
 425
 426 SELECT getname(new_emp());
 427  getname
 428 ---------
 429  None
 430 (1 row)
 431 </pre><p>
 432     </p></div><div class="sect2" id="XFUNC-OUTPUT-PARAMETERS"><div class="titlepage"><div><div><h3 class="title">36.5.4. <acronym class="acronym">SQL</acronym> Functions with Output Parameters <a href="#XFUNC-OUTPUT-PARAMETERS" class="id_link">#</a></h3></div></div></div><a id="id-1.8.3.8.12.2" class="indexterm"></a><p>
 433      An alternative way of describing a function's results is to define it
 434      with <em class="firstterm">output parameters</em>, as in this example:
 435
 436 </p><pre class="screen">
 437 CREATE FUNCTION add_em (IN x int, IN y int, OUT sum int)
 438 AS 'SELECT x + y'
 439 LANGUAGE SQL;
 440
 441 SELECT add_em(3,7);
 442  add_em
 443 --------
 444      10
 445 (1 row)
 446 </pre><p>
 447
 448      This is not essentially different from the version of <code class="literal">add_em</code>
 449      shown in <a class="xref" href="xfunc-sql.html#XFUNC-SQL-BASE-FUNCTIONS" title="36.5.2. SQL Functions on Base Types">Section 36.5.2</a>.  The real value of
 450      output parameters is that they provide a convenient way of defining
 451      functions that return several columns.  For example,
 452
 453 </p><pre class="screen">
 454 CREATE FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product int)
 455 AS 'SELECT x + y, x * y'
 456 LANGUAGE SQL;
 457
 458  SELECT * FROM sum_n_product(11,42);
 459  sum | product
 460 -----+---------
 461   53 |     462
 462 (1 row)
 463 </pre><p>
 464
 465      What has essentially happened here is that we have created an anonymous
 466      composite type for the result of the function.  The above example has
 467      the same end result as
 468
 469 </p><pre class="screen">
 470 CREATE TYPE sum_prod AS (sum int, product int);
 471
 472 CREATE FUNCTION sum_n_product (int, int) RETURNS sum_prod
 473 AS 'SELECT $1 + $2, $1 * $2'
 474 LANGUAGE SQL;
 475 </pre><p>
 476
 477      but not having to bother with the separate composite type definition
 478      is often handy.  Notice that the names attached to the output parameters
 479      are not just decoration, but determine the column names of the anonymous
 480      composite type.  (If you omit a name for an output parameter, the
 481      system will choose a name on its own.)
 482     </p><p>
 483      Notice that output parameters are not included in the calling argument
 484      list when invoking such a function from SQL.  This is because
 485      <span class="productname">PostgreSQL</span> considers only the input
 486      parameters to define the function's calling signature.  That means
 487      also that only the input parameters matter when referencing the function
 488      for purposes such as dropping it.  We could drop the above function
 489      with either of
 490
 491 </p><pre class="screen">
 492 DROP FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product int);
 493 DROP FUNCTION sum_n_product (int, int);
 494 </pre><p>
 495     </p><p>
 496      Parameters can be marked as <code class="literal">IN</code> (the default),
 497      <code class="literal">OUT</code>, <code class="literal">INOUT</code>, or <code class="literal">VARIADIC</code>.
 498      An <code class="literal">INOUT</code>
 499      parameter serves as both an input parameter (part of the calling
 500      argument list) and an output parameter (part of the result record type).
 501      <code class="literal">VARIADIC</code> parameters are input parameters, but are treated
 502      specially as described below.
 503     </p></div><div class="sect2" id="XFUNC-OUTPUT-PARAMETERS-PROC"><div class="titlepage"><div><div><h3 class="title">36.5.5. <acronym class="acronym">SQL</acronym> Procedures with Output Parameters <a href="#XFUNC-OUTPUT-PARAMETERS-PROC" class="id_link">#</a></h3></div></div></div><a id="id-1.8.3.8.13.2" class="indexterm"></a><p>
 504      Output parameters are also supported in procedures, but they work a bit
 505      differently from functions.  In <code class="command">CALL</code> commands,
 506      output parameters must be included in the argument list.
 507      For example, the bank account debiting routine from earlier could be
 508      written like this:
 509 </p><pre class="programlisting">
 510 CREATE PROCEDURE tp1 (accountno integer, debit numeric, OUT new_balance numeric) AS $$
 511     UPDATE bank
 512         SET balance = balance - debit
 513         WHERE accountno = tp1.accountno
 514     RETURNING balance;
 515 $$ LANGUAGE SQL;
 516 </pre><p>
 517      To call this procedure, an argument matching the <code class="literal">OUT</code>
 518      parameter must be included.  It's customary to write
 519      <code class="literal">NULL</code>:
 520 </p><pre class="programlisting">
 521 CALL tp1(17, 100.0, NULL);
 522 </pre><p>
 523      If you write something else, it must be an expression that is implicitly
 524      coercible to the declared type of the parameter, just as for input
 525      parameters.  Note however that such an expression will not be evaluated.
 526     </p><p>
 527      When calling a procedure from <span class="application">PL/pgSQL</span>,
 528      instead of writing <code class="literal">NULL</code> you must write a variable
 529      that will receive the procedure's output.  See <a class="xref" href="plpgsql-control-structures.html#PLPGSQL-STATEMENTS-CALLING-PROCEDURE" title="41.6.3. Calling a Procedure">Section 41.6.3</a> for details.
 530     </p></div><div class="sect2" id="XFUNC-SQL-VARIADIC-FUNCTIONS"><div class="titlepage"><div><div><h3 class="title">36.5.6. <acronym class="acronym">SQL</acronym> Functions with Variable Numbers of Arguments <a href="#XFUNC-SQL-VARIADIC-FUNCTIONS" class="id_link">#</a></h3></div></div></div><a id="id-1.8.3.8.14.2" class="indexterm"></a><a id="id-1.8.3.8.14.3" class="indexterm"></a><p>
 531      <acronym class="acronym">SQL</acronym> functions can be declared to accept
 532      variable numbers of arguments, so long as all the <span class="quote">“<span class="quote">optional</span>”</span>
 533      arguments are of the same data type.  The optional arguments will be
 534      passed to the function as an array.  The function is declared by
 535      marking the last parameter as <code class="literal">VARIADIC</code>; this parameter
 536      must be declared as being of an array type.  For example:
 537
 538 </p><pre class="screen">
 539 CREATE FUNCTION mleast(VARIADIC arr numeric[]) RETURNS numeric AS $$
 540     SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i);
 541 $$ LANGUAGE SQL;
 542
 543 SELECT mleast(10, -1, 5, 4.4);
 544  mleast
 545 --------
 546      -1
 547 (1 row)
 548 </pre><p>
 549
 550      Effectively, all the actual arguments at or beyond the
 551      <code class="literal">VARIADIC</code> position are gathered up into a one-dimensional
 552      array, as if you had written
 553
 554 </p><pre class="screen">
 555 SELECT mleast(ARRAY[10, -1, 5, 4.4]);    -- doesn't work
 556 </pre><p>
 557
 558      You can't actually write that, though — or at least, it will
 559      not match this function definition.  A parameter marked
 560      <code class="literal">VARIADIC</code> matches one or more occurrences of its element
 561      type, not of its own type.
 562     </p><p>
 563      Sometimes it is useful to be able to pass an already-constructed array
 564      to a variadic function; this is particularly handy when one variadic
 565      function wants to pass on its array parameter to another one.  Also,
 566      this is the only secure way to call a variadic function found in a schema
 567      that permits untrusted users to create objects; see
 568      <a class="xref" href="typeconv-func.html" title="10.3. Functions">Section 10.3</a>.  You can do this by
 569      specifying <code class="literal">VARIADIC</code> in the call:
 570
 571 </p><pre class="screen">
 572 SELECT mleast(VARIADIC ARRAY[10, -1, 5, 4.4]);
 573 </pre><p>
 574
 575      This prevents expansion of the function's variadic parameter into its
 576      element type, thereby allowing the array argument value to match
 577      normally.  <code class="literal">VARIADIC</code> can only be attached to the last
 578      actual argument of a function call.
 579     </p><p>
 580      Specifying <code class="literal">VARIADIC</code> in the call is also the only way to
 581      pass an empty array to a variadic function, for example:
 582
 583 </p><pre class="screen">
 584 SELECT mleast(VARIADIC ARRAY[]::numeric[]);
 585 </pre><p>
 586
 587      Simply writing <code class="literal">SELECT mleast()</code> does not work because a
 588      variadic parameter must match at least one actual argument.
 589      (You could define a second function also named <code class="literal">mleast</code>,
 590      with no parameters, if you wanted to allow such calls.)
 591     </p><p>
 592      The array element parameters generated from a variadic parameter are
 593      treated as not having any names of their own.  This means it is not
 594      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
 595      <code class="literal">VARIADIC</code>.  For example, this will work:
 596
 597 </p><pre class="screen">
 598 SELECT mleast(VARIADIC arr =&gt; ARRAY[10, -1, 5, 4.4]);
 599 </pre><p>
 600
 601      but not these:
 602
 603 </p><pre class="screen">
 604 SELECT mleast(arr =&gt; 10);
 605 SELECT mleast(arr =&gt; ARRAY[10, -1, 5, 4.4]);
 606 </pre><p>
 607     </p></div><div class="sect2" id="XFUNC-SQL-PARAMETER-DEFAULTS"><div class="titlepage"><div><div><h3 class="title">36.5.7. <acronym class="acronym">SQL</acronym> Functions with Default Values for Arguments <a href="#XFUNC-SQL-PARAMETER-DEFAULTS" class="id_link">#</a></h3></div></div></div><a id="id-1.8.3.8.15.2" class="indexterm"></a><p>
 608      Functions can be declared with default values for some or all input
 609      arguments.  The default values are inserted whenever the function is
 610      called with insufficiently many actual arguments.  Since arguments
 611      can only be omitted from the end of the actual argument list, all
 612      parameters after a parameter with a default value have to have
 613      default values as well.  (Although the use of named argument notation
 614      could allow this restriction to be relaxed, it's still enforced so that
 615      positional argument notation works sensibly.)  Whether or not you use it,
 616      this capability creates a need for precautions when calling functions in
 617      databases where some users mistrust other users; see
 618      <a class="xref" href="typeconv-func.html" title="10.3. Functions">Section 10.3</a>.
 619     </p><p>
 620      For example:
 621 </p><pre class="screen">
 622 CREATE FUNCTION foo(a int, b int DEFAULT 2, c int DEFAULT 3)
 623 RETURNS int
 624 LANGUAGE SQL
 625 AS $$
 626     SELECT $1 + $2 + $3;
 627 $$;
 628
 629 SELECT foo(10, 20, 30);
 630  foo
 631 -----
 632   60
 633 (1 row)
 634
 635 SELECT foo(10, 20);
 636  foo
 637 -----
 638   33
 639 (1 row)
 640
 641 SELECT foo(10);
 642  foo
 643 -----
 644   15
 645 (1 row)
 646
 647 SELECT foo();  -- fails since there is no default for the first argument
 648 ERROR:  function foo() does not exist
 649 </pre><p>
 650      The <code class="literal">=</code> sign can also be used in place of the
 651      key word <code class="literal">DEFAULT</code>.
 652     </p></div><div class="sect2" id="XFUNC-SQL-TABLE-FUNCTIONS"><div class="titlepage"><div><div><h3 class="title">36.5.8. <acronym class="acronym">SQL</acronym> Functions as Table Sources <a href="#XFUNC-SQL-TABLE-FUNCTIONS" class="id_link">#</a></h3></div></div></div><p>
 653      All SQL functions can be used in the <code class="literal">FROM</code> clause of a query,
 654      but it is particularly useful for functions returning composite types.
 655      If the function is defined to return a base type, the table function
 656      produces a one-column table.  If the function is defined to return
 657      a composite type, the table function produces a column for each attribute
 658      of the composite type.
 659     </p><p>
 660      Here is an example:
 661
 662 </p><pre class="screen">
 663 CREATE TABLE foo (fooid int, foosubid int, fooname text);
 664 INSERT INTO foo VALUES (1, 1, 'Joe');
 665 INSERT INTO foo VALUES (1, 2, 'Ed');
 666 INSERT INTO foo VALUES (2, 1, 'Mary');
 667
 668 CREATE FUNCTION getfoo(int) RETURNS foo AS $$
 669     SELECT * FROM foo WHERE fooid = $1;
 670 $$ LANGUAGE SQL;
 671
 672 SELECT *, upper(fooname) FROM getfoo(1) AS t1;
 673
 674  fooid | foosubid | fooname | upper
 675 -------+----------+---------+-------
 676      1 |        1 | Joe     | JOE
 677 (1 row)
 678 </pre><p>
 679
 680      As the example shows, we can work with the columns of the function's
 681      result just the same as if they were columns of a regular table.
 682     </p><p>
 683      Note that we only got one row out of the function.  This is because
 684      we did not use <code class="literal">SETOF</code>.  That is described in the next section.
 685     </p></div><div class="sect2" id="XFUNC-SQL-FUNCTIONS-RETURNING-SET"><div class="titlepage"><div><div><h3 class="title">36.5.9. <acronym class="acronym">SQL</acronym> Functions Returning Sets <a href="#XFUNC-SQL-FUNCTIONS-RETURNING-SET" class="id_link">#</a></h3></div></div></div><a id="id-1.8.3.8.17.2" class="indexterm"></a><p>
 686      When an SQL function is declared as returning <code class="literal">SETOF
 687      <em class="replaceable"><code>sometype</code></em></code>, the function's final
 688      query is executed to completion, and each row it
 689      outputs is returned as an element of the result set.
 690     </p><p>
 691      This feature is normally used when calling the function in the <code class="literal">FROM</code>
 692      clause.  In this case each row returned by the function becomes
 693      a row of the table seen by the query.  For example, assume that
 694      table <code class="literal">foo</code> has the same contents as above, and we say:
 695
 696 </p><pre class="programlisting">
 697 CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
 698     SELECT * FROM foo WHERE fooid = $1;
 699 $$ LANGUAGE SQL;
 700
 701 SELECT * FROM getfoo(1) AS t1;
 702 </pre><p>
 703
 704      Then we would get:
 705 </p><pre class="screen">
 706  fooid | foosubid | fooname
 707 -------+----------+---------
 708      1 |        1 | Joe
 709      1 |        2 | Ed
 710 (2 rows)
 711 </pre><p>
 712     </p><p>
 713      It is also possible to return multiple rows with the columns defined by
 714      output parameters, like this:
 715
 716 </p><pre class="programlisting">
 717 CREATE TABLE tab (y int, z int);
 718 INSERT INTO tab VALUES (1, 2), (3, 4), (5, 6), (7, 8);
 719
 720 CREATE FUNCTION sum_n_product_with_tab (x int, OUT sum int, OUT product int)
 721 RETURNS SETOF record
 722 AS $$
 723     SELECT $1 + tab.y, $1 * tab.y FROM tab;
 724 $$ LANGUAGE SQL;
 725
 726 SELECT * FROM sum_n_product_with_tab(10);
 727  sum | product
 728 -----+---------
 729   11 |      10
 730   13 |      30
 731   15 |      50
 732   17 |      70
 733 (4 rows)
 734 </pre><p>
 735
 736      The key point here is that you must write <code class="literal">RETURNS SETOF record</code>
 737      to indicate that the function returns multiple rows instead of just one.
 738      If there is only one output parameter, write that parameter's type
 739      instead of <code class="type">record</code>.
 740     </p><p>
 741      It is frequently useful to construct a query's result by invoking a
 742      set-returning function multiple times, with the parameters for each
 743      invocation coming from successive rows of a table or subquery.  The
 744      preferred way to do this is to use the <code class="literal">LATERAL</code> key word,
 745      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>.
 746      Here is an example using a set-returning function to enumerate
 747      elements of a tree structure:
 748
 749 </p><pre class="screen">
 750 SELECT * FROM nodes;
 751    name    | parent
 752 -----------+--------
 753  Top       |
 754  Child1    | Top
 755  Child2    | Top
 756  Child3    | Top
 757  SubChild1 | Child1
 758  SubChild2 | Child1
 759 (6 rows)
 760
 761 CREATE FUNCTION listchildren(text) RETURNS SETOF text AS $$
 762     SELECT name FROM nodes WHERE parent = $1
 763 $$ LANGUAGE SQL STABLE;
 764
 765 SELECT * FROM listchildren('Top');
 766  listchildren
 767 --------------
 768  Child1
 769  Child2
 770  Child3
 771 (3 rows)
 772
 773 SELECT name, child FROM nodes, LATERAL listchildren(name) AS child;
 774   name  |   child
 775 --------+-----------
 776  Top    | Child1
 777  Top    | Child2
 778  Top    | Child3
 779  Child1 | SubChild1
 780  Child1 | SubChild2
 781 (5 rows)
 782 </pre><p>
 783
 784      This example does not do anything that we couldn't have done with a
 785      simple join, but in more complex calculations the option to put
 786      some of the work into a function can be quite convenient.
 787     </p><p>
 788      Functions returning sets can also be called in the select list
 789      of a query.  For each row that the query
 790      generates by itself, the set-returning function is invoked, and an output
 791      row is generated for each element of the function's result set.
 792      The previous example could also be done with queries like
 793      these:
 794
 795 </p><pre class="screen">
 796 SELECT listchildren('Top');
 797  listchildren
 798 --------------
 799  Child1
 800  Child2
 801  Child3
 802 (3 rows)
 803
 804 SELECT name, listchildren(name) FROM nodes;
 805   name  | listchildren
 806 --------+--------------
 807  Top    | Child1
 808  Top    | Child2
 809  Top    | Child3
 810  Child1 | SubChild1
 811  Child1 | SubChild2
 812 (5 rows)
 813 </pre><p>
 814
 815      In the last <code class="command">SELECT</code>,
 816      notice that no output row appears for <code class="literal">Child2</code>, <code class="literal">Child3</code>, etc.
 817      This happens because <code class="function">listchildren</code> returns an empty set
 818      for those arguments, so no result rows are generated.  This is the same
 819      behavior as we got from an inner join to the function result when using
 820      the <code class="literal">LATERAL</code> syntax.
 821     </p><p>
 822      <span class="productname">PostgreSQL</span>'s behavior for a set-returning function in a
 823      query's select list is almost exactly the same as if the set-returning
 824      function had been written in a <code class="literal">LATERAL FROM</code>-clause item
 825      instead.  For example,
 826 </p><pre class="programlisting">
 827 SELECT x, generate_series(1,5) AS g FROM tab;
 828 </pre><p>
 829      is almost equivalent to
 830 </p><pre class="programlisting">
 831 SELECT x, g FROM tab, LATERAL generate_series(1,5) AS g;
 832 </pre><p>
 833      It would be exactly the same, except that in this specific example,
 834      the planner could choose to put <code class="structname">g</code> on the outside of the
 835      nested-loop join, since <code class="structname">g</code> has no actual lateral dependency
 836      on <code class="structname">tab</code>.  That would result in a different output row
 837      order.  Set-returning functions in the select list are always evaluated
 838      as though they are on the inside of a nested-loop join with the rest of
 839      the <code class="literal">FROM</code> clause, so that the function(s) are run to
 840      completion before the next row from the <code class="literal">FROM</code> clause is
 841      considered.
 842     </p><p>
 843      If there is more than one set-returning function in the query's select
 844      list, the behavior is similar to what you get from putting the functions
 845      into a single <code class="literal">LATERAL ROWS FROM( ... )</code> <code class="literal">FROM</code>-clause
 846      item.  For each row from the underlying query, there is an output row
 847      using the first result from each function, then an output row using the
 848      second result, and so on.  If some of the set-returning functions
 849      produce fewer outputs than others, null values are substituted for the
 850      missing data, so that the total number of rows emitted for one
 851      underlying row is the same as for the set-returning function that
 852      produced the most outputs.  Thus the set-returning functions
 853      run <span class="quote">“<span class="quote">in lockstep</span>”</span> until they are all exhausted, and then
 854      execution continues with the next underlying row.
 855     </p><p>
 856      Set-returning functions can be nested in a select list, although that is
 857      not allowed in <code class="literal">FROM</code>-clause items.  In such cases, each level
 858      of nesting is treated separately, as though it were
 859      a separate <code class="literal">LATERAL ROWS FROM( ... )</code> item.  For example, in
 860 </p><pre class="programlisting">
 861 SELECT srf1(srf2(x), srf3(y)), srf4(srf5(z)) FROM tab;
 862 </pre><p>
 863      the set-returning functions <code class="function">srf2</code>, <code class="function">srf3</code>,
 864      and <code class="function">srf5</code> would be run in lockstep for each row
 865      of <code class="structname">tab</code>, and then <code class="function">srf1</code> and <code class="function">srf4</code>
 866      would be applied in lockstep to each row produced by the lower
 867      functions.
 868     </p><p>
 869      Set-returning functions cannot be used within conditional-evaluation
 870      constructs, such as <code class="literal">CASE</code> or <code class="literal">COALESCE</code>.  For
 871      example, consider
 872 </p><pre class="programlisting">
 873 SELECT x, CASE WHEN x &gt; 0 THEN generate_series(1, 5) ELSE 0 END FROM tab;
 874 </pre><p>
 875      It might seem that this should produce five repetitions of input rows
 876      that have <code class="literal">x &gt; 0</code>, and a single repetition of those that do
 877      not; but actually, because <code class="function">generate_series(1, 5)</code> would be
 878      run in an implicit <code class="literal">LATERAL FROM</code> item before
 879      the <code class="literal">CASE</code> expression is ever evaluated, it would produce five
 880      repetitions of every input row.  To reduce confusion, such cases produce
 881      a parse-time error instead.
 882     </p><div class="note"><h3 class="title">Note</h3><p>
 883       If a function's last command is <code class="command">INSERT</code>,
 884       <code class="command">UPDATE</code>, <code class="command">DELETE</code>, or
 885       <code class="command">MERGE</code> with <code class="literal">RETURNING</code>, that command will
 886       always be executed to completion, even if the function is not declared
 887       with <code class="literal">SETOF</code> or the calling query does not fetch all the
 888       result rows.  Any extra rows produced by the <code class="literal">RETURNING</code>
 889       clause are silently dropped, but the commanded table modifications
 890       still happen (and are all completed before returning from the function).
 891      </p></div><div class="note"><h3 class="title">Note</h3><p>
 892       Before <span class="productname">PostgreSQL</span> 10, putting more than one
 893       set-returning function in the same select list did not behave very
 894       sensibly unless they always produced equal numbers of rows.  Otherwise,
 895       what you got was a number of output rows equal to the least common
 896       multiple of the numbers of rows produced by the set-returning
 897       functions.  Also, nested set-returning functions did not work as
 898       described above; instead, a set-returning function could have at most
 899       one set-returning argument, and each nest of set-returning functions
 900       was run independently.  Also, conditional execution (set-returning
 901       functions inside <code class="literal">CASE</code> etc.) was previously allowed,
 902       complicating things even more.
 903       Use of the <code class="literal">LATERAL</code> syntax is recommended when writing
 904       queries that need to work in older <span class="productname">PostgreSQL</span> versions,
 905       because that will give consistent results across different versions.
 906       If you have a query that is relying on conditional execution of a
 907       set-returning function, you may be able to fix it by moving the
 908       conditional test into a custom set-returning function.  For example,
 909 </p><pre class="programlisting">
 910 SELECT x, CASE WHEN y &gt; 0 THEN generate_series(1, z) ELSE 5 END FROM tab;
 911 </pre><p>
 912       could become
 913 </p><pre class="programlisting">
 914 CREATE FUNCTION case_generate_series(cond bool, start int, fin int, els int)
 915   RETURNS SETOF int AS $$
 916 BEGIN
 917   IF cond THEN
 918     RETURN QUERY SELECT generate_series(start, fin);
 919   ELSE
 920     RETURN QUERY SELECT els;
 921   END IF;
 922 END$$ LANGUAGE plpgsql;
 923
 924 SELECT x, case_generate_series(y &gt; 0, 1, z, 5) FROM tab;
 925 </pre><p>
 926       This formulation will work the same in all versions
 927       of <span class="productname">PostgreSQL</span>.
 928      </p></div></div><div class="sect2" id="XFUNC-SQL-FUNCTIONS-RETURNING-TABLE"><div class="titlepage"><div><div><h3 class="title">36.5.10. <acronym class="acronym">SQL</acronym> Functions Returning <code class="literal">TABLE</code> <a href="#XFUNC-SQL-FUNCTIONS-RETURNING-TABLE" class="id_link">#</a></h3></div></div></div><a id="id-1.8.3.8.18.2" class="indexterm"></a><p>
 929      There is another way to declare a function as returning a set,
 930      which is to use the syntax
 931      <code class="literal">RETURNS TABLE(<em class="replaceable"><code>columns</code></em>)</code>.
 932      This is equivalent to using one or more <code class="literal">OUT</code> parameters plus
 933      marking the function as returning <code class="literal">SETOF record</code> (or
 934      <code class="literal">SETOF</code> a single output parameter's type, as appropriate).
 935      This notation is specified in recent versions of the SQL standard, and
 936      thus may be more portable than using <code class="literal">SETOF</code>.
 937     </p><p>
 938      For example, the preceding sum-and-product example could also be
 939      done this way:
 940
 941 </p><pre class="programlisting">
 942 CREATE FUNCTION sum_n_product_with_tab (x int)
 943 RETURNS TABLE(sum int, product int) AS $$
 944     SELECT $1 + tab.y, $1 * tab.y FROM tab;
 945 $$ LANGUAGE SQL;
 946 </pre><p>
 947
 948      It is not allowed to use explicit <code class="literal">OUT</code> or <code class="literal">INOUT</code>
 949      parameters with the <code class="literal">RETURNS TABLE</code> notation — you must
 950      put all the output columns in the <code class="literal">TABLE</code> list.
 951     </p></div><div class="sect2" id="XFUNC-SQL-POLYMORPHIC-FUNCTIONS"><div class="titlepage"><div><div><h3 class="title">36.5.11. Polymorphic <acronym class="acronym">SQL</acronym> Functions <a href="#XFUNC-SQL-POLYMORPHIC-FUNCTIONS" class="id_link">#</a></h3></div></div></div><p>
 952      <acronym class="acronym">SQL</acronym> functions can be declared to accept and
 953      return the polymorphic types described in <a class="xref" href="extend-type-system.html#EXTEND-TYPES-POLYMORPHIC" title="36.2.5. Polymorphic Types">Section 36.2.5</a>.  Here is a polymorphic
 954      function <code class="function">make_array</code> that builds up an array
 955      from two arbitrary data type elements:
 956 </p><pre class="screen">
 957 CREATE FUNCTION make_array(anyelement, anyelement) RETURNS anyarray AS $$
 958     SELECT ARRAY[$1, $2];
 959 $$ LANGUAGE SQL;
 960
 961 SELECT make_array(1, 2) AS intarray, make_array('a'::text, 'b') AS textarray;
 962  intarray | textarray
 963 ----------+-----------
 964  {1,2}    | {a,b}
 965 (1 row)
 966 </pre><p>
 967     </p><p>
 968      Notice the use of the typecast <code class="literal">'a'::text</code>
 969      to specify that the argument is of type <code class="type">text</code>. This is
 970      required if the argument is just a string literal, since otherwise
 971      it would be treated as type
 972      <code class="type">unknown</code>, and array of <code class="type">unknown</code> is not a valid
 973      type.
 974      Without the typecast, you will get errors like this:
 975 </p><pre class="screen">
 976 ERROR:  could not determine polymorphic type because input has type unknown
 977 </pre><p>
 978     </p><p>
 979      With <code class="function">make_array</code> declared as above, you must
 980      provide two arguments that are of exactly the same data type; the
 981      system will not attempt to resolve any type differences.  Thus for
 982      example this does not work:
 983 </p><pre class="screen">
 984 SELECT make_array(1, 2.5) AS numericarray;
 985 ERROR:  function make_array(integer, numeric) does not exist
 986 </pre><p>
 987      An alternative approach is to use the <span class="quote">“<span class="quote">common</span>”</span> family of
 988      polymorphic types, which allows the system to try to identify a
 989      suitable common type:
 990 </p><pre class="screen">
 991 CREATE FUNCTION make_array2(anycompatible, anycompatible)
 992 RETURNS anycompatiblearray AS $$
 993     SELECT ARRAY[$1, $2];
 994 $$ LANGUAGE SQL;
 995
 996 SELECT make_array2(1, 2.5) AS numericarray;
 997  numericarray
 998 --------------
 999  {1,2.5}
1000 (1 row)
1001 </pre><p>
1002      Because the rules for common type resolution default to choosing
1003      type <code class="type">text</code> when all inputs are of unknown types, this
1004      also works:
1005 </p><pre class="screen">
1006 SELECT make_array2('a', 'b') AS textarray;
1007  textarray
1008 -----------
1009  {a,b}
1010 (1 row)
1011 </pre><p>
1012     </p><p>
1013      It is permitted to have polymorphic arguments with a fixed
1014      return type, but the converse is not. For example:
1015 </p><pre class="screen">
1016 CREATE FUNCTION is_greater(anyelement, anyelement) RETURNS boolean AS $$
1017     SELECT $1 &gt; $2;
1018 $$ LANGUAGE SQL;
1019
1020 SELECT is_greater(1, 2);
1021  is_greater
1022 ------------
1023  f
1024 (1 row)
1025
1026 CREATE FUNCTION invalid_func() RETURNS anyelement AS $$
1027     SELECT 1;
1028 $$ LANGUAGE SQL;
1029 ERROR:  cannot determine result data type
1030 DETAIL:  A result of type anyelement requires at least one input of type anyelement, anyarray, anynonarray, anyenum, or anyrange.
1031 </pre><p>
1032     </p><p>
1033      Polymorphism can be used with functions that have output arguments.
1034      For example:
1035 </p><pre class="screen">
1036 CREATE FUNCTION dup (f1 anyelement, OUT f2 anyelement, OUT f3 anyarray)
1037 AS 'select $1, array[$1,$1]' LANGUAGE SQL;
1038
1039 SELECT * FROM dup(22);
1040  f2 |   f3
1041 ----+---------
1042  22 | {22,22}
1043 (1 row)
1044 </pre><p>
1045     </p><p>
1046      Polymorphism can also be used with variadic functions.
1047      For example:
1048 </p><pre class="screen">
1049 CREATE FUNCTION anyleast (VARIADIC anyarray) RETURNS anyelement AS $$
1050     SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i);
1051 $$ LANGUAGE SQL;
1052
1053 SELECT anyleast(10, -1, 5, 4);
1054  anyleast
1055 ----------
1056        -1
1057 (1 row)
1058
1059 SELECT anyleast('abc'::text, 'def');
1060  anyleast
1061 ----------
1062  abc
1063 (1 row)
1064
1065 CREATE FUNCTION concat_values(text, VARIADIC anyarray) RETURNS text AS $$
1066     SELECT array_to_string($2, $1);
1067 $$ LANGUAGE SQL;
1068
1069 SELECT concat_values('|', 1, 4, 2);
1070  concat_values
1071 ---------------
1072  1|4|2
1073 (1 row)
1074 </pre><p>
1075     </p></div><div class="sect2" id="XFUNC-SQL-COLLATIONS"><div class="titlepage"><div><div><h3 class="title">36.5.12. <acronym class="acronym">SQL</acronym> Functions with Collations <a href="#XFUNC-SQL-COLLATIONS" class="id_link">#</a></h3></div></div></div><a id="id-1.8.3.8.20.2" class="indexterm"></a><p>
1076      When an SQL function has one or more parameters of collatable data types,
1077      a collation is identified for each function call depending on the
1078      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
1079      (i.e., there are no conflicts of implicit collations among the arguments)
1080      then all the collatable parameters are treated as having that collation
1081      implicitly.  This will affect the behavior of collation-sensitive
1082      operations within the function.  For example, using the
1083      <code class="function">anyleast</code> function described above, the result of
1084 </p><pre class="programlisting">
1085 SELECT anyleast('abc'::text, 'ABC');
1086 </pre><p>
1087      will depend on the database's default collation.  In <code class="literal">C</code> locale
1088      the result will be <code class="literal">ABC</code>, but in many other locales it will
1089      be <code class="literal">abc</code>.  The collation to use can be forced by adding
1090      a <code class="literal">COLLATE</code> clause to any of the arguments, for example
1091 </p><pre class="programlisting">
1092 SELECT anyleast('abc'::text, 'ABC' COLLATE "C");
1093 </pre><p>
1094      Alternatively, if you wish a function to operate with a particular
1095      collation regardless of what it is called with, insert
1096      <code class="literal">COLLATE</code> clauses as needed in the function definition.
1097      This version of <code class="function">anyleast</code> would always use <code class="literal">en_US</code>
1098      locale to compare strings:
1099 </p><pre class="programlisting">
1100 CREATE FUNCTION anyleast (VARIADIC anyarray) RETURNS anyelement AS $$
1101     SELECT min($1[i] COLLATE "en_US") FROM generate_subscripts($1, 1) g(i);
1102 $$ LANGUAGE SQL;
1103 </pre><p>
1104      But note that this will throw an error if applied to a non-collatable
1105      data type.
1106     </p><p>
1107      If no common collation can be identified among the actual arguments,
1108      then an SQL function treats its parameters as having their data types'
1109      default collation (which is usually the database's default collation,
1110      but could be different for parameters of domain types).
1111     </p><p>
1112      The behavior of collatable parameters can be thought of as a limited
1113      form of polymorphism, applicable only to textual data types.
1114     </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="36.4. User-Defined Procedures">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="extend.html" title="Chapter 36. Extending SQL">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="xfunc-overload.html" title="36.6. Function Overloading">Next</a></td></tr><tr><td width="40%" align="left" valign="top">36.4. User-Defined Procedures </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 36.6. Function Overloading</td></tr></table></div></body></html>