xfunc.sgml

PostgreSQL7.4.6 for Linux

<screen>
CREATE FUNCTION getname(emp) RETURNS text AS '
    SELECT $1.name;
' LANGUAGE SQL;

SELECT getname(new_emp());
 getname
---------
 None
(1 row)
</screen>
    </para>     
   </sect2>

   <sect2>
    <title><acronym>SQL</acronym> Functions as Table Sources</title>

    <para>
     All SQL functions may be used in the <literal>FROM</> 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.
    </para>

    <para>
     Here is an example:

<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
(2 rows)
</screen>

     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.
    </para>

    <para>
     Note that we only got one row out of the function.  This is because
     we did not use <literal>SETOF</>.  This is described in the next section.
    </para>
   </sect2>

   <sect2>
    <title><acronym>SQL</acronym> Functions Returning Sets</title>

    <para>
     When an SQL function is declared as returning <literal>SETOF
     <replaceable>sometype</></literal>, the function's final
     <command>SELECT</> query is executed to completion, and each row it
     outputs is returned as an element of the result set.
    </para>

    <para>
     This feature is normally used when calling the function in the <literal>FROM</>
     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 <literal>foo</> has the same contents as above, and we say:

<programlisting>
CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS '
    SELECT * FROM foo WHERE fooid = $1;
' LANGUAGE SQL;

SELECT * FROM getfoo(1) AS t1;
</programlisting>

     Then we would get:
<screen>
 fooid | foosubid | fooname
-------+----------+---------
     1 |        1 | Joe
     1 |        2 | Ed
(2 rows)
</screen>
    </para>

    <para>
     Currently, functions returning sets may also be called in the select list
     of a query.  For each row that the query
     generates by itself, the function returning set is invoked, and an output
     row is generated for each element of the function's result set. Note,
     however, that this capability is deprecated and may be removed in future
     releases. The following is an example function returning a set from the
     select list:

<screen>
CREATE FUNCTION listchildren(text) RETURNS SETOF text AS
'SELECT name FROM nodes WHERE parent = $1'
LANGUAGE SQL;

SELECT * FROM nodes;
   name    | parent
-----------+--------
 Top       |
 Child1    | Top
 Child2    | Top
 Child3    | Top
 SubChild1 | Child1
 SubChild2 | Child1
(6 rows)

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)
</screen>

     In the last <command>SELECT</command>,
     notice that no output row appears for <literal>Child2</>, <literal>Child3</>, etc.
     This happens because <function>listchildren</function> returns an empty set
     for those arguments, so no result rows are generated.
    </para>
   </sect2>

   <sect2>
    <title>Polymorphic <acronym>SQL</acronym> Functions</title>

    <para>
     <acronym>SQL</acronym> functions may be declared to accept and
     return the polymorphic types <type>anyelement</type> and
     <type>anyarray</type>.  See <xref
     linkend="extend-types-polymorphic"> for a more detailed
     explanation of polymorphic functions. Here is a polymorphic
     function <function>make_array</function> that builds up an array
     from two arbitrary data type elements:
<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)
</screen>
    </para>

    <para>
     Notice the use of the typecast <literal>'a'::text</literal>
     to specify that the argument is of type <type>text</type>. This is
     required if the argument is just a string literal, since otherwise
     it would be treated as type
     <type>unknown</type>, and array of <type>unknown</type> is not a valid
     type.
     Without the typecast, you will get errors like this:
<screen>
<computeroutput>
ERROR:  could not determine "anyarray"/"anyelement" type because input has type "unknown"
</computeroutput>
</screen>
    </para>

    <para>
     It is permitted to have polymorphic arguments with a deterministic
     return type, but the converse is not. For example:
<screen>
CREATE FUNCTION is_greater(anyelement, anyelement) RETURNS boolean AS '
    SELECT $1 > $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 function returning "anyarray" or "anyelement" must have at least one argument of either type.
</screen>
    </para>
   </sect2>
  </sect1>

  <sect1 id="xfunc-pl">
   <title>Procedural Language Functions</title>

   <para>
    Procedural languages aren't built into the
    <productname>PostgreSQL</productname> server; they are offered
    by loadable modules. Please refer to the documentation of the
    procedural language in question for details about the syntax and how the
    function body is interpreted for each language.
   </para>

   <para>
    There are currently four procedural languages available in the
    standard <productname>PostgreSQL</productname> distribution:
    <application>PL/pgSQL</application>, <application>PL/Tcl</application>,
    <application>PL/Perl</application>, and
    <application>PL/Python</application>.
    Refer to <xref linkend="xplang"> for more information.
    Other languages can be defined by users.
    The basics of developing a new procedural language are covered in <xref
    linkend="plhandler">.
   </para>
  </sect1>

  <sect1 id="xfunc-internal">
   <title>Internal Functions</title>

   <indexterm zone="xfunc-internal"><primary>function</><secondary>internal</></>

   <para>
    Internal functions are functions written in C that have been statically
    linked into the <productname>PostgreSQL</productname> server.
    The <quote>body</quote> of the function definition
    specifies the C-language name of the function, which need not be the
    same as the name being declared for SQL use.
    (For reasons of backwards compatibility, an empty body
    is accepted as meaning that the C-language function name is the
    same as the SQL name.)
   </para>

   <para>
    Normally, all internal functions present in the
    server are declared during the initialization of the database cluster (<command>initdb</command>),
    but a user could use <command>CREATE FUNCTION</command>
    to create additional alias names for an internal function.
    Internal functions are declared in <command>CREATE FUNCTION</command>
    with language name <literal>internal</literal>.  For instance, to
    create an alias for the <function>sqrt</function> function:
