Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
sql-createfunction.html555 linesDownload Raw Back to html
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>
codekingpro/portable-devtools · Team Ai