Home • Explore • Blog • CI
☰



postgresql
2nd chunk of `doc/src/sgml/xfunc.sgml`
ffc1fb02b008fe8b413c57f6dcf20f0a7e1a04512a4e23390000000100000fa0
 data to their callers via
       output parameters.
      </para>
     </listitem>
     <listitem>
      <para>
       While a function is called as part of a query or DML command, a
       procedure is called in isolation using
       the <link linkend="sql-call"><command>CALL</command></link> command.
      </para>
     </listitem>
     <listitem>
      <para>
       A procedure can commit or roll back transactions during its
       execution (then automatically beginning a new transaction), so long
       as the invoking <command>CALL</command> command is not part of an
       explicit transaction block.  A function cannot do that.
      </para>
     </listitem>
     <listitem>
      <para>
       Certain function attributes, such as strictness, don't apply to
       procedures.  Those attributes control how the function is
       used in a query, which isn't relevant to procedures.
      </para>
     </listitem>
    </itemizedlist>
   </para>

   <para>
    The explanations in the following sections about how to define
    user-defined functions apply to procedures as well, except for the
    points made above.
   </para>

   <para>
    Collectively, functions and procedures are also known
    as <firstterm>routines</firstterm><indexterm><primary>routine</primary></indexterm>.
    There are commands such as <link linkend="sql-alterroutine"><command>ALTER ROUTINE</command></link>
    and <link linkend="sql-droproutine"><command>DROP ROUTINE</command></link> that can operate on functions and
    procedures without having to know which kind it is.  Note, however, that
    there is no <literal>CREATE ROUTINE</literal> command.
   </para>
  </sect1>

  <sect1 id="xfunc-sql">
   <title>Query Language (<acronym>SQL</acronym>) Functions</title>

   <indexterm zone="xfunc-sql">
    <primary>function</primary>
    <secondary>user-defined</secondary>
    <tertiary>in SQL</tertiary>
   </indexterm>

   <para>
    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 <quote>the first row</quote> of a multirow
    result is not well-defined unless you use <literal>ORDER BY</literal>.)
    If the last query happens
    to return no rows at all, the null value will be returned.
   </para>

   <para>
    Alternatively, an SQL function can be declared to return a set (that is,
    multiple rows) by specifying the function's return type as <literal>SETOF
    <replaceable>sometype</replaceable></literal>, or equivalently by declaring it as
    <literal>RETURNS TABLE(<replaceable>columns</replaceable>)</literal>.  In this case
    all rows of the last query's result are returned.  Further details appear
    below.
   </para>

   <para>
    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
    <type>void</type>, the last statement must be a <command>SELECT</command>,
    or an <command>INSERT</command>, <command>UPDATE</command>,
    <command>DELETE</command>, or <command>MERGE</command>
    that has a <literal>RETURNING</literal> clause.
   </para>

    <para>
     Any collection of commands in the  <acronym>SQL</acronym>
     language can be packaged together and defined as a function.
     Besides <command>SELECT</command> queries, the commands can include data
     modification queries (<command>INSERT</command>,
     <command>UPDATE</command>, <command>DELETE</command>, and
     <command>MERGE</command>), as well as
     other SQL commands. (You cannot use transaction control commands, e.g.,
     <command>COMMIT</command>, <command>SAVEPOINT</command>, and some utility
     commands, e.g.,  <literal>VACUUM</literal>, in <acronym>SQL</acronym> functions.)
     However, the final command
     must be a <command>SELECT</command>
Title: User-Defined Procedures and SQL Functions
Summary
This section elaborates on procedures, noting they share most function definition principles except for how they're created (CREATE PROCEDURE), their return mechanisms (output parameters instead of RETURNS), and their invocation (CALL command). Both functions and procedures are collectively known as 'routines' and can be managed using ALTER ROUTINE and DROP ROUTINE. The section then transitions to SQL functions, which execute SQL statements and return the result of the last query, either as a single row or a set of rows. The body consists of semicolon-separated SQL statements, with the last statement typically being a SELECT (or INSERT, UPDATE, DELETE, or MERGE with a RETURNING clause). A wide range of SQL commands can be included, but transaction control commands and certain utility commands are prohibited.