Debian Codegraph @

doc/src/sgml/ref/create_function.sgml

raw
1 <!--
2 doc/src/sgml/ref/create_function.sgml
3 PostgreSQL documentation
4 -->
5
6 <refentry id="sql-createfunction">
7 <indexterm zone="sql-createfunction">
8 <primary>CREATE FUNCTION</primary>
9 </indexterm>
10
11 <refmeta>
12 <refentrytitle>CREATE FUNCTION</refentrytitle>
13 <manvolnum>7</manvolnum>
14 <refmiscinfo>SQL - Language Statements</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18 <refname>CREATE FUNCTION</refname>
19 <refpurpose>define a new function</refpurpose>
20 </refnamediv>
21
22 <refsynopsisdiv>
23 <synopsis>
24 CREATE [ OR REPLACE ] FUNCTION
25 <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> ] [, ...] ] )
26 [ RETURNS <replaceable class="parameter">rettype</replaceable>
27 | RETURNS TABLE ( <replaceable class="parameter">column_name</replaceable> <replaceable class="parameter">column_type</replaceable> [, ...] ) ]
28 { LANGUAGE <replaceable class="parameter">lang_name</replaceable>
29 | TRANSFORM { FOR TYPE <replaceable class="parameter">type_name</replaceable> } [, ... ]
30 | WINDOW
31 | { IMMUTABLE | STABLE | VOLATILE }
32 | [ NOT ] LEAKPROOF
33 | { CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT }
34 | { [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER }
35 | PARALLEL { UNSAFE | RESTRICTED | SAFE }
36 | COST <replaceable class="parameter">execution_cost</replaceable>
37 | ROWS <replaceable class="parameter">result_rows</replaceable>
38 | SUPPORT <replaceable class="parameter">support_function</replaceable>
39 | SET <replaceable class="parameter">configuration_parameter</replaceable> { TO <replaceable class="parameter">value</replaceable> | = <replaceable class="parameter">value</replaceable> | FROM CURRENT }
40 | AS '<replaceable class="parameter">definition</replaceable>'
41 | AS '<replaceable class="parameter">obj_file</replaceable>', '<replaceable class="parameter">link_symbol</replaceable>'
42 | <replaceable class="parameter">sql_body</replaceable>
43 } ...
44 </synopsis>
45 </refsynopsisdiv>
46
47 <refsect1 id="sql-createfunction-description">
48 <title>Description</title>
49
50 <para>
51 <command>CREATE FUNCTION</command> defines a new function.
52 <command>CREATE OR REPLACE FUNCTION</command> will either create a
53 new function, or replace an existing definition.
54 To be able to define a function, the user must have the
55 <literal>USAGE</literal> privilege on the language.
56 </para>
57
58 <para>
59 If a schema name is included, then the function is created in the
60 specified schema. Otherwise it is created in the current schema.
61 The name of the new function must not match any existing function or procedure
62 with the same input argument types in the same schema. However,
63 functions and procedures of different argument types can share a name (this is
64 called <firstterm>overloading</firstterm>).
65 </para>
66
67 <para>
68 To replace the current definition of an existing function, use
69 <command>CREATE OR REPLACE FUNCTION</command>. It is not possible
70 to change the name or argument types of a function this way (if you
71 tried, you would actually be creating a new, distinct function).
72 Also, <command>CREATE OR REPLACE FUNCTION</command> will not let
73 you change the return type of an existing function. To do that,
74 you must drop and recreate the function. (When using <literal>OUT</literal>
75 parameters, that means you cannot change the types of any
76 <literal>OUT</literal> parameters except by dropping the function.)
77 </para>
78
79 <para>
80 When <command>CREATE OR REPLACE FUNCTION</command> is used to replace an
81 existing function, the ownership and permissions of the function
82 do not change. All other function properties are assigned the
83 values specified or implied in the command. You must own the function
84 to replace it (this includes being a member of the owning role).
85 </para>
86
87 <para>
88 If you drop and then recreate a function, the new function is not
89 the same entity as the old; you will have to drop existing rules, views,
90 triggers, etc. that refer to the old function. Use
91 <command>CREATE OR REPLACE FUNCTION</command> to change a function
92 definition without breaking objects that refer to the function.
93 Also, <command>ALTER FUNCTION</command> can be used to change most of the
94 auxiliary properties of an existing function.
95 </para>
96
97 <para>
98 The user that creates the function becomes the owner of the function.
99 </para>
100
101 <para>
102 To be able to create a function, you must have <literal>USAGE</literal>
103 privilege on the argument types and the return type.
104 </para>
105
106 <para>
107 Refer to <xref linkend="xfunc"/> for further information on writing
108 functions.
109 </para>
110 </refsect1>
111
112 <refsect1>
113 <title>Parameters</title>
114
115 <variablelist>
116
117 <varlistentry>
118 <term><replaceable class="parameter">name</replaceable></term>
119
120 <listitem>
121 <para>
122 The name (optionally schema-qualified) of the function to create.
123 </para>
124 </listitem>
125 </varlistentry>
126
127 <varlistentry>
128 <term><replaceable class="parameter">argmode</replaceable></term>
129
130 <listitem>
131 <para>
132 The mode of an argument: <literal>IN</literal>, <literal>OUT</literal>,
133 <literal>INOUT</literal>, or <literal>VARIADIC</literal>.
134 If omitted, the default is <literal>IN</literal>.
135 Only <literal>OUT</literal> arguments can follow a <literal>VARIADIC</literal> one.
136 Also, <literal>OUT</literal> and <literal>INOUT</literal> arguments cannot be used
137 together with the <literal>RETURNS TABLE</literal> notation.
138 </para>
139 </listitem>
140 </varlistentry>
141
142 <varlistentry>
143 <term><replaceable class="parameter">argname</replaceable></term>
144
145 <listitem>
146 <para>
147 The name of an argument. Some languages (including SQL and PL/pgSQL)
148 let you use the name in the function body. For other languages the
149 name of an input argument is just extra documentation, so far as
150 the function itself is concerned; but you can use input argument names
151 when calling a function to improve readability (see <xref
152 linkend="sql-syntax-calling-funcs"/>). In any case, the name
153 of an output argument is significant, because it defines the column
154 name in the result row type. (If you omit the name for an output
155 argument, the system will choose a default column name.)
156 </para>
157 </listitem>
158 </varlistentry>
159
160 <varlistentry>
161 <term><replaceable class="parameter">argtype</replaceable></term>
162
163 <listitem>
164 <para>
165 The data type(s) of the function's arguments (optionally
166 schema-qualified), if any. The argument types can be base, composite,
167 or domain types, or can reference the type of a table column.
168 </para>
169 <para>
170 Depending on the implementation language it might also be allowed
171 to specify <quote>pseudo-types</quote> such as <type>cstring</type>.
172 Pseudo-types indicate that the actual argument type is either
173 incompletely specified, or outside the set of ordinary SQL data types.
174 </para>
175 <para>
176 The type of a column is referenced by writing
177 <literal><replaceable
178 class="parameter">table_name</replaceable>.<replaceable
179 class="parameter">column_name</replaceable>%TYPE</literal>.
180 Using this feature can sometimes help make a function independent of
181 changes to the definition of a table.
182 </para>
183 </listitem>
184 </varlistentry>
185
186 <varlistentry>
187 <term><replaceable class="parameter">default_expr</replaceable></term>
188
189 <listitem>
190 <para>
191 An expression to be used as default value if the parameter is
192 not specified. The expression has to be coercible to the
193 argument type of the parameter.
194 Only input (including <literal>INOUT</literal>) parameters can have a default
195 value. All input parameters following a
196 parameter with a default value must have default values as well.
197 </para>
198 </listitem>
199 </varlistentry>
200
201 <varlistentry>
202 <term><replaceable class="parameter">rettype</replaceable></term>
203
204 <listitem>
205 <para>
206 The return data type (optionally schema-qualified). The return type
207 can be a base, composite, or domain type,
208 or can reference the type of a table column.
209 Depending on the implementation language it might also be allowed
210 to specify <quote>pseudo-types</quote> such as <type>cstring</type>.
211 If the function is not supposed to return a value, specify
212 <type>void</type> as the return type.
213 </para>
214 <para>
215 When there are <literal>OUT</literal> or <literal>INOUT</literal> parameters,
216 the <literal>RETURNS</literal> clause can be omitted. If present, it
217 must agree with the result type implied by the output parameters:
218 <literal>RECORD</literal> if there are multiple output parameters, or
219 the same type as the single output parameter.
220 </para>
221 <para>
222 The <literal>SETOF</literal>
223 modifier indicates that the function will return a set of
224 items, rather than a single item.
225 </para>
226 <para>
227 The type of a column is referenced by writing
228 <literal><replaceable
229 class="parameter">table_name</replaceable>.<replaceable
230 class="parameter">column_name</replaceable>%TYPE</literal>.
231 </para>
232 </listitem>
233 </varlistentry>
234
235 <varlistentry>
236 <term><replaceable class="parameter">column_name</replaceable></term>
237
238 <listitem>
239 <para>
240 The name of an output column in the <literal>RETURNS TABLE</literal>
241 syntax. This is effectively another way of declaring a named
242 <literal>OUT</literal> parameter, except that <literal>RETURNS TABLE</literal>
243 also implies <literal>RETURNS SETOF</literal>.
244 </para>
245 </listitem>
246 </varlistentry>
247
248 <varlistentry>
249 <term><replaceable class="parameter">column_type</replaceable></term>
250
251 <listitem>
252 <para>
253 The data type of an output column in the <literal>RETURNS TABLE</literal>
254 syntax.
255 </para>
256 </listitem>
257 </varlistentry>
258
259 <varlistentry>
260 <term><replaceable class="parameter">lang_name</replaceable></term>
261
262 <listitem>
263 <para>
264 The name of the language that the function is implemented in.
265 It can be <literal>sql</literal>, <literal>c</literal>,
266 <literal>internal</literal>, or the name of a user-defined
267 procedural language, e.g., <literal>plpgsql</literal>. The default is
268 <literal>sql</literal> if <replaceable
269 class="parameter">sql_body</replaceable> is specified. Enclosing the
270 name in single quotes is deprecated and requires matching case.
271 </para>
272 </listitem>
273 </varlistentry>
274
275 <varlistentry>
276 <term><literal>TRANSFORM { FOR TYPE <replaceable class="parameter">type_name</replaceable> } [, ... ] }</literal></term>
277
278 <listitem>
279 <para>
280 Lists which transforms a call to the function should apply. Transforms
281 convert between SQL types and language-specific data types;
282 see <xref linkend="sql-createtransform"/>. Procedural language
283 implementations usually have hardcoded knowledge of the built-in types,
284 so those don't need to be listed here. If a procedural language
285 implementation does not know how to handle a type and no transform is
286 supplied, it will fall back to a default behavior for converting data
287 types, but this depends on the implementation.
288 </para>
289 </listitem>
290 </varlistentry>
291
292 <varlistentry>
293 <term><literal>WINDOW</literal></term>
294
295 <listitem>
296 <para><literal>WINDOW</literal> indicates that the function is a
297 <firstterm>window function</firstterm> rather than a plain function.
298 This is currently only useful for functions written in C.
299 The <literal>WINDOW</literal> attribute cannot be changed when
300 replacing an existing function definition.
301 </para>
302 </listitem>
303 </varlistentry>
304
305 <varlistentry>
306 <term><literal>IMMUTABLE</literal></term>
307 <term><literal>STABLE</literal></term>
308 <term><literal>VOLATILE</literal></term>
309
310 <listitem>
311 <para>
312 These attributes inform the query optimizer about the behavior
313 of the function. At most one choice
314 can be specified. If none of these appear,
315 <literal>VOLATILE</literal> is the default assumption.
316 </para>
317
318 <para><literal>IMMUTABLE</literal> indicates that the function
319 cannot modify the database and always
320 returns the same result when given the same argument values; that
321 is, it does not do database lookups or otherwise use information not
322 directly present in its argument list. If this option is given,
323 any call of the function with all-constant arguments can be
324 immediately replaced with the function value.
325 </para>
326
327 <para><literal>STABLE</literal> indicates that the function
328 cannot modify the database,
329 and that within a single table scan it will consistently
330 return the same result for the same argument values, but that its
331 result could change across SQL statements. This is the appropriate
332 selection for functions whose results depend on database lookups,
333 parameter variables (such as the current time zone), etc. (It is
334 inappropriate for <literal>AFTER</literal> triggers that wish to
335 query rows modified by the current command.) Also note
336 that the <function>current_timestamp</function> family of functions qualify
337 as stable, since their values do not change within a transaction.
338 </para>
339
340 <para><literal>VOLATILE</literal> indicates that the function value can
341 change even within a single table scan, so no optimizations can be
342 made. Relatively few database functions are volatile in this sense;
343 some examples are <literal>random()</literal>, <literal>currval()</literal>,
344 <literal>timeofday()</literal>. But note that any function that has
345 side-effects must be classified volatile, even if its result is quite
346 predictable, to prevent calls from being optimized away; an example is
347 <literal>setval()</literal>.
348 </para>
349
350 <para>
351 For additional details see <xref linkend="xfunc-volatility"/>.
352 </para>
353 </listitem>
354 </varlistentry>
355
356 <varlistentry>
357 <term><literal>LEAKPROOF</literal></term>
358 <listitem>
359 <para>
360 <literal>LEAKPROOF</literal> indicates that the function has no side
361 effects. It reveals no information about its arguments other than by
362 its return value. For example, a function which throws an error message
363 for some argument values but not others, or which includes the argument
364 values in any error message, is not leakproof. This affects how the
365 system executes queries against views created with the
366 <literal>security_barrier</literal> option or tables with row level
367 security enabled. The system will enforce conditions from security
368 policies and security barrier views before any user-supplied conditions
369 from the query itself that contain non-leakproof functions, in order to
370 prevent the inadvertent exposure of data. Functions and operators
371 marked as leakproof are assumed to be trustworthy, and may be executed
372 before conditions from security policies and security barrier views.
373 In addition, functions which do not take arguments or which are not
374 passed any arguments from the security barrier view or table do not have
375 to be marked as leakproof to be executed before security conditions. See
376 <xref linkend="sql-createview"/> and <xref linkend="rules-privileges"/>.
377 This option can only be set by the superuser.
378 </para>
379 </listitem>
380 </varlistentry>
381
382 <varlistentry>
383 <term><literal>CALLED ON NULL INPUT</literal></term>
384 <term><literal>RETURNS NULL ON NULL INPUT</literal></term>
385 <term><literal>STRICT</literal></term>
386
387 <listitem>
388 <para><literal>CALLED ON NULL INPUT</literal> (the default) indicates
389 that the function will be called normally when some of its
390 arguments are null. It is then the function author's
391 responsibility to check for null values if necessary and respond
392 appropriately.
393 </para>
394
395 <para><literal>RETURNS NULL ON NULL INPUT</literal> or
396 <literal>STRICT</literal> indicates that the function always
397 returns null whenever any of its arguments are null. If this
398 parameter is specified, the function is not executed when there
399 are null arguments; instead a null result is assumed
400 automatically.
401 </para>
402 </listitem>
403 </varlistentry>
404
405 <varlistentry>
406 <term><literal><optional>EXTERNAL</optional> SECURITY INVOKER</literal></term>
407 <term><literal><optional>EXTERNAL</optional> SECURITY DEFINER</literal></term>
408
409 <listitem>
410 <para><literal>SECURITY INVOKER</literal> indicates that the function
411 is to be executed with the privileges of the user that calls it.
412 That is the default. <literal>SECURITY DEFINER</literal>
413 specifies that the function is to be executed with the
414 privileges of the user that owns it. For information on how to
415 write <literal>SECURITY DEFINER</literal> functions safely,
416 <link linkend="sql-createfunction-security">see below</link>.
417 </para>
418
419 <para>
420 The key word <literal>EXTERNAL</literal> is allowed for SQL
421 conformance, but it is optional since, unlike in SQL, this feature
422 applies to all functions not only external ones.
423 </para>
424 </listitem>
425 </varlistentry>
426
427 <varlistentry>
428 <term><literal>PARALLEL</literal></term>
429
430 <listitem>
431 <para>
432 <literal>PARALLEL UNSAFE</literal> indicates that the function
433 can't be executed in parallel mode; the presence of such a
434 function in an SQL statement forces a serial execution plan. This is
435 the default. <literal>PARALLEL RESTRICTED</literal> indicates that
436 the function can be executed in parallel mode, but only in the parallel
437 group leader process. <literal>PARALLEL SAFE</literal>
438 indicates that the function is safe to run in parallel mode without
439 restriction, including in parallel worker processes.
440 </para>
441
442 <para>
443 Functions should be labeled parallel unsafe if they modify any database
444 state, change the transaction state (other than by using a
445 subtransaction for error recovery), access sequences (e.g., by
446 calling <literal>currval</literal>) or make persistent changes to
447 settings. They should
448 be labeled parallel restricted if they access temporary tables,
449 client connection state, cursors, prepared statements, or miscellaneous
450 backend-local state which the system cannot synchronize in parallel mode
451 (e.g., <literal>setseed</literal> cannot be executed other than by the group
452 leader because a change made by another process would not be reflected
453 in the leader). In general, if a function is labeled as being safe when
454 it is restricted or unsafe, or if it is labeled as being restricted when
455 it is in fact unsafe, it may throw errors or produce wrong answers
456 when used in a parallel query. C-language functions could in theory
457 exhibit totally undefined behavior if mislabeled, since there is no way
458 for the system to protect itself against arbitrary C code, but in most
459 likely cases the result will be no worse than for any other function.
460 If in doubt, functions should be labeled as <literal>UNSAFE</literal>, which is
461 the default.
462 </para>
463 </listitem>
464 </varlistentry>
465
466 <varlistentry>
467 <term><literal>COST</literal> <replaceable class="parameter">execution_cost</replaceable></term>
468
469 <listitem>
470 <para>
471 A positive number giving the estimated execution cost for the function,
472 in units of <xref linkend="guc-cpu-operator-cost"/>. If the function
473 returns a set, this is the cost per returned row. If the cost is
474 not specified, 1 unit is assumed for C-language and internal functions,
475 and 100 units for functions in all other languages. Larger values
476 cause the planner to try to avoid evaluating the function more often
477 than necessary.
478 </para>
479 </listitem>
480 </varlistentry>
481
482 <varlistentry>
483 <term><literal>ROWS</literal> <replaceable class="parameter">result_rows</replaceable></term>
484
485 <listitem>
486 <para>
487 A positive number giving the estimated number of rows that the planner
488 should expect the function to return. This is only allowed when the
489 function is declared to return a set. The default assumption is
490 1000 rows.
491 </para>
492 </listitem>
493 </varlistentry>
494
495 <varlistentry>
496 <term><literal>SUPPORT</literal> <replaceable class="parameter">support_function</replaceable></term>
497
498 <listitem>
499 <para>
500 The name (optionally schema-qualified) of a <firstterm>planner support
501 function</firstterm> to use for this function. See
502 <xref linkend="xfunc-optimization"/> for details.
503 You must be superuser to use this option.
504 </para>
505 </listitem>
506 </varlistentry>
507
508 <varlistentry>
509 <term><replaceable>configuration_parameter</replaceable></term>
510 <term><replaceable>value</replaceable></term>
511 <listitem>
512 <para>
513 The <literal>SET</literal> clause causes the specified configuration
514 parameter to be set to the specified value when the function is
515 entered, and then restored to its prior value when the function exits.
516 <literal>SET FROM CURRENT</literal> saves the value of the parameter that
517 is current when <command>CREATE FUNCTION</command> is executed as the value
518 to be applied when the function is entered.
519 </para>
520
521 <para>
522 If a <literal>SET</literal> clause is attached to a function, then
523 the effects of a <command>SET LOCAL</command> command executed inside the
524 function for the same variable are restricted to the function: the
525 configuration parameter's prior value is still restored at function exit.
526 However, an ordinary
527 <command>SET</command> command (without <literal>LOCAL</literal>) overrides the
528 <literal>SET</literal> clause, much as it would do for a previous <command>SET
529 LOCAL</command> command: the effects of such a command will persist after
530 function exit, unless the current transaction is rolled back.
531 </para>
532
533 <para>
534 See <xref linkend="sql-set"/> and
535 <xref linkend="runtime-config"/>
536 for more information about allowed parameter names and values.
537 </para>
538 </listitem>
539 </varlistentry>
540
541 <varlistentry>
542 <term><replaceable class="parameter">definition</replaceable></term>
543
544 <listitem>
545 <para>
546 A string constant defining the function; the meaning depends on the
547 language. It can be an internal function name, the path to an
548 object file, an SQL command, or text in a procedural language.
549 </para>
550
551 <para>
552 It is often helpful to use dollar quoting (see <xref
553 linkend="sql-syntax-dollar-quoting"/>) to write the function definition
554 string, rather than the normal single quote syntax. Without dollar
555 quoting, any single quotes or backslashes in the function definition must
556 be escaped by doubling them.
557 </para>
558
559 </listitem>
560 </varlistentry>
561
562 <varlistentry>
563 <term><literal><replaceable class="parameter">obj_file</replaceable>, <replaceable class="parameter">link_symbol</replaceable></literal></term>
564
565 <listitem>
566 <para>
567 This form of the <literal>AS</literal> clause is used for
568 dynamically loadable C language functions when the function name
569 in the C language source code is not the same as the name of
570 the SQL function. The string <replaceable
571 class="parameter">obj_file</replaceable> is the name of the shared
572 library file containing the compiled C function, and is interpreted
573 as for the <link linkend="sql-load"><command>LOAD</command></link> command. The string
574 <replaceable class="parameter">link_symbol</replaceable> is the
575 function's link symbol, that is, the name of the function in the C
576 language source code. If the link symbol is omitted, it is assumed to
577 be the same as the name of the SQL function being defined. The C names
578 of all functions must be different, so you must give overloaded C
579 functions different C names (for example, use the argument types as
580 part of the C names).
581 </para>
582
583 <para>
584 When repeated <command>CREATE FUNCTION</command> calls refer to
585 the same object file, the file is only loaded once per session.
586 To unload and
587 reload the file (perhaps during development), start a new session.
588 </para>
589
590 </listitem>
591 </varlistentry>
592
593 <varlistentry>
594 <term><replaceable class="parameter">sql_body</replaceable></term>
595
596 <listitem>
597 <para>
598 The body of a <literal>LANGUAGE SQL</literal> function. This can
599 either be a single statement
600 <programlisting>
601 RETURN <replaceable>expression</replaceable>
602 </programlisting>
603 or a block
604 <programlisting>
605 BEGIN ATOMIC
606 <replaceable>statement</replaceable>;
607 <replaceable>statement</replaceable>;
608 ...
609 <replaceable>statement</replaceable>;
610 END
611 </programlisting>
612 </para>
613
614 <para>
615 This is similar to writing the text of the function body as a string
616 constant (see <replaceable>definition</replaceable> above), but there
617 are some differences: This form only works for <literal>LANGUAGE
618 SQL</literal>, the string constant form works for all languages. This
619 form is parsed at function definition time, the string constant form is
620 parsed at execution time; therefore this form cannot support
621 polymorphic argument types and other constructs that are not resolvable
622 at function definition time. This form tracks dependencies between the
623 function and objects used in the function body, so <literal>DROP
624 ... CASCADE</literal> will work correctly, whereas the form using
625 string literals may leave dangling functions. Finally, this form is
626 more compatible with the SQL standard and other SQL implementations.
627 </para>
628 </listitem>
629 </varlistentry>
630
631 </variablelist>
632 </refsect1>
633
634 <refsect1 id="sql-createfunction-overloading">
635 <title>Overloading</title>
636
637 <para>
638 <productname>PostgreSQL</productname> allows function
639 <firstterm>overloading</firstterm>; that is, the same name can be
640 used for several different functions so long as they have distinct
641 input argument types. Whether or not you use it, this capability entails
642 security precautions when calling functions in databases where some users
643 mistrust other users; see <xref linkend="typeconv-func"/>.
644 </para>
645
646 <para>
647 Two functions are considered the same if they have the same names and
648 <emphasis>input</emphasis> argument types, ignoring any <literal>OUT</literal>
649 parameters. Thus for example these declarations conflict:
650 <programlisting>
651 CREATE FUNCTION foo(int) ...
652 CREATE FUNCTION foo(int, out text) ...
653 </programlisting>
654 </para>
655
656 <para>
657 Functions that have different argument type lists will not be considered
658 to conflict at creation time, but if defaults are provided they might
659 conflict in use. For example, consider
660 <programlisting>
661 CREATE FUNCTION foo(int) ...
662 CREATE FUNCTION foo(int, int default 42) ...
663 </programlisting>
664 A call <literal>foo(10)</literal> will fail due to the ambiguity about which
665 function should be called.
666 </para>
667
668 </refsect1>
669
670 <refsect1 id="sql-createfunction-notes">
671 <title>Notes</title>
672
673 <para>
674 The full <acronym>SQL</acronym> type syntax is allowed for
675 declaring a function's arguments and return value. However,
676 parenthesized type modifiers (e.g., the precision field for
677 type <type>numeric</type>) are discarded by <command>CREATE FUNCTION</command>.
678 Thus for example
679 <literal>CREATE FUNCTION foo (varchar(10)) ...</literal>
680 is exactly the same as
681 <literal>CREATE FUNCTION foo (varchar) ...</literal>.
682 </para>
683
684 <para>
685 When replacing an existing function with <command>CREATE OR REPLACE
686 FUNCTION</command>, there are restrictions on changing parameter names.
687 You cannot change the name already assigned to any input parameter
688 (although you can add names to parameters that had none before).
689 If there is more than one output parameter, you cannot change the
690 names of the output parameters, because that would change the
691 column names of the anonymous composite type that describes the
692 function's result. These restrictions are made to ensure that
693 existing calls of the function do not stop working when it is replaced.
694 </para>
695
696 <para>
697 If a function is declared <literal>STRICT</literal> with a <literal>VARIADIC</literal>
698 argument, the strictness check tests that the variadic array <emphasis>as
699 a whole</emphasis> is non-null. The function will still be called if the
700 array has null elements.
701 </para>
702
703 </refsect1>
704
705 <refsect1 id="sql-createfunction-examples">
706 <title>Examples</title>
707
708 <para>
709 Add two integers using an SQL function:
710 <programlisting>
711 CREATE FUNCTION add(integer, integer) RETURNS integer
712 AS 'select $1 + $2;'
713 LANGUAGE SQL
714 IMMUTABLE
715 RETURNS NULL ON NULL INPUT;
716 </programlisting>
717 The same function written in a more SQL-conforming style, using argument
718 names and an unquoted body:
719 <programlisting>
720 CREATE FUNCTION add(a integer, b integer) RETURNS integer
721 LANGUAGE SQL
722 IMMUTABLE
723 RETURNS NULL ON NULL INPUT
724 RETURN a + b;
725 </programlisting>
726 </para>
727
728 <para>
729 Increment an integer, making use of an argument name, in
730 <application>PL/pgSQL</application>:
731 <programlisting>
732 CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$
733 BEGIN
734 RETURN i + 1;
735 END;
736 $$ LANGUAGE plpgsql;
737 </programlisting>
738 </para>
739
740 <para>
741 Return a record containing multiple output parameters:
742 <programlisting>
743 CREATE FUNCTION dup(in int, out f1 int, out f2 text)
744 AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
745 LANGUAGE SQL;
746
747 SELECT * FROM dup(42);
748 </programlisting>
749 You can do the same thing more verbosely with an explicitly named
750 composite type:
751 <programlisting>
752 CREATE TYPE dup_result AS (f1 int, f2 text);
753
754 CREATE FUNCTION dup(int) RETURNS dup_result
755 AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
756 LANGUAGE SQL;
757
758 SELECT * FROM dup(42);
759 </programlisting>
760 Another way to return multiple columns is to use a <literal>TABLE</literal>
761 function:
762 <programlisting>
763 CREATE FUNCTION dup(int) RETURNS TABLE(f1 int, f2 text)
764 AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
765 LANGUAGE SQL;
766
767 SELECT * FROM dup(42);
768 </programlisting>
769 However, a <literal>TABLE</literal> function is different from the
770 preceding examples, because it actually returns a <emphasis>set</emphasis>
771 of records, not just one record.
772 </para>
773 </refsect1>
774
775 <refsect1 id="sql-createfunction-security">
776 <title>Writing <literal>SECURITY DEFINER</literal> Functions Safely</title>
777
778 <indexterm>
779 <primary><varname>search_path</varname> configuration parameter</primary>
780 <secondary>use in securing functions</secondary>
781 </indexterm>
782
783 <indexterm>
784 <primary><varname>createrole_self_grant</varname> configuration parameter</primary>
785 <secondary>use in securing functions</secondary>
786 </indexterm>
787
788 <para>
789 Because a <literal>SECURITY DEFINER</literal> function is executed
790 with the privileges of the user that owns it, care is needed to
791 ensure that the function cannot be misused. For security,
792 <xref linkend="guc-search-path"/> should be set to exclude any schemas
793 writable by untrusted users. This prevents
794 malicious users from creating objects (e.g., tables, functions, and
795 operators) that mask objects intended to be used by the function.
796 Particularly important in this regard is the
797 temporary-table schema, which is searched first by default, and
798 is normally writable by anyone. A secure arrangement can be obtained
799 by forcing the temporary schema to be searched last. To do this,
800 write <literal>pg_temp</literal><indexterm><primary>pg_temp</primary><secondary>securing functions</secondary></indexterm> as the last entry in <varname>search_path</varname>.
801 This function illustrates safe usage:
802
803 <programlisting>
804 CREATE FUNCTION check_password(uname TEXT, pass TEXT)
805 RETURNS BOOLEAN AS $$
806 DECLARE passed BOOLEAN;
807 BEGIN
808 SELECT (pwd = $2) INTO passed
809 FROM pwds
810 WHERE username = $1;
811
812 RETURN passed;
813 END;
814 $$ LANGUAGE plpgsql
815 SECURITY DEFINER
816 -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
817 SET search_path = admin, pg_temp;
818 </programlisting>
819
820 This function's intention is to access a table <literal>admin.pwds</literal>.
821 But without the <literal>SET</literal> clause, or with a <literal>SET</literal> clause
822 mentioning only <literal>admin</literal>, the function could be subverted by
823 creating a temporary table named <literal>pwds</literal>.
824 </para>
825
826 <para>
827 If the security definer function intends to create roles, and if it
828 is running as a non-superuser, <varname>createrole_self_grant</varname>
829 should also be set to a known value using the <literal>SET</literal>
830 clause.
831 </para>
832
833 <para>
834 Another point to keep in mind is that by default, execute privilege
835 is granted to <literal>PUBLIC</literal> for newly created functions
836 (see <xref linkend="ddl-priv"/> for more
837 information). Frequently you will wish to restrict use of a security
838 definer function to only some users. To do that, you must revoke
839 the default <literal>PUBLIC</literal> privileges and then grant execute
840 privilege selectively. To avoid having a window where the new function
841 is accessible to all, create it and set the privileges within a single
842 transaction. For example:
843 </para>
844
845 <programlisting>
846 BEGIN;
847 CREATE FUNCTION check_password(uname TEXT, pass TEXT) ... SECURITY DEFINER;
848 REVOKE ALL ON FUNCTION check_password(uname TEXT, pass TEXT) FROM PUBLIC;
849 GRANT EXECUTE ON FUNCTION check_password(uname TEXT, pass TEXT) TO admins;
850 COMMIT;
851 </programlisting>
852
853 </refsect1>
854
855 <refsect1 id="sql-createfunction-compat">
856 <title>Compatibility</title>
857
858 <para>
859 A <command>CREATE FUNCTION</command> command is defined in the SQL
860 standard. The <productname>PostgreSQL</productname> implementation can be
861 used in a compatible way but has many extensions. Conversely, the SQL
862 standard specifies a number of optional features that are not implemented
863 in <productname>PostgreSQL</productname>.
864 </para>
865
866 <para>
867 The following are important compatibility issues:
868
869 <itemizedlist>
870 <listitem>
871 <para>
872 <literal>OR REPLACE</literal> is a PostgreSQL extension.
873 </para>
874 </listitem>
875
876 <listitem>
877 <para>
878 For compatibility with some other database systems, <replaceable
879 class="parameter">argmode</replaceable> can be written either before or
880 after <replaceable class="parameter">argname</replaceable>. But only
881 the first way is standard-compliant.
882 </para>
883 </listitem>
884
885 <listitem>
886 <para>
887 For parameter defaults, the SQL standard specifies only the syntax with
888 the <literal>DEFAULT</literal> key word. The syntax with
889 <literal>=</literal> is used in T-SQL and Firebird.
890 </para>
891 </listitem>
892
893 <listitem>
894 <para>
895 The <literal>SETOF</literal> modifier is a PostgreSQL extension.
896 </para>
897 </listitem>
898
899 <listitem>
900 <para>
901 Only <literal>SQL</literal> is standardized as a language.
902 </para>
903 </listitem>
904
905 <listitem>
906 <para>
907 All other attributes except <literal>CALLED ON NULL INPUT</literal> and
908 <literal>RETURNS NULL ON NULL INPUT</literal> are not standardized.
909 </para>
910 </listitem>
911
912 <listitem>
913 <para>
914 For the body of <literal>LANGUAGE SQL</literal> functions, the SQL
915 standard only specifies the <replaceable>sql_body</replaceable> form.
916 </para>
917 </listitem>
918 </itemizedlist>
919 </para>
920
921 <para>
922 Simple <literal>LANGUAGE SQL</literal> functions can be written in a way
923 that is both standard-conforming and portable to other implementations.
924 More complex functions using advanced features, optimization attributes, or
925 other languages will necessarily be specific to PostgreSQL in a significant
926 way.
927 </para>
928 </refsect1>
929
930
931 <refsect1>
932 <title>See Also</title>
933
934 <simplelist type="inline">
935 <member><xref linkend="sql-alterfunction"/></member>
936 <member><xref linkend="sql-dropfunction"/></member>
937 <member><xref linkend="sql-grant"/></member>
938 <member><xref linkend="sql-load"/></member>
939 <member><xref linkend="sql-revoke"/></member>
940 </simplelist>
941 </refsect1>
942
943 </refentry>