1 <?xml version="1.0" encoding="UTF-8" standalone="no"?>
2 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>CREATE FUNCTION</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="sql-createforeigntable.html" title="CREATE FOREIGN TABLE" /><link rel="next" href="sql-creategroup.html" title="CREATE GROUP" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">CREATE FUNCTION</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-createforeigntable.html" title="CREATE FOREIGN TABLE">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-creategroup.html" title="CREATE GROUP">Next</a></td></tr></table><hr /></div><div class="refentry" id="SQL-CREATEFUNCTION"><div class="titlepage"></div><a id="id-1.9.3.67.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">CREATE FUNCTION</span></h2><p>CREATE FUNCTION — define a new function</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis">
3 CREATE [ OR REPLACE ] FUNCTION
4 <em class="replaceable"><code>name</code></em> ( [ [ <em class="replaceable"><code>argmode</code></em> ] [ <em class="replaceable"><code>argname</code></em> ] <em class="replaceable"><code>argtype</code></em> [ { DEFAULT | = } <em class="replaceable"><code>default_expr</code></em> ] [, ...] ] )
5 [ RETURNS <em class="replaceable"><code>rettype</code></em>
6 | RETURNS TABLE ( <em class="replaceable"><code>column_name</code></em> <em class="replaceable"><code>column_type</code></em> [, ...] ) ]
7 { LANGUAGE <em class="replaceable"><code>lang_name</code></em>
8 | TRANSFORM { FOR TYPE <em class="replaceable"><code>type_name</code></em> } [, ... ]
10 | { IMMUTABLE | STABLE | VOLATILE }
12 | { CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT }
13 | { [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER }
14 | PARALLEL { UNSAFE | RESTRICTED | SAFE }
15 | COST <em class="replaceable"><code>execution_cost</code></em>
16 | ROWS <em class="replaceable"><code>result_rows</code></em>
17 | SUPPORT <em class="replaceable"><code>support_function</code></em>
18 | SET <em class="replaceable"><code>configuration_parameter</code></em> { TO <em class="replaceable"><code>value</code></em> | = <em class="replaceable"><code>value</code></em> | FROM CURRENT }
19 | AS '<em class="replaceable"><code>definition</code></em>'
20 | AS '<em class="replaceable"><code>obj_file</code></em>', '<em class="replaceable"><code>link_symbol</code></em>'
21 | <em class="replaceable"><code>sql_body</code></em>
23 </pre></div><div class="refsect1" id="SQL-CREATEFUNCTION-DESCRIPTION"><h2>Description</h2><p>
24 <code class="command">CREATE FUNCTION</code> defines a new function.
25 <code class="command">CREATE OR REPLACE FUNCTION</code> will either create a
26 new function, or replace an existing definition.
27 To be able to define a function, the user must have the
28 <code class="literal">USAGE</code> privilege on the language.
30 If a schema name is included, then the function is created in the
31 specified schema. Otherwise it is created in the current schema.
32 The name of the new function must not match any existing function or procedure
33 with the same input argument types in the same schema. However,
34 functions and procedures of different argument types can share a name (this is
35 called <em class="firstterm">overloading</em>).
37 To replace the current definition of an existing function, use
38 <code class="command">CREATE OR REPLACE FUNCTION</code>. It is not possible
39 to change the name or argument types of a function this way (if you
40 tried, you would actually be creating a new, distinct function).
41 Also, <code class="command">CREATE OR REPLACE FUNCTION</code> will not let
42 you change the return type of an existing function. To do that,
43 you must drop and recreate the function. (When using <code class="literal">OUT</code>
44 parameters, that means you cannot change the types of any
45 <code class="literal">OUT</code> parameters except by dropping the function.)
47 When <code class="command">CREATE OR REPLACE FUNCTION</code> is used to replace an
48 existing function, the ownership and permissions of the function
49 do not change. All other function properties are assigned the
50 values specified or implied in the command. You must own the function
51 to replace it (this includes being a member of the owning role).
53 If you drop and then recreate a function, the new function is not
54 the same entity as the old; you will have to drop existing rules, views,
55 triggers, etc. that refer to the old function. Use
56 <code class="command">CREATE OR REPLACE FUNCTION</code> to change a function
57 definition without breaking objects that refer to the function.
58 Also, <code class="command">ALTER FUNCTION</code> can be used to change most of the
59 auxiliary properties of an existing function.
61 The user that creates the function becomes the owner of the function.
63 To be able to create a function, you must have <code class="literal">USAGE</code>
64 privilege on the argument types and the return type.
66 Refer to <a class="xref" href="xfunc.html" title="36.3. User-Defined Functions">Section 36.3</a> for further information on writing
68 </p></div><div class="refsect1" id="id-1.9.3.67.6"><h2>Parameters</h2><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>name</code></em></span></dt><dd><p>
69 The name (optionally schema-qualified) of the function to create.
70 </p></dd><dt><span class="term"><em class="replaceable"><code>argmode</code></em></span></dt><dd><p>
71 The mode of an argument: <code class="literal">IN</code>, <code class="literal">OUT</code>,
72 <code class="literal">INOUT</code>, or <code class="literal">VARIADIC</code>.
73 If omitted, the default is <code class="literal">IN</code>.
74 Only <code class="literal">OUT</code> arguments can follow a <code class="literal">VARIADIC</code> one.
75 Also, <code class="literal">OUT</code> and <code class="literal">INOUT</code> arguments cannot be used
76 together with the <code class="literal">RETURNS TABLE</code> notation.
77 </p></dd><dt><span class="term"><em class="replaceable"><code>argname</code></em></span></dt><dd><p>
78 The name of an argument. Some languages (including SQL and PL/pgSQL)
79 let you use the name in the function body. For other languages the
80 name of an input argument is just extra documentation, so far as
81 the function itself is concerned; but you can use input argument names
82 when calling a function to improve readability (see <a class="xref" href="sql-syntax-calling-funcs.html" title="4.3. Calling Functions">Section 4.3</a>). In any case, the name
83 of an output argument is significant, because it defines the column
84 name in the result row type. (If you omit the name for an output
85 argument, the system will choose a default column name.)
86 </p></dd><dt><span class="term"><em class="replaceable"><code>argtype</code></em></span></dt><dd><p>
87 The data type(s) of the function's arguments (optionally
88 schema-qualified), if any. The argument types can be base, composite,
89 or domain types, or can reference the type of a table column.
91 Depending on the implementation language it might also be allowed
92 to specify <span class="quote">“<span class="quote">pseudo-types</span>”</span> such as <code class="type">cstring</code>.
93 Pseudo-types indicate that the actual argument type is either
94 incompletely specified, or outside the set of ordinary SQL data types.
96 The type of a column is referenced by writing
97 <code class="literal"><em class="replaceable"><code>table_name</code></em>.<em class="replaceable"><code>column_name</code></em>%TYPE</code>.
98 Using this feature can sometimes help make a function independent of
99 changes to the definition of a table.
100 </p></dd><dt><span class="term"><em class="replaceable"><code>default_expr</code></em></span></dt><dd><p>
101 An expression to be used as default value if the parameter is
102 not specified. The expression has to be coercible to the
103 argument type of the parameter.
104 Only input (including <code class="literal">INOUT</code>) parameters can have a default
105 value. All input parameters following a
106 parameter with a default value must have default values as well.
107 </p></dd><dt><span class="term"><em class="replaceable"><code>rettype</code></em></span></dt><dd><p>
108 The return data type (optionally schema-qualified). The return type
109 can be a base, composite, or domain type,
110 or can reference the type of a table column.
111 Depending on the implementation language it might also be allowed
112 to specify <span class="quote">“<span class="quote">pseudo-types</span>”</span> such as <code class="type">cstring</code>.
113 If the function is not supposed to return a value, specify
114 <code class="type">void</code> as the return type.
116 When there are <code class="literal">OUT</code> or <code class="literal">INOUT</code> parameters,
117 the <code class="literal">RETURNS</code> clause can be omitted. If present, it
118 must agree with the result type implied by the output parameters:
119 <code class="literal">RECORD</code> if there are multiple output parameters, or
120 the same type as the single output parameter.
122 The <code class="literal">SETOF</code>
123 modifier indicates that the function will return a set of
124 items, rather than a single item.
126 The type of a column is referenced by writing
127 <code class="literal"><em class="replaceable"><code>table_name</code></em>.<em class="replaceable"><code>column_name</code></em>%TYPE</code>.
128 </p></dd><dt><span class="term"><em class="replaceable"><code>column_name</code></em></span></dt><dd><p>
129 The name of an output column in the <code class="literal">RETURNS TABLE</code>
130 syntax. This is effectively another way of declaring a named
131 <code class="literal">OUT</code> parameter, except that <code class="literal">RETURNS TABLE</code>
132 also implies <code class="literal">RETURNS SETOF</code>.
133 </p></dd><dt><span class="term"><em class="replaceable"><code>column_type</code></em></span></dt><dd><p>
134 The data type of an output column in the <code class="literal">RETURNS TABLE</code>
136 </p></dd><dt><span class="term"><em class="replaceable"><code>lang_name</code></em></span></dt><dd><p>
137 The name of the language that the function is implemented in.
138 It can be <code class="literal">sql</code>, <code class="literal">c</code>,
139 <code class="literal">internal</code>, or the name of a user-defined
140 procedural language, e.g., <code class="literal">plpgsql</code>. The default is
141 <code class="literal">sql</code> if <em class="replaceable"><code>sql_body</code></em> is specified. Enclosing the
142 name in single quotes is deprecated and requires matching case.
143 </p></dd><dt><span class="term"><code class="literal">TRANSFORM { FOR TYPE <em class="replaceable"><code>type_name</code></em> } [, ... ] }</code></span></dt><dd><p>
144 Lists which transforms a call to the function should apply. Transforms
145 convert between SQL types and language-specific data types;
146 see <a class="xref" href="sql-createtransform.html" title="CREATE TRANSFORM"><span class="refentrytitle">CREATE TRANSFORM</span></a>. Procedural language
147 implementations usually have hardcoded knowledge of the built-in types,
148 so those don't need to be listed here. If a procedural language
149 implementation does not know how to handle a type and no transform is
150 supplied, it will fall back to a default behavior for converting data
151 types, but this depends on the implementation.
152 </p></dd><dt><span class="term"><code class="literal">WINDOW</code></span></dt><dd><p><code class="literal">WINDOW</code> indicates that the function is a
153 <em class="firstterm">window function</em> rather than a plain function.
154 This is currently only useful for functions written in C.
155 The <code class="literal">WINDOW</code> attribute cannot be changed when
156 replacing an existing function definition.
157 </p></dd><dt><span class="term"><code class="literal">IMMUTABLE</code><br /></span><span class="term"><code class="literal">STABLE</code><br /></span><span class="term"><code class="literal">VOLATILE</code></span></dt><dd><p>
158 These attributes inform the query optimizer about the behavior
159 of the function. At most one choice
160 can be specified. If none of these appear,
161 <code class="literal">VOLATILE</code> is the default assumption.
162 </p><p><code class="literal">IMMUTABLE</code> indicates that the function
163 cannot modify the database and always
164 returns the same result when given the same argument values; that
165 is, it does not do database lookups or otherwise use information not
166 directly present in its argument list. If this option is given,
167 any call of the function with all-constant arguments can be
168 immediately replaced with the function value.
169 </p><p><code class="literal">STABLE</code> indicates that the function
170 cannot modify the database,
171 and that within a single table scan it will consistently
172 return the same result for the same argument values, but that its
173 result could change across SQL statements. This is the appropriate
174 selection for functions whose results depend on database lookups,
175 parameter variables (such as the current time zone), etc. (It is
176 inappropriate for <code class="literal">AFTER</code> triggers that wish to
177 query rows modified by the current command.) Also note
178 that the <code class="function">current_timestamp</code> family of functions qualify
179 as stable, since their values do not change within a transaction.
180 </p><p><code class="literal">VOLATILE</code> indicates that the function value can
181 change even within a single table scan, so no optimizations can be
182 made. Relatively few database functions are volatile in this sense;
183 some examples are <code class="literal">random()</code>, <code class="literal">currval()</code>,
184 <code class="literal">timeofday()</code>. But note that any function that has
185 side-effects must be classified volatile, even if its result is quite
186 predictable, to prevent calls from being optimized away; an example is
187 <code class="literal">setval()</code>.
189 For additional details see <a class="xref" href="xfunc-volatility.html" title="36.7. Function Volatility Categories">Section 36.7</a>.
190 </p></dd><dt><span class="term"><code class="literal">LEAKPROOF</code></span></dt><dd><p>
191 <code class="literal">LEAKPROOF</code> indicates that the function has no side
192 effects. It reveals no information about its arguments other than by
193 its return value. For example, a function which throws an error message
194 for some argument values but not others, or which includes the argument
195 values in any error message, is not leakproof. This affects how the
196 system executes queries against views created with the
197 <code class="literal">security_barrier</code> option or tables with row level
198 security enabled. The system will enforce conditions from security
199 policies and security barrier views before any user-supplied conditions
200 from the query itself that contain non-leakproof functions, in order to
201 prevent the inadvertent exposure of data. Functions and operators
202 marked as leakproof are assumed to be trustworthy, and may be executed
203 before conditions from security policies and security barrier views.
204 In addition, functions which do not take arguments or which are not
205 passed any arguments from the security barrier view or table do not have
206 to be marked as leakproof to be executed before security conditions. See
207 <a class="xref" href="sql-createview.html" title="CREATE VIEW"><span class="refentrytitle">CREATE VIEW</span></a> and <a class="xref" href="rules-privileges.html" title="39.5. Rules and Privileges">Section 39.5</a>.
208 This option can only be set by the superuser.
209 </p></dd><dt><span class="term"><code class="literal">CALLED ON NULL INPUT</code><br /></span><span class="term"><code class="literal">RETURNS NULL ON NULL INPUT</code><br /></span><span class="term"><code class="literal">STRICT</code></span></dt><dd><p><code class="literal">CALLED ON NULL INPUT</code> (the default) indicates
210 that the function will be called normally when some of its
211 arguments are null. It is then the function author's
212 responsibility to check for null values if necessary and respond
214 </p><p><code class="literal">RETURNS NULL ON NULL INPUT</code> or
215 <code class="literal">STRICT</code> indicates that the function always
216 returns null whenever any of its arguments are null. If this
217 parameter is specified, the function is not executed when there
218 are null arguments; instead a null result is assumed
220 </p></dd><dt><span class="term"><code class="literal">[<span class="optional">EXTERNAL</span>] SECURITY INVOKER</code><br /></span><span class="term"><code class="literal">[<span class="optional">EXTERNAL</span>] SECURITY DEFINER</code></span></dt><dd><p><code class="literal">SECURITY INVOKER</code> indicates that the function
221 is to be executed with the privileges of the user that calls it.
222 That is the default. <code class="literal">SECURITY DEFINER</code>
223 specifies that the function is to be executed with the
224 privileges of the user that owns it. For information on how to
225 write <code class="literal">SECURITY DEFINER</code> functions safely,
226 <a class="link" href="sql-createfunction.html#SQL-CREATEFUNCTION-SECURITY" title="Writing SECURITY DEFINER Functions Safely">see below</a>.
228 The key word <code class="literal">EXTERNAL</code> is allowed for SQL
229 conformance, but it is optional since, unlike in SQL, this feature
230 applies to all functions not only external ones.
231 </p></dd><dt><span class="term"><code class="literal">PARALLEL</code></span></dt><dd><p>
232 <code class="literal">PARALLEL UNSAFE</code> indicates that the function
233 can't be executed in parallel mode; the presence of such a
234 function in an SQL statement forces a serial execution plan. This is
235 the default. <code class="literal">PARALLEL RESTRICTED</code> indicates that
236 the function can be executed in parallel mode, but only in the parallel
237 group leader process. <code class="literal">PARALLEL SAFE</code>
238 indicates that the function is safe to run in parallel mode without
239 restriction, including in parallel worker processes.
241 Functions should be labeled parallel unsafe if they modify any database
242 state, change the transaction state (other than by using a
243 subtransaction for error recovery), access sequences (e.g., by
244 calling <code class="literal">currval</code>) or make persistent changes to
245 settings. They should
246 be labeled parallel restricted if they access temporary tables,
247 client connection state, cursors, prepared statements, or miscellaneous
248 backend-local state which the system cannot synchronize in parallel mode
249 (e.g., <code class="literal">setseed</code> cannot be executed other than by the group
250 leader because a change made by another process would not be reflected
251 in the leader). In general, if a function is labeled as being safe when
252 it is restricted or unsafe, or if it is labeled as being restricted when
253 it is in fact unsafe, it may throw errors or produce wrong answers
254 when used in a parallel query. C-language functions could in theory
255 exhibit totally undefined behavior if mislabeled, since there is no way
256 for the system to protect itself against arbitrary C code, but in most
257 likely cases the result will be no worse than for any other function.
258 If in doubt, functions should be labeled as <code class="literal">UNSAFE</code>, which is
260 </p></dd><dt><span class="term"><code class="literal">COST</code> <em class="replaceable"><code>execution_cost</code></em></span></dt><dd><p>
261 A positive number giving the estimated execution cost for the function,
262 in units of <a class="xref" href="runtime-config-query.html#GUC-CPU-OPERATOR-COST">cpu_operator_cost</a>. If the function
263 returns a set, this is the cost per returned row. If the cost is
264 not specified, 1 unit is assumed for C-language and internal functions,
265 and 100 units for functions in all other languages. Larger values
266 cause the planner to try to avoid evaluating the function more often
268 </p></dd><dt><span class="term"><code class="literal">ROWS</code> <em class="replaceable"><code>result_rows</code></em></span></dt><dd><p>
269 A positive number giving the estimated number of rows that the planner
270 should expect the function to return. This is only allowed when the
271 function is declared to return a set. The default assumption is
273 </p></dd><dt><span class="term"><code class="literal">SUPPORT</code> <em class="replaceable"><code>support_function</code></em></span></dt><dd><p>
274 The name (optionally schema-qualified) of a <em class="firstterm">planner support
275 function</em> to use for this function. See
276 <a class="xref" href="xfunc-optimization.html" title="36.11. Function Optimization Information">Section 36.11</a> for details.
277 You must be superuser to use this option.
278 </p></dd><dt><span class="term"><em class="replaceable"><code>configuration_parameter</code></em><br /></span><span class="term"><em class="replaceable"><code>value</code></em></span></dt><dd><p>
279 The <code class="literal">SET</code> clause causes the specified configuration
280 parameter to be set to the specified value when the function is
281 entered, and then restored to its prior value when the function exits.
282 <code class="literal">SET FROM CURRENT</code> saves the value of the parameter that
283 is current when <code class="command">CREATE FUNCTION</code> is executed as the value
284 to be applied when the function is entered.
286 If a <code class="literal">SET</code> clause is attached to a function, then
287 the effects of a <code class="command">SET LOCAL</code> command executed inside the
288 function for the same variable are restricted to the function: the
289 configuration parameter's prior value is still restored at function exit.
291 <code class="command">SET</code> command (without <code class="literal">LOCAL</code>) overrides the
292 <code class="literal">SET</code> clause, much as it would do for a previous <code class="command">SET
293 LOCAL</code> command: the effects of such a command will persist after
294 function exit, unless the current transaction is rolled back.
296 See <a class="xref" href="sql-set.html" title="SET"><span class="refentrytitle">SET</span></a> and
297 <a class="xref" href="runtime-config.html" title="Chapter 19. Server Configuration">Chapter 19</a>
298 for more information about allowed parameter names and values.
299 </p></dd><dt><span class="term"><em class="replaceable"><code>definition</code></em></span></dt><dd><p>
300 A string constant defining the function; the meaning depends on the
301 language. It can be an internal function name, the path to an
302 object file, an SQL command, or text in a procedural language.
304 It is often helpful to use dollar quoting (see <a class="xref" href="sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING" title="4.1.2.4. Dollar-Quoted String Constants">Section 4.1.2.4</a>) to write the function definition
305 string, rather than the normal single quote syntax. Without dollar
306 quoting, any single quotes or backslashes in the function definition must
307 be escaped by doubling them.
308 </p></dd><dt><span class="term"><code class="literal"><em class="replaceable"><code>obj_file</code></em>, <em class="replaceable"><code>link_symbol</code></em></code></span></dt><dd><p>
309 This form of the <code class="literal">AS</code> clause is used for
310 dynamically loadable C language functions when the function name
311 in the C language source code is not the same as the name of
312 the SQL function. The string <em class="replaceable"><code>obj_file</code></em> is the name of the shared
313 library file containing the compiled C function, and is interpreted
314 as for the <a class="link" href="sql-load.html" title="LOAD"><code class="command">LOAD</code></a> command. The string
315 <em class="replaceable"><code>link_symbol</code></em> is the
316 function's link symbol, that is, the name of the function in the C
317 language source code. If the link symbol is omitted, it is assumed to
318 be the same as the name of the SQL function being defined. The C names
319 of all functions must be different, so you must give overloaded C
320 functions different C names (for example, use the argument types as
321 part of the C names).
323 When repeated <code class="command">CREATE FUNCTION</code> calls refer to
324 the same object file, the file is only loaded once per session.
326 reload the file (perhaps during development), start a new session.
327 </p></dd><dt><span class="term"><em class="replaceable"><code>sql_body</code></em></span></dt><dd><p>
328 The body of a <code class="literal">LANGUAGE SQL</code> function. This can
329 either be a single statement
330 </p><pre class="programlisting">
331 RETURN <em class="replaceable"><code>expression</code></em>
334 </p><pre class="programlisting">
336 <em class="replaceable"><code>statement</code></em>;
337 <em class="replaceable"><code>statement</code></em>;
339 <em class="replaceable"><code>statement</code></em>;
343 This is similar to writing the text of the function body as a string
344 constant (see <em class="replaceable"><code>definition</code></em> above), but there
345 are some differences: This form only works for <code class="literal">LANGUAGE
346 SQL</code>, the string constant form works for all languages. This
347 form is parsed at function definition time, the string constant form is
348 parsed at execution time; therefore this form cannot support
349 polymorphic argument types and other constructs that are not resolvable
350 at function definition time. This form tracks dependencies between the
351 function and objects used in the function body, so <code class="literal">DROP
352 ... CASCADE</code> will work correctly, whereas the form using
353 string literals may leave dangling functions. Finally, this form is
354 more compatible with the SQL standard and other SQL implementations.
355 </p></dd></dl></div></div><div class="refsect1" id="SQL-CREATEFUNCTION-OVERLOADING"><h2>Overloading</h2><p>
356 <span class="productname">PostgreSQL</span> allows function
357 <em class="firstterm">overloading</em>; that is, the same name can be
358 used for several different functions so long as they have distinct
359 input argument types. Whether or not you use it, this capability entails
360 security precautions when calling functions in databases where some users
361 mistrust other users; see <a class="xref" href="typeconv-func.html" title="10.3. Functions">Section 10.3</a>.
363 Two functions are considered the same if they have the same names and
364 <span class="emphasis"><em>input</em></span> argument types, ignoring any <code class="literal">OUT</code>
365 parameters. Thus for example these declarations conflict:
366 </p><pre class="programlisting">
367 CREATE FUNCTION foo(int) ...
368 CREATE FUNCTION foo(int, out text) ...
371 Functions that have different argument type lists will not be considered
372 to conflict at creation time, but if defaults are provided they might
373 conflict in use. For example, consider
374 </p><pre class="programlisting">
375 CREATE FUNCTION foo(int) ...
376 CREATE FUNCTION foo(int, int default 42) ...
378 A call <code class="literal">foo(10)</code> will fail due to the ambiguity about which
379 function should be called.
380 </p></div><div class="refsect1" id="SQL-CREATEFUNCTION-NOTES"><h2>Notes</h2><p>
381 The full <acronym class="acronym">SQL</acronym> type syntax is allowed for
382 declaring a function's arguments and return value. However,
383 parenthesized type modifiers (e.g., the precision field for
384 type <code class="type">numeric</code>) are discarded by <code class="command">CREATE FUNCTION</code>.
386 <code class="literal">CREATE FUNCTION foo (varchar(10)) ...</code>
387 is exactly the same as
388 <code class="literal">CREATE FUNCTION foo (varchar) ...</code>.
390 When replacing an existing function with <code class="command">CREATE OR REPLACE
391 FUNCTION</code>, there are restrictions on changing parameter names.
392 You cannot change the name already assigned to any input parameter
393 (although you can add names to parameters that had none before).
394 If there is more than one output parameter, you cannot change the
395 names of the output parameters, because that would change the
396 column names of the anonymous composite type that describes the
397 function's result. These restrictions are made to ensure that
398 existing calls of the function do not stop working when it is replaced.
400 If a function is declared <code class="literal">STRICT</code> with a <code class="literal">VARIADIC</code>
401 argument, the strictness check tests that the variadic array <span class="emphasis"><em>as
402 a whole</em></span> is non-null. The function will still be called if the
403 array has null elements.
404 </p></div><div class="refsect1" id="SQL-CREATEFUNCTION-EXAMPLES"><h2>Examples</h2><p>
405 Add two integers using an SQL function:
406 </p><pre class="programlisting">
407 CREATE FUNCTION add(integer, integer) RETURNS integer
411 RETURNS NULL ON NULL INPUT;
413 The same function written in a more SQL-conforming style, using argument
414 names and an unquoted body:
415 </p><pre class="programlisting">
416 CREATE FUNCTION add(a integer, b integer) RETURNS integer
419 RETURNS NULL ON NULL INPUT
423 Increment an integer, making use of an argument name, in
424 <span class="application">PL/pgSQL</span>:
425 </p><pre class="programlisting">
426 CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$
433 Return a record containing multiple output parameters:
434 </p><pre class="programlisting">
435 CREATE FUNCTION dup(in int, out f1 int, out f2 text)
436 AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
439 SELECT * FROM dup(42);
441 You can do the same thing more verbosely with an explicitly named
443 </p><pre class="programlisting">
444 CREATE TYPE dup_result AS (f1 int, f2 text);
446 CREATE FUNCTION dup(int) RETURNS dup_result
447 AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
450 SELECT * FROM dup(42);
452 Another way to return multiple columns is to use a <code class="literal">TABLE</code>
454 </p><pre class="programlisting">
455 CREATE FUNCTION dup(int) RETURNS TABLE(f1 int, f2 text)
456 AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
459 SELECT * FROM dup(42);
461 However, a <code class="literal">TABLE</code> function is different from the
462 preceding examples, because it actually returns a <span class="emphasis"><em>set</em></span>
463 of records, not just one record.
464 </p></div><div class="refsect1" id="SQL-CREATEFUNCTION-SECURITY"><h2>Writing <code class="literal">SECURITY DEFINER</code> Functions Safely</h2><a id="id-1.9.3.67.10.2" class="indexterm"></a><a id="id-1.9.3.67.10.3" class="indexterm"></a><p>
465 Because a <code class="literal">SECURITY DEFINER</code> function is executed
466 with the privileges of the user that owns it, care is needed to
467 ensure that the function cannot be misused. For security,
468 <a class="xref" href="runtime-config-client.html#GUC-SEARCH-PATH">search_path</a> should be set to exclude any schemas
469 writable by untrusted users. This prevents
470 malicious users from creating objects (e.g., tables, functions, and
471 operators) that mask objects intended to be used by the function.
472 Particularly important in this regard is the
473 temporary-table schema, which is searched first by default, and
474 is normally writable by anyone. A secure arrangement can be obtained
475 by forcing the temporary schema to be searched last. To do this,
476 write <code class="literal">pg_temp</code><a id="id-1.9.3.67.10.4.4" class="indexterm"></a> as the last entry in <code class="varname">search_path</code>.
477 This function illustrates safe usage:
479 </p><pre class="programlisting">
480 CREATE FUNCTION check_password(uname TEXT, pass TEXT)
481 RETURNS BOOLEAN AS $$
482 DECLARE passed BOOLEAN;
484 SELECT (pwd = $2) INTO passed
492 -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
493 SET search_path = admin, pg_temp;
496 This function's intention is to access a table <code class="literal">admin.pwds</code>.
497 But without the <code class="literal">SET</code> clause, or with a <code class="literal">SET</code> clause
498 mentioning only <code class="literal">admin</code>, the function could be subverted by
499 creating a temporary table named <code class="literal">pwds</code>.
501 If the security definer function intends to create roles, and if it
502 is running as a non-superuser, <code class="varname">createrole_self_grant</code>
503 should also be set to a known value using the <code class="literal">SET</code>
506 Another point to keep in mind is that by default, execute privilege
507 is granted to <code class="literal">PUBLIC</code> for newly created functions
508 (see <a class="xref" href="ddl-priv.html" title="5.8. Privileges">Section 5.8</a> for more
509 information). Frequently you will wish to restrict use of a security
510 definer function to only some users. To do that, you must revoke
511 the default <code class="literal">PUBLIC</code> privileges and then grant execute
512 privilege selectively. To avoid having a window where the new function
513 is accessible to all, create it and set the privileges within a single
514 transaction. For example:
515 </p><pre class="programlisting">
517 CREATE FUNCTION check_password(uname TEXT, pass TEXT) ... SECURITY DEFINER;
518 REVOKE ALL ON FUNCTION check_password(uname TEXT, pass TEXT) FROM PUBLIC;
519 GRANT EXECUTE ON FUNCTION check_password(uname TEXT, pass TEXT) TO admins;
521 </pre></div><div class="refsect1" id="SQL-CREATEFUNCTION-COMPAT"><h2>Compatibility</h2><p>
522 A <code class="command">CREATE FUNCTION</code> command is defined in the SQL
523 standard. The <span class="productname">PostgreSQL</span> implementation can be
524 used in a compatible way but has many extensions. Conversely, the SQL
525 standard specifies a number of optional features that are not implemented
526 in <span class="productname">PostgreSQL</span>.
528 The following are important compatibility issues:
530 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
531 <code class="literal">OR REPLACE</code> is a PostgreSQL extension.
532 </p></li><li class="listitem"><p>
533 For compatibility with some other database systems, <em class="replaceable"><code>argmode</code></em> can be written either before or
534 after <em class="replaceable"><code>argname</code></em>. But only
535 the first way is standard-compliant.
536 </p></li><li class="listitem"><p>
537 For parameter defaults, the SQL standard specifies only the syntax with
538 the <code class="literal">DEFAULT</code> key word. The syntax with
539 <code class="literal">=</code> is used in T-SQL and Firebird.
540 </p></li><li class="listitem"><p>
541 The <code class="literal">SETOF</code> modifier is a PostgreSQL extension.
542 </p></li><li class="listitem"><p>
543 Only <code class="literal">SQL</code> is standardized as a language.
544 </p></li><li class="listitem"><p>
545 All other attributes except <code class="literal">CALLED ON NULL INPUT</code> and
546 <code class="literal">RETURNS NULL ON NULL INPUT</code> are not standardized.
547 </p></li><li class="listitem"><p>
548 For the body of <code class="literal">LANGUAGE SQL</code> functions, the SQL
549 standard only specifies the <em class="replaceable"><code>sql_body</code></em> form.
550 </p></li></ul></div><p>
552 Simple <code class="literal">LANGUAGE SQL</code> functions can be written in a way
553 that is both standard-conforming and portable to other implementations.
554 More complex functions using advanced features, optimization attributes, or
555 other languages will necessarily be specific to PostgreSQL in a significant
557 </p></div><div class="refsect1" id="id-1.9.3.67.12"><h2>See Also</h2><span class="simplelist"><a class="xref" href="sql-alterfunction.html" title="ALTER FUNCTION"><span class="refentrytitle">ALTER FUNCTION</span></a>, <a class="xref" href="sql-dropfunction.html" title="DROP FUNCTION"><span class="refentrytitle">DROP FUNCTION</span></a>, <a class="xref" href="sql-grant.html" title="GRANT"><span class="refentrytitle">GRANT</span></a>, <a class="xref" href="sql-load.html" title="LOAD"><span class="refentrytitle">LOAD</span></a>, <a class="xref" href="sql-revoke.html" title="REVOKE"><span class="refentrytitle">REVOKE</span></a></span></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-createforeigntable.html" title="CREATE FOREIGN TABLE">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-creategroup.html" title="CREATE GROUP">Next</a></td></tr><tr><td width="40%" align="left" valign="top">CREATE FOREIGN TABLE </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> CREATE GROUP</td></tr></table></div></body></html>
begriffs.com / projects / ai-pg / blob