Home • Explore • Blog • CI
☰



postgresql
4th chunk of `doc/src/sgml/ref/create_function.sgml`
ec6dde9eeaef00ade266758422ff4de82ac85418b77e190f0000000100000fa9
 If present, it
       must agree with the result type implied by the output parameters:
       <literal>RECORD</literal> if there are multiple output parameters, or
       the same type as the single output parameter.
      </para>
      <para>
       The <literal>SETOF</literal>
       modifier indicates that the function will return a set of
       items, rather than a single item.
      </para>
      <para>
       The type of a column is referenced by writing
       <literal><replaceable
       class="parameter">table_name</replaceable>.<replaceable
       class="parameter">column_name</replaceable>%TYPE</literal>.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><replaceable class="parameter">column_name</replaceable></term>

     <listitem>
      <para>
       The name of an output column in the <literal>RETURNS TABLE</literal>
       syntax.  This is effectively another way of declaring a named
       <literal>OUT</literal> parameter, except that <literal>RETURNS TABLE</literal>
       also implies <literal>RETURNS SETOF</literal>.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><replaceable class="parameter">column_type</replaceable></term>

     <listitem>
      <para>
       The data type of an output column in the <literal>RETURNS TABLE</literal>
       syntax.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><replaceable class="parameter">lang_name</replaceable></term>

     <listitem>
      <para>
       The name of the language that the function is implemented in.
       It can be <literal>sql</literal>, <literal>c</literal>,
       <literal>internal</literal>, or the name of a user-defined
       procedural language, e.g., <literal>plpgsql</literal>.  The default is
       <literal>sql</literal> if <replaceable
       class="parameter">sql_body</replaceable> is specified.  Enclosing the
       name in single quotes is deprecated and requires matching case.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><literal>TRANSFORM { FOR TYPE <replaceable class="parameter">type_name</replaceable> } [, ... ] }</literal></term>

     <listitem>
      <para>
       Lists which transforms a call to the function should apply.  Transforms
       convert between SQL types and language-specific data types;
       see <xref linkend="sql-createtransform"/>.  Procedural language
       implementations usually have hardcoded knowledge of the built-in types,
       so those don't need to be listed here.  If a procedural language
       implementation does not know how to handle a type and no transform is
       supplied, it will fall back to a default behavior for converting data
       types, but this depends on the implementation.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><literal>WINDOW</literal></term>

     <listitem>
      <para><literal>WINDOW</literal> indicates that the function is a
       <firstterm>window function</firstterm> rather than a plain function.
       This is currently only useful for functions written in C.
       The <literal>WINDOW</literal> attribute cannot be changed when
       replacing an existing function definition.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><literal>IMMUTABLE</literal></term>
     <term><literal>STABLE</literal></term>
     <term><literal>VOLATILE</literal></term>

     <listitem>
      <para>
       These attributes inform the query optimizer about the behavior
       of the function.  At most one choice
       can be specified.  If none of these appear,
       <literal>VOLATILE</literal> is the default assumption.
      </para>

      <para><literal>IMMUTABLE</literal> indicates that the function
       cannot modify the database and always
       returns the same result when given the same argument values; that
       is, it does not do database lookups or otherwise use information
Title: CREATE FUNCTION: RETURNS TABLE, Language, Transforms, WINDOW, and Volatility Attributes
Summary
Explanation of the RETURNS TABLE syntax, which is an alternative to OUT parameters and implies RETURNS SETOF. Specification of the implementation language (SQL, C, internal, or user-defined PL). Use of transforms to convert between SQL types and language-specific data types. WINDOW attribute for C window functions. Volatility attributes (IMMUTABLE, STABLE, VOLATILE) to inform the query optimizer about function behavior.