| <!-- |
| $PostgreSQL: pgsql/doc/src/sgml/ref/create_function.sgml,v 1.70.2.2 2007/04/23 16:52:56 neilc Exp $ |
| --> |
| |
| <refentry id="SQL-CREATEFUNCTION"> |
| <refmeta> |
| <refentrytitle id="SQL-CREATEFUNCTION-TITLE">CREATE FUNCTION</refentrytitle> |
| <refmiscinfo>SQL - Language Statements</refmiscinfo> |
| </refmeta> |
| |
| <refnamediv> |
| <refname>CREATE FUNCTION</refname> |
| <refpurpose>define a new function</refpurpose> |
| </refnamediv> |
| |
| <indexterm zone="sql-createfunction"> |
| <primary>CREATE FUNCTION</primary> |
| </indexterm> |
| |
| <refsynopsisdiv> |
| <synopsis> |
| CREATE [ OR REPLACE ] FUNCTION |
| <replaceable class="parameter">name</replaceable> ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">argtype</replaceable> [, ...] ] ) |
| [ RETURNS [SETOF] <replaceableclass="parameter">rettype</replaceable> ] |
| { LANGUAGE <replaceable class="parameter">langname</replaceable> |
| | IMMUTABLE | STABLE | VOLATILE |
| | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT |
| | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER |
| | AS '<replaceable class="parameter">definition</replaceable>' |
| | AS '<replaceable class="parameter">obj_file</replaceable>', '<replaceable class="parameter">link_symbol</replaceable>' |
| } ... |
| [ WITH ( <replaceable class="parameter">attribute</replaceable> [, ...] ) ] |
| </synopsis> |
| </refsynopsisdiv> |
| |
| <refsect1 id="sql-createfunction-description"> |
| <title>Description</title> |
| |
| <para> |
| <command>CREATE FUNCTION</command> defines a new function. |
| <command>CREATE OR REPLACE FUNCTION</command> will either create a |
| new function, or replace an existing definition. |
| </para> |
| |
| <para> |
| If a schema name is included, then the function is created in the |
| specified schema. Otherwise it is created in the current schema. |
| The name of the new function must not match any existing function |
| with the same argument types in the same schema. However, |
| functions of different argument types may share a name (this is |
| called <firstterm>overloading</>). |
| </para> |
| |
| <para> |
| To update the definition of an existing function, use |
| <command>CREATE OR REPLACE FUNCTION</command>. It is not possible |
| to change the name or argument types of a function this way (if you |
| tried, you would actually be creating a new, distinct function). |
| Also, <command>CREATE OR REPLACE FUNCTION</command> will not let |
| you change the return type of an existing function. To do that, |
| you must drop and recreate the function. (When using <literal>OUT</> |
| parameters, that means you can't change the names or types of any |
| <literal>OUT</> parameters except by dropping the function.) |
| </para> |
| |
| <para> |
| If you drop and then recreate a function, the new function is not |
| the same entity as the old; you will have to drop existing rules, views, |
| triggers, etc. that refer to the old function. Use |
| <command>CREATE OR REPLACE FUNCTION</command> to change a function |
| definition without breaking objects that refer to the function. |
| </para> |
| |
| <para> |
| The user that creates the function becomes the owner of the function. |
| </para> |
| </refsect1> |
| |
| <refsect1> |
| <title>Parameters</title> |
| |
| <variablelist> |
| |
| <varlistentry> |
| <term><replaceable class="parameter">name</replaceable></term> |
| |
| <listitem> |
| <para> |
| The name (optionally schema-qualified) of the function to create. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="parameter">argmode</replaceable></term> |
| |
| <listitem> |
| <para> |
| The mode of an argument: either <literal>IN</>, <literal>OUT</>, |
| or <literal>INOUT</>. If omitted, the default is <literal>IN</>. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="parameter">argname</replaceable></term> |
| |
| <listitem> |
| <para> |
| The name of an argument. Some languages (currently only PL/pgSQL) let |
| you use the name in the function body. For other languages the |
| name of an input argument is just extra documentation. But the name |
| of an output argument is significant, since it defines the column |
| name in the result row type. (If you omit the name for an output |
| argument, the system will choose a default column name.) |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="parameter">argtype</replaceable></term> |
| |
| <listitem> |
| <para> |
| The data type(s) of the function's arguments (optionally |
| schema-qualified), if any. The argument types may be base, composite, |
| or domain types, or may reference the type of a table column. |
| </para> |
| <para> |
| Depending on the implementation language it may also be allowed |
| to specify <quote>pseudotypes</> such as <type>cstring</>. |
| Pseudotypes indicate that the actual argument type is either |
| incompletely specified, or outside the set of ordinary SQL data types. |
| </para> |
| <para> |
| The type of a column is referenced by writing |
| <literal><replaceable |
| class="parameter">tablename</replaceable>.<replaceable |
| class="parameter">columnname</replaceable>%TYPE</literal>. |
| Using this feature can sometimes help make a function independent of |
| changes to the definition of a table. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="parameter">rettype</replaceable></term> |
| |
| <listitem> |
| <para> |
| The return data type (optionally schema-qualified). The return type |
| may be a base, composite, or domain type, |
| or may reference the type of a table column. |
| Depending on the implementation language it may also be allowed |
| to specify <quote>pseudotypes</> such as <type>cstring</>. |
| If the function is not supposed to return a value, specify |
| <type>void</> as the return type. |
| </para> |
| <para> |
| When there are <literal>OUT</> or <literal>INOUT</> parameters, |
| the <literal>RETURNS</> clause may be omitted. If present, it |
| must agree with the result type implied by the output parameters: |
| <literal>RECORD</> 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">tablename</replaceable>.<replaceable |
| class="parameter">columnname</replaceable>%TYPE</literal>. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="parameter">langname</replaceable></term> |
| |
| <listitem> |
| <para> |
| The name of the language that the function is implemented in. |
| May be <literal>SQL</literal>, <literal>C</literal>, |
| <literal>internal</literal>, or the name of a user-defined |
| procedural language. For backward compatibility, |
| the name may be enclosed by single quotes. |
| </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 |
| may 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 not |
| directly present in its argument list. If this option is given, |
| any call of the function with all-constant arguments can be |
| immediately replaced with the function value. |
| </para> |
| |
| <para> |
| <literal>STABLE</literal> indicates that the function |
| cannot modify the database, |
| and that within a single table scan it will consistently |
| return the same result for the same argument values, but that its |
| result could change across SQL statements. This is the appropriate |
| selection for functions whose results depend on database lookups, |
| parameter variables (such as the current time zone), etc. Also note |
| that the <function>current_timestamp</> family of functions qualify |
| as stable, since their values do not change within a transaction. |
| </para> |
| |
| <para> |
| <literal>VOLATILE</literal> indicates that the function value can |
| change even within a single table scan, so no optimizations can be |
| made. Relatively few database functions are volatile in this sense; |
| some examples are <literal>random()</>, <literal>currval()</>, |
| <literal>timeofday()</>. But note that any function that has |
| side-effects must be classified volatile, even if its result is quite |
| predictable, to prevent calls from being optimized away; an example is |
| <literal>setval()</>. |
| </para> |
| |
| <para> |
| For additional details see <xref linkend="xfunc-volatility">. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>CALLED ON NULL INPUT</literal></term> |
| <term><literal>RETURNS NULL ON NULL INPUT</literal></term> |
| <term><literal>STRICT</literal></term> |
| |
| <listitem> |
| <para> |
| <literal>CALLED ON NULL INPUT</literal> (the default) indicates |
| that the function will be called normally when some of its |
| arguments are null. It is then the function author's |
| responsibility to check for null values if necessary and respond |
| appropriately. |
| </para> |
| |
| <para> |
| <literal>RETURNS NULL ON NULL INPUT</literal> or |
| <literal>STRICT</literal> indicates that the function always |
| returns null whenever any of its arguments are null. If this |
| parameter is specified, the function is not executed when there |
| are null arguments; instead a null result is assumed |
| automatically. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal><optional>EXTERNAL</optional> SECURITY INVOKER</literal></term> |
| <term><literal><optional>EXTERNAL</optional> SECURITY DEFINER</literal></term> |
| |
| <listitem> |
| <para> |
| <literal>SECURITY INVOKER</literal> indicates that the function |
| is to be executed with the privileges of the user that calls it. |
| That is the default. <literal>SECURITY DEFINER</literal> |
| specifies that the function is to be executed with the |
| privileges of the user that created it. |
| </para> |
| |
| <para> |
| The key word <literal>EXTERNAL</literal> is allowed for SQL |
| conformance, but it is optional since, unlike in SQL, this feature |
| applies to all functions not only external ones. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="parameter">definition</replaceable></term> |
| |
| <listitem> |
| <para> |
| A string constant defining the function; the meaning depends on the |
| language. It may be an internal function name, the path to an |
| object file, an SQL command, or text in a procedural language. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal><replaceable class="parameter">obj_file</replaceable>, <replaceable class="parameter">link_symbol</replaceable></literal></term> |
| |
| <listitem> |
| <para> |
| This form of the <literal>AS</literal> clause is used for |
| dynamically loadable C language functions when the function name |
| in the C language source code is not the same as the name of |
| the SQL function. The string <replaceable |
| class="parameter">obj_file</replaceable> is the name of the |
| file containing the dynamically loadable object, and |
| <replaceable class="parameter">link_symbol</replaceable> is the |
| function's link symbol, that is, the name of the function in the C |
| language source code. If the link symbol is omitted, it is assumed |
| to be the same as the name of the SQL function being defined. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="parameter">attribute</replaceable></term> |
| |
| <listitem> |
| <para> |
| The historical way to specify optional pieces of information |
| about the function. The following attributes may appear here: |
| |
| <variablelist> |
| <varlistentry> |
| <term><literal>isStrict</></term> |
| <listitem> |
| <para> |
| Equivalent to <literal>STRICT</literal> or <literal>RETURNS NULL ON NULL INPUT</literal>. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>isCachable</></term> |
| <listitem> |
| <para> |
| <literal>isCachable</literal> is an obsolete equivalent of |
| <literal>IMMUTABLE</literal>; it's still accepted for |
| backwards-compatibility reasons. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| </variablelist> |
| |
| Attribute names are not case-sensitive. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| </variablelist> |
| |
| </refsect1> |
| |
| <refsect1 id="sql-createfunction-notes"> |
| <title>Notes</title> |
| |
| <para> |
| Refer to <xref linkend="xfunc"> for further information on writing |
| functions. |
| </para> |
| |
| <para> |
| The full <acronym>SQL</acronym> type syntax is allowed for |
| input arguments and return value. However, some details of the |
| type specification (e.g., the precision field for |
| type <type>numeric</type>) are the responsibility of the |
| underlying function implementation and are silently swallowed |
| (i.e., not recognized or |
| enforced) by the <command>CREATE FUNCTION</command> command. |
| </para> |
| |
| <para> |
| <productname>PostgreSQL</productname> allows function |
| <firstterm>overloading</firstterm>; that is, the same name can be |
| used for several different functions so long as they have distinct |
| argument types. However, the C names of all functions must be |
| different, so you must give overloaded C functions different C |
| names (for example, use the argument types as part of the C |
| names). |
| </para> |
| |
| <para> |
| Two functions are considered the same if they have the same names and |
| <emphasis>input</> argument types, ignoring any <literal>OUT</> |
| parameters. Thus for example these declarations conflict: |
| <programlisting> |
| CREATE FUNCTION foo(int) ... |
| CREATE FUNCTION foo(int, out text) ... |
| </programlisting> |
| </para> |
| |
| <para> |
| When repeated <command>CREATE FUNCTION</command> calls refer to |
| the same object file, the file is only loaded once. To unload and |
| reload the file (perhaps during development), use the <xref |
| linkend="sql-load" endterm="sql-load-title"> command. |
| </para> |
| |
| <para> |
| Use <xref linkend="sql-dropfunction" |
| endterm="sql-dropfunction-title"> to remove user-defined |
| functions. |
| </para> |
| |
| <para> |
| It is often helpful to use dollar quoting (see <xref |
| linkend="sql-syntax-dollar-quoting">) to write the function definition |
| string, rather than the normal single quote syntax. Without dollar |
| quoting, any single quotes or backslashes in the function definition must |
| be escaped by doubling them. |
| </para> |
| |
| <para> |
| To be able to define a function, the user must have the |
| <literal>USAGE</literal> privilege on the language. |
| </para> |
| |
| </refsect1> |
| |
| <refsect1 id="sql-createfunction-examples"> |
| <title>Examples</title> |
| |
| <para> |
| Here are some trivial examples to help you get started. For more |
| information and examples, see <xref linkend="xfunc">. |
| <programlisting> |
| CREATE FUNCTION add(integer, integer) RETURNS integer |
| AS 'select $1 + $2;' |
| LANGUAGE SQL |
| IMMUTABLE |
| RETURNS NULL ON NULL INPUT; |
| </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> |
| </para> |
| </refsect1> |
| |
| <refsect1 id="sql-createfunction-security"> |
| <title>Writing <literal>SECURITY DEFINER</literal> Functions Safely</title> |
| |
| <para> |
| Because a <literal>SECURITY DEFINER</literal> function is executed |
| with the privileges of the user that created 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 that mask objects 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 had |
| by forcing the temporary schema to be searched last. To do this, |
| write <literal>pg_temp</> as the last entry in <varname>search_path</>. |
| This function illustrates safe usage: |
| </para> |
| |
| <programlisting> |
| CREATE FUNCTION check_password(uname TEXT, pass TEXT) |
| RETURNS BOOLEAN AS $$ |
| DECLARE passed BOOLEAN; |
| old_path TEXT; |
| BEGIN |
| -- Save old search_path; notice we must qualify current_setting |
| -- to ensure we invoke the right function |
| old_path := pg_catalog.current_setting('search_path'); |
| |
| -- Set a secure search_path: trusted schemas, then 'pg_temp'. |
| -- We set is_local = true so that the old value will be restored |
| -- in event of an error before we reach the function end. |
| PERFORM pg_catalog.set_config('search_path', 'admin, pg_temp', true); |
| |
| -- Do whatever secure work we came for. |
| SELECT (pwd = $2) INTO passed |
| FROM pwds |
| WHERE username = $1; |
| |
| -- Restore caller's search_path |
| PERFORM pg_catalog.set_config('search_path', old_path, true); |
| |
| RETURN passed; |
| END; |
| $$ LANGUAGE plpgsql SECURITY DEFINER; |
| </programlisting> |
| |
| </refsect1> |
| |
| |
| <refsect1 id="sql-createfunction-compat"> |
| <title>Compatibility</title> |
| |
| <para> |
| A <command>CREATE FUNCTION</command> command is defined in SQL:1999 and later. |
| The <productname>PostgreSQL</productname> version is similar but |
| not fully compatible. The attributes are not portable, neither are the |
| different available languages. |
| </para> |
| |
| <para> |
| For compatibility with some other database systems, |
| <replaceable class="parameter">argmode</replaceable> can be written |
| either before or after <replaceable class="parameter">argname</replaceable>. |
| But only the first way is standard-compliant. |
| </para> |
| </refsect1> |
| |
| |
| <refsect1> |
| <title>See Also</title> |
| |
| <simplelist type="inline"> |
| <member><xref linkend="sql-alterfunction" endterm="sql-alterfunction-title"></member> |
| <member><xref linkend="sql-dropfunction" endterm="sql-dropfunction-title"></member> |
| <member><xref linkend="sql-grant" endterm="sql-grant-title"></member> |
| <member><xref linkend="sql-load" endterm="sql-load-title"></member> |
| <member><xref linkend="sql-revoke" endterm="sql-revoke-title"></member> |
| <member><xref linkend="app-createlang" endterm="app-createlang-title"></member> |
| </simplelist> |
| </refsect1> |
| |
| </refentry> |