2 doc/src/sgml/ref/create_function.sgml
5 <refentry id="SQL-CREATEFUNCTION">
7 <refentrytitle>CREATE FUNCTION</refentrytitle>
8 <manvolnum>7</manvolnum>
9 <refmiscinfo>SQL - Language Statements</refmiscinfo>
13 <refname>CREATE FUNCTION</refname>
14 <refpurpose>define a new function</refpurpose>
17 <indexterm zone="sql-createfunction">
18 <primary>CREATE FUNCTION</primary>
23 CREATE [ OR REPLACE ] FUNCTION
24 <replaceable class="parameter">name</replaceable> ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">argtype</replaceable> [ { DEFAULT | = } <replaceable class="parameter">default_expr</replaceable> ] [, ...] ] )
25 [ RETURNS <replaceable class="parameter">rettype</replaceable>
26 | RETURNS TABLE ( <replaceable class="parameter">column_name</replaceable> <replaceable class="parameter">column_type</replaceable> [, ...] ) ]
27 { LANGUAGE <replaceable class="parameter">lang_name</replaceable>
29 | IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF
30 | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
31 | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
32 | COST <replaceable class="parameter">execution_cost</replaceable>
33 | ROWS <replaceable class="parameter">result_rows</replaceable>
34 | SET <replaceable class="parameter">configuration_parameter</replaceable> { TO <replaceable class="parameter">value</replaceable> | = <replaceable class="parameter">value</replaceable> | FROM CURRENT }
35 | AS '<replaceable class="parameter">definition</replaceable>'
36 | AS '<replaceable class="parameter">obj_file</replaceable>', '<replaceable class="parameter">link_symbol</replaceable>'
38 [ WITH ( <replaceable class="parameter">attribute</replaceable> [, ...] ) ]
42 <refsect1 id="sql-createfunction-description">
43 <title>Description</title>
46 <command>CREATE FUNCTION</command> defines a new function.
47 <command>CREATE OR REPLACE FUNCTION</command> will either create a
48 new function, or replace an existing definition.
49 To be able to define a function, the user must have the
50 <literal>USAGE</literal> privilege on the language.
54 If a schema name is included, then the function is created in the
55 specified schema. Otherwise it is created in the current schema.
56 The name of the new function must not match any existing function
57 with the same input argument types in the same schema. However,
58 functions of different argument types can share a name (this is
59 called <firstterm>overloading</>).
63 To replace the current definition of an existing function, use
64 <command>CREATE OR REPLACE FUNCTION</command>. It is not possible
65 to change the name or argument types of a function this way (if you
66 tried, you would actually be creating a new, distinct function).
67 Also, <command>CREATE OR REPLACE FUNCTION</command> will not let
68 you change the return type of an existing function. To do that,
69 you must drop and recreate the function. (When using <literal>OUT</>
70 parameters, that means you cannot change the types of any
71 <literal>OUT</> parameters except by dropping the function.)
75 When <command>CREATE OR REPLACE FUNCTION</> is used to replace an
76 existing function, the ownership and permissions of the function
77 do not change. All other function properties are assigned the
78 values specified or implied in the command. You must own the function
79 to replace it (this includes being a member of the owning role).
83 If you drop and then recreate a function, the new function is not
84 the same entity as the old; you will have to drop existing rules, views,
85 triggers, etc. that refer to the old function. Use
86 <command>CREATE OR REPLACE FUNCTION</command> to change a function
87 definition without breaking objects that refer to the function.
88 Also, <command>ALTER FUNCTION</> can be used to change most of the
89 auxiliary properties of an existing function.
93 The user that creates the function becomes the owner of the function.
97 To be able to create a function, you must have <literal>USAGE</literal>
98 privilege on the argument types and the return type.
103 <title>Parameters</title>
108 <term><replaceable class="parameter">name</replaceable></term>
112 The name (optionally schema-qualified) of the function to create.
118 <term><replaceable class="parameter">argmode</replaceable></term>
122 The mode of an argument: <literal>IN</>, <literal>OUT</>,
123 <literal>INOUT</>, or <literal>VARIADIC</>.
124 If omitted, the default is <literal>IN</>.
125 Only <literal>OUT</> arguments can follow a <literal>VARIADIC</> one.
126 Also, <literal>OUT</> and <literal>INOUT</> arguments cannot be used
127 together with the <literal>RETURNS TABLE</> notation.
133 <term><replaceable class="parameter">argname</replaceable></term>
137 The name of an argument. Some languages (currently only PL/pgSQL) let
138 you use the name in the function body. For other languages the
139 name of an input argument is just extra documentation, so far as
140 the function itself is concerned; but you can use input argument names
141 when calling a function to improve readability (see <xref
142 linkend="sql-syntax-calling-funcs">). In any case, the name
143 of an output argument is significant, because it defines the column
144 name in the result row type. (If you omit the name for an output
145 argument, the system will choose a default column name.)
151 <term><replaceable class="parameter">argtype</replaceable></term>
155 The data type(s) of the function's arguments (optionally
156 schema-qualified), if any. The argument types can be base, composite,
157 or domain types, or can reference the type of a table column.
160 Depending on the implementation language it might also be allowed
161 to specify <quote>pseudotypes</> such as <type>cstring</>.
162 Pseudotypes indicate that the actual argument type is either
163 incompletely specified, or outside the set of ordinary SQL data types.
166 The type of a column is referenced by writing
167 <literal><replaceable
168 class="parameter">table_name</replaceable>.<replaceable
169 class="parameter">column_name</replaceable>%TYPE</literal>.
170 Using this feature can sometimes help make a function independent of
171 changes to the definition of a table.
177 <term><replaceable class="parameter">default_expr</replaceable></term>
181 An expression to be used as default value if the parameter is
182 not specified. The expression has to be coercible to the
183 argument type of the parameter.
184 Only input (including <literal>INOUT</>) parameters can have a default
185 value. All input parameters following a
186 parameter with a default value must have default values as well.
192 <term><replaceable class="parameter">rettype</replaceable></term>
196 The return data type (optionally schema-qualified). The return type
197 can be a base, composite, or domain type,
198 or can reference the type of a table column.
199 Depending on the implementation language it might also be allowed
200 to specify <quote>pseudotypes</> such as <type>cstring</>.
201 If the function is not supposed to return a value, specify
202 <type>void</> as the return type.
205 When there are <literal>OUT</> or <literal>INOUT</> parameters,
206 the <literal>RETURNS</> clause can be omitted. If present, it
207 must agree with the result type implied by the output parameters:
208 <literal>RECORD</> if there are multiple output parameters, or
209 the same type as the single output parameter.
212 The <literal>SETOF</literal>
213 modifier indicates that the function will return a set of
214 items, rather than a single item.
217 The type of a column is referenced by writing
218 <literal><replaceable
219 class="parameter">table_name</replaceable>.<replaceable
220 class="parameter">column_name</replaceable>%TYPE</literal>.
226 <term><replaceable class="parameter">column_name</replaceable></term>
230 The name of an output column in the <literal>RETURNS TABLE</>
231 syntax. This is effectively another way of declaring a named
232 <literal>OUT</> parameter, except that <literal>RETURNS TABLE</>
233 also implies <literal>RETURNS SETOF</>.
239 <term><replaceable class="parameter">column_type</replaceable></term>
243 The data type of an output column in the <literal>RETURNS TABLE</>
250 <term><replaceable class="parameter">lang_name</replaceable></term>
254 The name of the language that the function is implemented in.
255 Can be <literal>SQL</literal>, <literal>C</literal>,
256 <literal>internal</literal>, or the name of a user-defined
257 procedural language. For backward compatibility,
258 the name can be enclosed by single quotes.
264 <term><literal>WINDOW</literal></term>
267 <para><literal>WINDOW</literal> indicates that the function is a
268 <firstterm>window function</> rather than a plain function.
269 This is currently only useful for functions written in C.
270 The <literal>WINDOW</> attribute cannot be changed when
271 replacing an existing function definition.
277 <term><literal>IMMUTABLE</literal></term>
278 <term><literal>STABLE</literal></term>
279 <term><literal>VOLATILE</literal></term>
283 These attributes inform the query optimizer about the behavior
284 of the function. At most one choice
285 can be specified. If none of these appear,
286 <literal>VOLATILE</literal> is the default assumption.
289 <para><literal>IMMUTABLE</literal> indicates that the function
290 cannot modify the database and always
291 returns the same result when given the same argument values; that
292 is, it does not do database lookups or otherwise use information not
293 directly present in its argument list. If this option is given,
294 any call of the function with all-constant arguments can be
295 immediately replaced with the function value.
298 <para><literal>STABLE</literal> indicates that the function
299 cannot modify the database,
300 and that within a single table scan it will consistently
301 return the same result for the same argument values, but that its
302 result could change across SQL statements. This is the appropriate
303 selection for functions whose results depend on database lookups,
304 parameter variables (such as the current time zone), etc. (It is
305 inappropriate for <literal>AFTER</> triggers that wish to
306 query rows modified by the current command.) Also note
307 that the <function>current_timestamp</> family of functions qualify
308 as stable, since their values do not change within a transaction.
311 <para><literal>VOLATILE</literal> indicates that the function value can
312 change even within a single table scan, so no optimizations can be
313 made. Relatively few database functions are volatile in this sense;
314 some examples are <literal>random()</>, <literal>currval()</>,
315 <literal>timeofday()</>. But note that any function that has
316 side-effects must be classified volatile, even if its result is quite
317 predictable, to prevent calls from being optimized away; an example is
318 <literal>setval()</>.
322 For additional details see <xref linkend="xfunc-volatility">.
328 <term><literal>LEAKPROOF</literal></term>
331 <literal>LEAKPROOF</literal> indicates that the function has no side
332 effects. It reveals no information about its arguments other than by
333 its return value. For example, a function which throws an error message
334 for some argument values but not others, or which includes the argument
335 values in any error message, is not leakproof. The query planner may
336 push leakproof functions (but not others) into views created with the
337 <literal>security_barrier</literal> option. See
338 <xref linkend="sql-createview"> and <xref linkend="rules-privileges">.
339 This option can only be set by the superuser.
345 <term><literal>CALLED ON NULL INPUT</literal></term>
346 <term><literal>RETURNS NULL ON NULL INPUT</literal></term>
347 <term><literal>STRICT</literal></term>
350 <para><literal>CALLED ON NULL INPUT</literal> (the default) indicates
351 that the function will be called normally when some of its
352 arguments are null. It is then the function author's
353 responsibility to check for null values if necessary and respond
357 <para><literal>RETURNS NULL ON NULL INPUT</literal> or
358 <literal>STRICT</literal> indicates that the function always
359 returns null whenever any of its arguments are null. If this
360 parameter is specified, the function is not executed when there
361 are null arguments; instead a null result is assumed
368 <term><literal><optional>EXTERNAL</optional> SECURITY INVOKER</literal></term>
369 <term><literal><optional>EXTERNAL</optional> SECURITY DEFINER</literal></term>
372 <para><literal>SECURITY INVOKER</literal> indicates that the function
373 is to be executed with the privileges of the user that calls it.
374 That is the default. <literal>SECURITY DEFINER</literal>
375 specifies that the function is to be executed with the
376 privileges of the user that created it.
380 The key word <literal>EXTERNAL</literal> is allowed for SQL
381 conformance, but it is optional since, unlike in SQL, this feature
382 applies to all functions not only external ones.
388 <term><replaceable class="parameter">execution_cost</replaceable></term>
392 A positive number giving the estimated execution cost for the function,
393 in units of <xref linkend="guc-cpu-operator-cost">. If the function
394 returns a set, this is the cost per returned row. If the cost is
395 not specified, 1 unit is assumed for C-language and internal functions,
396 and 100 units for functions in all other languages. Larger values
397 cause the planner to try to avoid evaluating the function more often
404 <term><replaceable class="parameter">result_rows</replaceable></term>
408 A positive number giving the estimated number of rows that the planner
409 should expect the function to return. This is only allowed when the
410 function is declared to return a set. The default assumption is
417 <term><replaceable>configuration_parameter</replaceable></term>
418 <term><replaceable>value</replaceable></term>
421 The <literal>SET</> clause causes the specified configuration
422 parameter to be set to the specified value when the function is
423 entered, and then restored to its prior value when the function exits.
424 <literal>SET FROM CURRENT</> saves the session's current value of
425 the parameter as the value to be applied when the function is entered.
429 If a <literal>SET</> clause is attached to a function, then
430 the effects of a <command>SET LOCAL</> command executed inside the
431 function for the same variable are restricted to the function: the
432 configuration parameter's prior value is still restored at function exit.
434 <command>SET</> command (without <literal>LOCAL</>) overrides the
435 <literal>SET</> clause, much as it would do for a previous <command>SET
436 LOCAL</> command: the effects of such a command will persist after
437 function exit, unless the current transaction is rolled back.
441 See <xref linkend="sql-set"> and
442 <xref linkend="runtime-config">
443 for more information about allowed parameter names and values.
449 <term><replaceable class="parameter">definition</replaceable></term>
453 A string constant defining the function; the meaning depends on the
454 language. It can be an internal function name, the path to an
455 object file, an SQL command, or text in a procedural language.
459 It is often helpful to use dollar quoting (see <xref
460 linkend="sql-syntax-dollar-quoting">) to write the function definition
461 string, rather than the normal single quote syntax. Without dollar
462 quoting, any single quotes or backslashes in the function definition must
463 be escaped by doubling them.
470 <term><literal><replaceable class="parameter">obj_file</replaceable>, <replaceable class="parameter">link_symbol</replaceable></literal></term>
474 This form of the <literal>AS</literal> clause is used for
475 dynamically loadable C language functions when the function name
476 in the C language source code is not the same as the name of
477 the SQL function. The string <replaceable
478 class="parameter">obj_file</replaceable> is the name of the
479 file containing the dynamically loadable object, and
480 <replaceable class="parameter">link_symbol</replaceable> is the
481 function's link symbol, that is, the name of the function in the C
482 language source code. If the link symbol is omitted, it is assumed
483 to be the same as the name of the SQL function being defined.
487 When repeated <command>CREATE FUNCTION</command> calls refer to
488 the same object file, the file is only loaded once per session.
490 reload the file (perhaps during development), start a new session.
497 <term><replaceable class="parameter">attribute</replaceable></term>
501 The historical way to specify optional pieces of information
502 about the function. The following attributes can appear here:
506 <term><literal>isStrict</></term>
509 Equivalent to <literal>STRICT</literal> or <literal>RETURNS NULL ON NULL INPUT</literal>.
515 <term><literal>isCachable</></term>
517 <para><literal>isCachable</literal> is an obsolete equivalent of
518 <literal>IMMUTABLE</literal>; it's still accepted for
519 backwards-compatibility reasons.
526 Attribute names are not case-sensitive.
534 Refer to <xref linkend="xfunc"> for further information on writing
540 <refsect1 id="sql-createfunction-overloading">
541 <title>Overloading</title>
544 <productname>PostgreSQL</productname> allows function
545 <firstterm>overloading</firstterm>; that is, the same name can be
546 used for several different functions so long as they have distinct
547 input argument types. However, the C names of all functions must be
548 different, so you must give overloaded C functions different C
549 names (for example, use the argument types as part of the C
554 Two functions are considered the same if they have the same names and
555 <emphasis>input</> argument types, ignoring any <literal>OUT</>
556 parameters. Thus for example these declarations conflict:
558 CREATE FUNCTION foo(int) ...
559 CREATE FUNCTION foo(int, out text) ...
564 Functions that have different argument type lists will not be considered
565 to conflict at creation time, but if defaults are provided they might
566 conflict in use. For example, consider
568 CREATE FUNCTION foo(int) ...
569 CREATE FUNCTION foo(int, int default 42) ...
571 A call <literal>foo(10)</> will fail due to the ambiguity about which
572 function should be called.
577 <refsect1 id="sql-createfunction-notes">
581 The full <acronym>SQL</acronym> type syntax is allowed for
582 input arguments and return value. However, some details of the
583 type specification (e.g., the precision field for
584 type <type>numeric</type>) are the responsibility of the
585 underlying function implementation and are silently swallowed
586 (i.e., not recognized or
587 enforced) by the <command>CREATE FUNCTION</command> command.
591 When replacing an existing function with <command>CREATE OR REPLACE
592 FUNCTION</>, there are restrictions on changing parameter names.
593 You cannot change the name already assigned to any input parameter
594 (although you can add names to parameters that had none before).
595 If there is more than one output parameter, you cannot change the
596 names of the output parameters, because that would change the
597 column names of the anonymous composite type that describes the
598 function's result. These restrictions are made to ensure that
599 existing calls of the function do not stop working when it is replaced.
603 If a function is declared <literal>STRICT</> with a <literal>VARIADIC</>
604 argument, the strictness check tests that the variadic array <emphasis>as
605 a whole</> is non-null. The function will still be called if the
606 array has null elements.
611 <refsect1 id="sql-createfunction-examples">
612 <title>Examples</title>
615 Here are some trivial examples to help you get started. For more
616 information and examples, see <xref linkend="xfunc">.
618 CREATE FUNCTION add(integer, integer) RETURNS integer
622 RETURNS NULL ON NULL INPUT;
627 Increment an integer, making use of an argument name, in
628 <application>PL/pgSQL</application>:
630 CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$
639 Return a record containing multiple output parameters:
641 CREATE FUNCTION dup(in int, out f1 int, out f2 text)
642 AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
645 SELECT * FROM dup(42);
647 You can do the same thing more verbosely with an explicitly named
650 CREATE TYPE dup_result AS (f1 int, f2 text);
652 CREATE FUNCTION dup(int) RETURNS dup_result
653 AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
656 SELECT * FROM dup(42);
658 Another way to return multiple columns is to use a <literal>TABLE</>
661 CREATE FUNCTION dup(int) RETURNS TABLE(f1 int, f2 text)
662 AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
665 SELECT * FROM dup(42);
667 However, a <literal>TABLE</> function is different from the
668 preceding examples, because it actually returns a <emphasis>set</>
669 of records, not just one record.
673 <refsect1 id="sql-createfunction-security">
674 <title>Writing <literal>SECURITY DEFINER</literal> Functions Safely</title>
677 Because a <literal>SECURITY DEFINER</literal> function is executed
678 with the privileges of the user that created it, care is needed to
679 ensure that the function cannot be misused. For security,
680 <xref linkend="guc-search-path"> should be set to exclude any schemas
681 writable by untrusted users. This prevents
682 malicious users from creating objects that mask objects used by the
683 function. Particularly important in this regard is the
684 temporary-table schema, which is searched first by default, and
685 is normally writable by anyone. A secure arrangement can be had
686 by forcing the temporary schema to be searched last. To do this,
687 write <literal>pg_temp</> as the last entry in <varname>search_path</>.
688 This function illustrates safe usage:
692 CREATE FUNCTION check_password(uname TEXT, pass TEXT)
693 RETURNS BOOLEAN AS $$
694 DECLARE passed BOOLEAN;
696 SELECT (pwd = $2) INTO passed
704 -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
705 SET search_path = admin, pg_temp;
709 Before <productname>PostgreSQL</productname> version 8.3, the
710 <literal>SET</> option was not available, and so older functions may
711 contain rather complicated logic to save, set, and restore
712 <varname>search_path</>. The <literal>SET</> option is far easier
713 to use for this purpose.
717 Another point to keep in mind is that by default, execute privilege
718 is granted to <literal>PUBLIC</> for newly created functions
719 (see <xref linkend="sql-grant"> for more
720 information). Frequently you will wish to restrict use of a security
721 definer function to only some users. To do that, you must revoke
722 the default <literal>PUBLIC</> privileges and then grant execute
723 privilege selectively. To avoid having a window where the new function
724 is accessible to all, create it and set the privileges within a single
725 transaction. For example:
730 CREATE FUNCTION check_password(uname TEXT, pass TEXT) ... SECURITY DEFINER;
731 REVOKE ALL ON FUNCTION check_password(uname TEXT, pass TEXT) FROM PUBLIC;
732 GRANT EXECUTE ON FUNCTION check_password(uname TEXT, pass TEXT) TO admins;
738 <refsect1 id="sql-createfunction-compat">
739 <title>Compatibility</title>
742 A <command>CREATE FUNCTION</command> command is defined in SQL:1999 and later.
743 The <productname>PostgreSQL</productname> version is similar but
744 not fully compatible. The attributes are not portable, neither are the
745 different available languages.
749 For compatibility with some other database systems,
750 <replaceable class="parameter">argmode</replaceable> can be written
751 either before or after <replaceable class="parameter">argname</replaceable>.
752 But only the first way is standard-compliant.
756 The SQL standard does not specify parameter defaults. The syntax
757 with the <literal>DEFAULT</literal> key word is from Oracle, and it
758 is somewhat in the spirit of the standard: SQL/PSM uses it for
759 variable default values. The syntax with <literal>=</literal> is
760 used in T-SQL and Firebird.
766 <title>See Also</title>
768 <simplelist type="inline">
769 <member><xref linkend="sql-alterfunction"></member>
770 <member><xref linkend="sql-dropfunction"></member>
771 <member><xref linkend="sql-grant"></member>
772 <member><xref linkend="sql-load"></member>
773 <member><xref linkend="sql-revoke"></member>
774 <member><xref linkend="app-createlang"></member>