integer) RETURNS integer
    AS 'select $1 + $2;'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;
</programlisting>
   The same function written in a more SQL-conforming style, using argument
   names and an unquoted body:
<programlisting>
CREATE FUNCTION add(a integer, b integer) RETURNS integer
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT
    RETURN a + b;
</programlisting>
  </para>

  <para>
   Increment an integer, making use of an argument name, in
   <application>PL/pgSQL</application>:
<programlisting>
CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$
        BEGIN
                RETURN i + 1;
        END;
$$ LANGUAGE plpgsql;
</programlisting>
  </para>

  <para>
   Return a record containing multiple output parameters:
<programlisting>
CREATE FUNCTION dup(in int, out f1 int, out f2 text)
    AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
    LANGUAGE SQL;

SELECT * FROM dup(42);
</programlisting>
   You can do the same thing more verbosely with an explicitly named
   composite type:
<programlisting>
CREATE TYPE dup_result AS (f1 int, f2 text);

CREATE FUNCTION dup(int) RETURNS dup_result
    AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
    LANGUAGE SQL;

SELECT * FROM dup(42);
</programlisting>
   Another way to return multiple columns is to use a <literal>TABLE</literal>
   function:
<programlisting>
CREATE FUNCTION dup(int) RETURNS TABLE(f1 int, f2 text)
    AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
    LANGUAGE SQL;

SELECT * FROM dup(42);
</programlisting>
   However, a <literal>TABLE</literal> function is different from the
   preceding examples, because it actually returns a <emphasis>set</emphasis>
   of records, not just one record.
  </para>
 </refsect1>

 <refsect1 id="sql-createfunction-security">
  <title>Writing <literal>SECURITY DEFINER</literal> Functions Safely</title>

  <indexterm>
   <primary><varname>search_path</varname> configuration parameter</primary>
   <secondary>use in securing functions</secondary>
  </indexterm>

  <indexterm>
   <primary><varname>createrole_self_grant</varname> configuration parameter</primary>
   <secondary>use in securing functions</secondary>
  </indexterm>

   <para>
    Because a <literal>SECURITY DEFINER</literal> function is executed
    with the privileges of the user that owns it, care is needed to
    ensure that the function cannot be misused.  For security,
    <xref linkend="guc-search-path"/> should be set to exclude any schemas
    writable by untrusted users.  This prevents
    malicious users from creating objects (e.g., tables, functions, and
    operators) that mask objects intended to be used by the function.
    Particularly important in this regard is the
    temporary-table schema, which is searched first by default, and
    is normally writable by anyone.  A secure arrangement can be obtained
    by forcing the temporary schema to be searched last.  To do this,
    write <literal>pg_temp</literal><indexterm><primary>pg_temp</primary><secondary>securing functions</secondary></indexterm> as the last entry in <varname>search_path</varname>.
    This function illustrates safe usage:

<programlisting>
CREATE FUNCTION check_password(uname TEXT, pass TEXT)
RETURNS BOOLEAN AS $$
DECLARE passed BOOLEAN;
BEGIN
        SELECT  (pwd = $2) INTO passed
        FROM    pwds
        WHERE   username = $1;

        RETURN passed;
END;
$$  LANGUAGE plpgsql
    SECURITY DEFINER
    -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
    SET search_path = admin, pg_temp;
</programlisting>

    This function's intention is to access a table <literal>admin.pwds</literal>.
    But without the <literal>SET</literal> clause, or with a <literal>SET</literal> clause
    mentioning only <literal>admin</literal>, the function could be subverted by
    creating