codekingpro/portable-devtools
115k
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 16.3 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">3CREATE [ OR REPLACE ] FUNCTION4 <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> } [, ... ]9 | WINDOW10 | { IMMUTABLE | STABLE | VOLATILE }11 | [ NOT ] LEAKPROOF12 | { 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>22 } ...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 a26 new function, or replace an existing definition.27 To be able to define a function, the user must have the28 <code class="literal">USAGE</code> privilege on the language.29 </p><p>30 If a schema name is included, then the function is created in the31 specified schema. Otherwise it is created in the current schema.32 The name of the new function must not match any existing function or procedure33 with the same input argument types in the same schema. However,34 functions and procedures of different argument types can share a name (this is35 called <em class="firstterm">overloading</em>).36 </p><p>37 To replace the current definition of an existing function, use38 <code class="command">CREATE OR REPLACE FUNCTION</code>. It is not possible39 to change the name or argument types of a function this way (if you40 tried, you would actually be creating a new, distinct function).41 Also, <code class="command">CREATE OR REPLACE FUNCTION</code> will not let42 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 any45 <code class="literal">OUT</code> parameters except by dropping the function.)46 </p><p>47 When <code class="command">CREATE OR REPLACE FUNCTION</code> is used to replace an48 existing function, the ownership and permissions of the function49 do not change. All other function properties are assigned the50 values specified or implied in the command. You must own the function51 to replace it (this includes being a member of the owning role).52 </p><p>53 If you drop and then recreate a function, the new function is not54 the same entity as the old; you will have to drop existing rules, views,55 triggers, etc. that refer to the old function. Use56 <code class="command">CREATE OR REPLACE FUNCTION</code> to change a function57 definition without breaking objects that refer to the function.58 Also, <code class="command">ALTER FUNCTION</code> can be used to change most of the59 auxiliary properties of an existing function.60 </p><p>61 The user that creates the function becomes the owner of the function.62 </p><p>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.65 </p><p>66 Refer to <a class="xref" href="xfunc.html" title="38.3. User-Defined Functions">Section 38.3</a> for further information on writing67 functions.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 used76 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 the80 name of an input argument is just extra documentation, so far as81 the function itself is concerned; but you can use input argument names82 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 name83 of an output argument is significant, because it defines the column84 name in the result row type. (If you omit the name for an output85 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 (optionally88 schema-qualified), if any. The argument types can be base, composite,89 or domain types, or can reference the type of a table column.90 </p><p>91 Depending on the implementation language it might also be allowed92 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 either94 incompletely specified, or outside the set of ordinary SQL data types.95 </p><p>96 The type of a column is referenced by writing97 <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 of99 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 is102 not specified. The expression has to be coercible to the103 argument type of the parameter.104 Only input (including <code class="literal">INOUT</code>) parameters can have a default105 value. All input parameters following a106 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 type109 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 allowed112 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, specify114 <code class="type">void</code> as the return type.115 </p><p>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, it118 must agree with the result type implied by the output parameters:119 <code class="literal">RECORD</code> if there are multiple output parameters, or120 the same type as the single output parameter.121 </p><p>122 The <code class="literal">SETOF</code>123 modifier indicates that the function will return a set of124 items, rather than a single item.125 </p><p>126 The type of a column is referenced by writing127 <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 named131 <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>135 syntax.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-defined140 procedural language, e.g., <code class="literal">plpgsql</code>. The default is141 <code class="literal">sql</code> if <em class="replaceable"><code>sql_body</code></em> is specified. Enclosing the142 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. Transforms145 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 language147 implementations usually have hardcoded knowledge of the built-in types,148 so those don't need to be listed here. If a procedural language149 implementation does not know how to handle a type and no transform is150 supplied, it will fall back to a default behavior for converting data151 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 a153 <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 when156 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 behavior159 of the function. At most one choice160 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 function163 cannot modify the database and always164 returns the same result when given the same argument values; that165 is, it does not do database lookups or otherwise use information not166 directly present in its argument list. If this option is given,167 any call of the function with all-constant arguments can be168 immediately replaced with the function value.169 </p><p><code class="literal">STABLE</code> indicates that the function170 cannot modify the database,171 and that within a single table scan it will consistently172 return the same result for the same argument values, but that its173 result could change across SQL statements. This is the appropriate174 selection for functions whose results depend on database lookups,175 parameter variables (such as the current time zone), etc. (It is176 inappropriate for <code class="literal">AFTER</code> triggers that wish to177 query rows modified by the current command.) Also note178 that the <code class="function">current_timestamp</code> family of functions qualify179 as stable, since their values do not change within a transaction.180 </p><p><code class="literal">VOLATILE</code> indicates that the function value can181 change even within a single table scan, so no optimizations can be182 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 has185 side-effects must be classified volatile, even if its result is quite186 predictable, to prevent calls from being optimized away; an example is187 <code class="literal">setval()</code>.188 </p><p>189 For additional details see <a class="xref" href="xfunc-volatility.html" title="38.7. Function Volatility Categories">Section 38.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 side192 effects. It reveals no information about its arguments other than by193 its return value. For example, a function which throws an error message194 for some argument values but not others, or which includes the argument195 values in any error message, is not leakproof. This affects how the196 system executes queries against views created with the197 <code class="literal">security_barrier</code> option or tables with row level198 security enabled. The system will enforce conditions from security199 policies and security barrier views before any user-supplied conditions200 from the query itself that contain non-leakproof functions, in order to201 prevent the inadvertent exposure of data. Functions and operators202 marked as leakproof are assumed to be trustworthy, and may be executed203 before conditions from security policies and security barrier views.204 In addition, functions which do not take arguments or which are not205 passed any arguments from the security barrier view or table do not have206 to be marked as leakproof to be executed before security conditions. See207 <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="41.5. Rules and Privileges">Section 41.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) indicates210 that the function will be called normally when some of its211 arguments are null. It is then the function author's212 responsibility to check for null values if necessary and respond213 appropriately.214 </p><p><code class="literal">RETURNS NULL ON NULL INPUT</code> or215 <code class="literal">STRICT</code> indicates that the function always216 returns null whenever any of its arguments are null. If this217 parameter is specified, the function is not executed when there218 are null arguments; instead a null result is assumed219 automatically.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 function221 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 the224 privileges of the user that owns it. For information on how to225 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>.227 </p><p>228 The key word <code class="literal">EXTERNAL</code> is allowed for SQL229 conformance, but it is optional since, unlike in SQL, this feature230 applies to all functions not only external ones.231 </p></dd><dt><span class="term"><code class="literal">PARALLEL</code></span></dt><dd><p><code class="literal">PARALLEL UNSAFE</code> indicates that the function232 can't be executed in parallel mode and the presence of such a233 function in an SQL statement forces a serial execution plan. This is234 the default. <code class="literal">PARALLEL RESTRICTED</code> indicates that235 the function can be executed in parallel mode, but the execution is236 restricted to parallel group leader. <code class="literal">PARALLEL SAFE</code>237 indicates that the function is safe to run in parallel mode without238 restriction.239 </p><p>240 Functions should be labeled parallel unsafe if they modify any database241 state, or if they make changes to the transaction such as using242 sub-transactions, or if they access sequences or attempt to make243 persistent changes to settings (e.g., <code class="literal">setval</code>). They should244 be labeled as parallel restricted if they access temporary tables,245 client connection state, cursors, prepared statements, or miscellaneous246 backend-local state which the system cannot synchronize in parallel mode247 (e.g., <code class="literal">setseed</code> cannot be executed other than by the group248 leader because a change made by another process would not be reflected249 in the leader). In general, if a function is labeled as being safe when250 it is restricted or unsafe, or if it is labeled as being restricted when251 it is in fact unsafe, it may throw errors or produce wrong answers252 when used in a parallel query. C-language functions could in theory253 exhibit totally undefined behavior if mislabeled, since there is no way254 for the system to protect itself against arbitrary C code, but in most255 likely cases the result will be no worse than for any other function.256 If in doubt, functions should be labeled as <code class="literal">UNSAFE</code>, which is257 the default.258 </p></dd><dt><span class="term"><code class="literal">COST</code> <em class="replaceable"><code>execution_cost</code></em></span></dt><dd><p>259 A positive number giving the estimated execution cost for the function,260 in units of <a class="xref" href="runtime-config-query.html#GUC-CPU-OPERATOR-COST">cpu_operator_cost</a>. If the function261 returns a set, this is the cost per returned row. If the cost is262 not specified, 1 unit is assumed for C-language and internal functions,263 and 100 units for functions in all other languages. Larger values264 cause the planner to try to avoid evaluating the function more often265 than necessary.266 </p></dd><dt><span class="term"><code class="literal">ROWS</code> <em class="replaceable"><code>result_rows</code></em></span></dt><dd><p>267 A positive number giving the estimated number of rows that the planner268 should expect the function to return. This is only allowed when the269 function is declared to return a set. The default assumption is270 1000 rows.271 </p></dd><dt><span class="term"><code class="literal">SUPPORT</code> <em class="replaceable"><code>support_function</code></em></span></dt><dd><p>272 The name (optionally schema-qualified) of a <em class="firstterm">planner support273 function</em> to use for this function. See274 <a class="xref" href="xfunc-optimization.html" title="38.11. Function Optimization Information">Section 38.11</a> for details.275 You must be superuser to use this option.276 </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>277 The <code class="literal">SET</code> clause causes the specified configuration278 parameter to be set to the specified value when the function is279 entered, and then restored to its prior value when the function exits.280 <code class="literal">SET FROM CURRENT</code> saves the value of the parameter that281 is current when <code class="command">CREATE FUNCTION</code> is executed as the value282 to be applied when the function is entered.283 </p><p>284 If a <code class="literal">SET</code> clause is attached to a function, then285 the effects of a <code class="command">SET LOCAL</code> command executed inside the286 function for the same variable are restricted to the function: the287 configuration parameter's prior value is still restored at function exit.288 However, an ordinary289 <code class="command">SET</code> command (without <code class="literal">LOCAL</code>) overrides the290 <code class="literal">SET</code> clause, much as it would do for a previous <code class="command">SET291 LOCAL</code> command: the effects of such a command will persist after292 function exit, unless the current transaction is rolled back.293 </p><p>294 See <a class="xref" href="sql-set.html" title="SET"><span class="refentrytitle">SET</span></a> and295 <a class="xref" href="runtime-config.html" title="Chapter 20. Server Configuration">Chapter 20</a>296 for more information about allowed parameter names and values.297 </p></dd><dt><span class="term"><em class="replaceable"><code>definition</code></em></span></dt><dd><p>298 A string constant defining the function; the meaning depends on the299 language. It can be an internal function name, the path to an300 object file, an SQL command, or text in a procedural language.301 </p><p>302 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 definition303 string, rather than the normal single quote syntax. Without dollar304 quoting, any single quotes or backslashes in the function definition must305 be escaped by doubling them.306 </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>307 This form of the <code class="literal">AS</code> clause is used for308 dynamically loadable C language functions when the function name309 in the C language source code is not the same as the name of310 the SQL function. The string <em class="replaceable"><code>obj_file</code></em> is the name of the shared311 library file containing the compiled C function, and is interpreted312 as for the <a class="link" href="sql-load.html" title="LOAD"><code class="command">LOAD</code></a> command. The string313 <em class="replaceable"><code>link_symbol</code></em> is the314 function's link symbol, that is, the name of the function in the C315 language source code. If the link symbol is omitted, it is assumed to316 be the same as the name of the SQL function being defined. The C names317 of all functions must be different, so you must give overloaded C318 functions different C names (for example, use the argument types as319 part of the C names).320 </p><p>321 When repeated <code class="command">CREATE FUNCTION</code> calls refer to322 the same object file, the file is only loaded once per session.323 To unload and324 reload the file (perhaps during development), start a new session.325 </p></dd><dt><span class="term"><em class="replaceable"><code>sql_body</code></em></span></dt><dd><p>326 The body of a <code class="literal">LANGUAGE SQL</code> function. This can327 either be a single statement328</p><pre class="programlisting">329RETURN <em class="replaceable"><code>expression</code></em>330</pre><p>331 or a block332</p><pre class="programlisting">333BEGIN ATOMIC334 <em class="replaceable"><code>statement</code></em>;335 <em class="replaceable"><code>statement</code></em>;336 ...337 <em class="replaceable"><code>statement</code></em>;338END339</pre><p>340 </p><p>341 This is similar to writing the text of the function body as a string342 constant (see <em class="replaceable"><code>definition</code></em> above), but there343 are some differences: This form only works for <code class="literal">LANGUAGE344 SQL</code>, the string constant form works for all languages. This345 form is parsed at function definition time, the string constant form is346 parsed at execution time; therefore this form cannot support347 polymorphic argument types and other constructs that are not resolvable348 at function definition time. This form tracks dependencies between the349 function and objects used in the function body, so <code class="literal">DROP350 ... CASCADE</code> will work correctly, whereas the form using351 string literals may leave dangling functions. Finally, this form is352 more compatible with the SQL standard and other SQL implementations.353 </p></dd></dl></div></div><div class="refsect1" id="SQL-CREATEFUNCTION-OVERLOADING"><h2>Overloading</h2><p>354 <span class="productname">PostgreSQL</span> allows function355 <em class="firstterm">overloading</em>; that is, the same name can be356 used for several different functions so long as they have distinct357 input argument types. Whether or not you use it, this capability entails358 security precautions when calling functions in databases where some users359 mistrust other users; see <a class="xref" href="typeconv-func.html" title="10.3. Functions">Section 10.3</a>.360 </p><p>361 Two functions are considered the same if they have the same names and362 <span class="emphasis"><em>input</em></span> argument types, ignoring any <code class="literal">OUT</code>363 parameters. Thus for example these declarations conflict:364</p><pre class="programlisting">365CREATE FUNCTION foo(int) ...366CREATE FUNCTION foo(int, out text) ...367</pre><p>368 </p><p>369 Functions that have different argument type lists will not be considered370 to conflict at creation time, but if defaults are provided they might371 conflict in use. For example, consider372</p><pre class="programlisting">373CREATE FUNCTION foo(int) ...374CREATE FUNCTION foo(int, int default 42) ...375</pre><p>376 A call <code class="literal">foo(10)</code> will fail due to the ambiguity about which377 function should be called.378 </p></div><div class="refsect1" id="SQL-CREATEFUNCTION-NOTES"><h2>Notes</h2><p>379 The full <acronym class="acronym">SQL</acronym> type syntax is allowed for380 declaring a function's arguments and return value. However,381 parenthesized type modifiers (e.g., the precision field for382 type <code class="type">numeric</code>) are discarded by <code class="command">CREATE FUNCTION</code>.383 Thus for example384 <code class="literal">CREATE FUNCTION foo (varchar(10)) ...</code>385 is exactly the same as386 <code class="literal">CREATE FUNCTION foo (varchar) ...</code>.387 </p><p>388 When replacing an existing function with <code class="command">CREATE OR REPLACE389 FUNCTION</code>, there are restrictions on changing parameter names.390 You cannot change the name already assigned to any input parameter391 (although you can add names to parameters that had none before).392 If there is more than one output parameter, you cannot change the393 names of the output parameters, because that would change the394 column names of the anonymous composite type that describes the395 function's result. These restrictions are made to ensure that396 existing calls of the function do not stop working when it is replaced.397 </p><p>398 If a function is declared <code class="literal">STRICT</code> with a <code class="literal">VARIADIC</code>399 argument, the strictness check tests that the variadic array <span class="emphasis"><em>as400 a whole</em></span> is non-null. The function will still be called if the401 array has null elements.402 </p></div><div class="refsect1" id="SQL-CREATEFUNCTION-EXAMPLES"><h2>Examples</h2><p>403 Add two integers using an SQL function:404</p><pre class="programlisting">405CREATE FUNCTION add(integer, integer) RETURNS integer406 AS 'select $1 + $2;'407 LANGUAGE SQL408 IMMUTABLE409 RETURNS NULL ON NULL INPUT;410</pre><p>411 The same function written in a more SQL-conforming style, using argument412 names and an unquoted body:413</p><pre class="programlisting">414CREATE FUNCTION add(a integer, b integer) RETURNS integer415 LANGUAGE SQL416 IMMUTABLE417 RETURNS NULL ON NULL INPUT418 RETURN a + b;419</pre><p>420 </p><p>421 Increment an integer, making use of an argument name, in422 <span class="application">PL/pgSQL</span>:423</p><pre class="programlisting">424CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$425 BEGIN426 RETURN i + 1;427 END;428$$ LANGUAGE plpgsql;429</pre><p>430 </p><p>431 Return a record containing multiple output parameters:432</p><pre class="programlisting">433CREATE FUNCTION dup(in int, out f1 int, out f2 text)434 AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$435 LANGUAGE SQL;436 437SELECT * FROM dup(42);438</pre><p>439 You can do the same thing more verbosely with an explicitly named440 composite type:441</p><pre class="programlisting">442CREATE TYPE dup_result AS (f1 int, f2 text);443 444CREATE FUNCTION dup(int) RETURNS dup_result445 AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$446 LANGUAGE SQL;447 448SELECT * FROM dup(42);449</pre><p>450 Another way to return multiple columns is to use a <code class="literal">TABLE</code>451 function:452</p><pre class="programlisting">453CREATE FUNCTION dup(int) RETURNS TABLE(f1 int, f2 text)454 AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$455 LANGUAGE SQL;456 457SELECT * FROM dup(42);458</pre><p>459 However, a <code class="literal">TABLE</code> function is different from the460 preceding examples, because it actually returns a <span class="emphasis"><em>set</em></span>461 of records, not just one record.462 </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>463 Because a <code class="literal">SECURITY DEFINER</code> function is executed464 with the privileges of the user that owns it, care is needed to465 ensure that the function cannot be misused. For security,466 <a class="xref" href="runtime-config-client.html#GUC-SEARCH-PATH">search_path</a> should be set to exclude any schemas467 writable by untrusted users. This prevents468 malicious users from creating objects (e.g., tables, functions, and469 operators) that mask objects intended to be used by the function.470 Particularly important in this regard is the471 temporary-table schema, which is searched first by default, and472 is normally writable by anyone. A secure arrangement can be obtained473 by forcing the temporary schema to be searched last. To do this,474 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>.475 This function illustrates safe usage:476 477</p><pre class="programlisting">478CREATE FUNCTION check_password(uname TEXT, pass TEXT)479RETURNS BOOLEAN AS $$480DECLARE passed BOOLEAN;481BEGIN482 SELECT (pwd = $2) INTO passed483 FROM pwds484 WHERE username = $1;485 486 RETURN passed;487END;488$$ LANGUAGE plpgsql489 SECURITY DEFINER490 -- Set a secure search_path: trusted schema(s), then 'pg_temp'.491 SET search_path = admin, pg_temp;492</pre><p>493 494 This function's intention is to access a table <code class="literal">admin.pwds</code>.495 But without the <code class="literal">SET</code> clause, or with a <code class="literal">SET</code> clause496 mentioning only <code class="literal">admin</code>, the function could be subverted by497 creating a temporary table named <code class="literal">pwds</code>.498 </p><p>499 If the security definer function intends to create roles, and if it500 is running as a non-superuser, <code class="varname">createrole_self_grant</code>501 should also be set to a known value using the <code class="literal">SET</code>502 clause.503 </p><p>504 Another point to keep in mind is that by default, execute privilege505 is granted to <code class="literal">PUBLIC</code> for newly created functions506 (see <a class="xref" href="ddl-priv.html" title="5.7. Privileges">Section 5.7</a> for more507 information). Frequently you will wish to restrict use of a security508 definer function to only some users. To do that, you must revoke509 the default <code class="literal">PUBLIC</code> privileges and then grant execute510 privilege selectively. To avoid having a window where the new function511 is accessible to all, create it and set the privileges within a single512 transaction. For example:513 </p><pre class="programlisting">514BEGIN;515CREATE FUNCTION check_password(uname TEXT, pass TEXT) ... SECURITY DEFINER;516REVOKE ALL ON FUNCTION check_password(uname TEXT, pass TEXT) FROM PUBLIC;517GRANT EXECUTE ON FUNCTION check_password(uname TEXT, pass TEXT) TO admins;518COMMIT;519</pre></div><div class="refsect1" id="SQL-CREATEFUNCTION-COMPAT"><h2>Compatibility</h2><p>520 A <code class="command">CREATE FUNCTION</code> command is defined in the SQL521 standard. The <span class="productname">PostgreSQL</span> implementation can be522 used in a compatible way but has many extensions. Conversely, the SQL523 standard specifies a number of optional features that are not implemented524 in <span class="productname">PostgreSQL</span>.525 </p><p>526 The following are important compatibility issues:527 528 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>529 <code class="literal">OR REPLACE</code> is a PostgreSQL extension.530 </p></li><li class="listitem"><p>531 For compatibility with some other database systems, <em class="replaceable"><code>argmode</code></em> can be written either before or532 after <em class="replaceable"><code>argname</code></em>. But only533 the first way is standard-compliant.534 </p></li><li class="listitem"><p>535 For parameter defaults, the SQL standard specifies only the syntax with536 the <code class="literal">DEFAULT</code> key word. The syntax with537 <code class="literal">=</code> is used in T-SQL and Firebird.538 </p></li><li class="listitem"><p>539 The <code class="literal">SETOF</code> modifier is a PostgreSQL extension.540 </p></li><li class="listitem"><p>541 Only <code class="literal">SQL</code> is standardized as a language.542 </p></li><li class="listitem"><p>543 All other attributes except <code class="literal">CALLED ON NULL INPUT</code> and544 <code class="literal">RETURNS NULL ON NULL INPUT</code> are not standardized.545 </p></li><li class="listitem"><p>546 For the body of <code class="literal">LANGUAGE SQL</code> functions, the SQL547 standard only specifies the <em class="replaceable"><code>sql_body</code></em> form.548 </p></li></ul></div><p>549 </p><p>550 Simple <code class="literal">LANGUAGE SQL</code> functions can be written in a way551 that is both standard-conforming and portable to other implementations.552 More complex functions using advanced features, optimization attributes, or553 other languages will necessarily be specific to PostgreSQL in a significant554 way.555 </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 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> CREATE GROUP</td></tr></table></div></body></html>