<programlisting>
CREATE FUNCTION square_root(double precision) RETURNS double precision
    AS 'dsqrt'
    LANGUAGE internal
    STRICT;
</programlisting>
    (Most internal functions expect to be declared <quote>strict</quote>.)
   </para>

   <note>
    <para>
     Not all <quote>predefined</quote> functions are
     <quote>internal</quote> in the above sense.  Some predefined
     functions are written in SQL.
    </para>
   </note>
  </sect1>

  <sect1 id="xfunc-c">
   <title>C-Language Functions</title>

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

   <para>
    User-defined functions can be written in C (or a language that can
    be made compatible with C, such as C++).  Such functions are
    compiled into dynamically loadable objects (also called shared
    libraries) and are loaded by the server on demand.  The dynamic
    loading feature is what distinguishes <quote>C language</> functions
    from <quote>internal</> functions --- the actual coding conventions
    are essentially the same for both.  (Hence, the standard internal
    function library is a rich source of coding examples for user-defined
    C functions.)
   </para>

   <para>
    Two different calling conventions are currently used for C functions.
    The newer <quote>version 1</quote> calling convention is indicated by writing
    a <literal>PG_FUNCTION_INFO_V1()</literal> macro call for the function,
    as illustrated below.  Lack of such a macro indicates an old-style
    (<quote>version 0</quote>) function.  The language name specified in <command>CREATE FUNCTION</command>
    is <literal>C</literal> in either case.  Old-style functions are now deprecated
    because of portability problems and lack of functionality, but they
    are still supported for compatibility reasons.
   </para>

  <sect2 id="xfunc-c-dynload">
   <title>Dynamic Loading</title>

   <indexterm zone="xfunc-c-dynload">
    <primary>dynamic loading</primary>
   </indexterm>

   <para>
    The first time a user-defined function in a particular
    loadable object file is called in a session,
    the dynamic loader loads that object file into memory so that the
    function can be called.  The <command>CREATE FUNCTION</command>
    for a user-defined C function must therefore specify two pieces of
    information for the function: the name of the loadable
    object file, and the C name (link symbol) of the specific function to call
    within that object file.  If the C name is not explicitly specified then
    it is assumed to be the same as the SQL function name.
   </para>

   <para>
    The following algorithm is used to locate the shared object file
    based on the name given in the <command>CREATE FUNCTION</command>
    command:

    <orderedlist>
     <listitem>
      <para>
       If the name is an absolute path, the given file is loaded.
      </para>
     </listitem>

     <listitem>
      <para>
       If the name starts with the string <literal>$libdir</literal>,
       that part is replaced by the <productname>PostgreSQL</> package
	library directory
       name, which is determined at build time.<indexterm><primary>$libdir</></>
      </para>
     </listitem>

     <listitem>
      <para>
       If the name does not contain a directory part, the file is
       searched for in the path specified by the configuration variable
       <varname>dynamic_library_path</varname>.<indexterm><primary>dynamic_library_path</></>
      </para>
     </listitem>

     <listitem>
      <para>
       Otherwise (the file was not found in the path, or it contains a
       non-absolute directory part), the dynamic loader will try to
       take the name as given, which will most likely fail.  (It is
       unreliable to depend on the current working directory.)
      </para>
     </listitem>
    </orderedlist>

    If this sequence does not work, the platform-specific shared
    library file name extension (often <filename>.so</filename>) is
    appended to the given name and this sequence is tried again.  If
    that fails as well, the load will fail.
   </para>

   <para>
    The user ID the <productname>PostgreSQL</productname> server runs
    as must be able to traverse the path to the file you intend to
    load.  Making the file or a higher-level directory not readable
    and/or not executable by the <systemitem>postgres</systemitem>
    user is a common mistake.
   </para>

   <para>
    In any case, the file name that is given in the
    <command>CREATE FUNCTION</command> command is recorded literally
    in the system catalogs, so if the file needs to be loaded again
    the same procedure is applied.
   </para>

   <note>
    <para>
     <productname>PostgreSQL</productname> will not compile a C function
     automatically.  The object file must be compiled before it is referenced
     in a <command>CREATE
     FUNCTION</> command.  See <xref linkend="dfunc"> for additional
     information.
    </para>
   </note>

   <para>