doc/src/sgml/ref/create_function.sgml

   1 <!--
   2 $PostgreSQL$
   3 -->
   4
   5 <refentry id="SQL-CREATEFUNCTION">
   6  <refmeta>
   7   <refentrytitle id="SQL-CREATEFUNCTION-TITLE">CREATE FUNCTION</refentrytitle>
   8   <manvolnum>7</manvolnum>
   9   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  10  </refmeta>
  11
  12  <refnamediv>
  13   <refname>CREATE FUNCTION</refname>
  14   <refpurpose>define a new function</refpurpose>
  15  </refnamediv>
  16
  17  <indexterm zone="sql-createfunction">
  18   <primary>CREATE FUNCTION</primary>
  19  </indexterm>
  20
  21  <refsynopsisdiv>
  22 <synopsis>
  23 CREATE [ OR REPLACE ] FUNCTION
  24     <replaceable class="parameter">name</replaceable> ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">argtype</replaceable> [ { DEFAULT | = } <replaceable class="parameter">defexpr</replaceable> ] [, ...] ] )
  25     [ RETURNS <replaceable class="parameter">rettype</replaceable>
  26       | RETURNS TABLE ( <replaceable class="parameter">colname</replaceable> <replaceable class="parameter">coltype</replaceable> [, ...] ) ]
  27   { LANGUAGE <replaceable class="parameter">langname</replaceable>
  28     | WINDOW
  29     | IMMUTABLE | STABLE | VOLATILE
  30     | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
  31     | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
  32     | COST <replaceable class="parameter">execution_cost</replaceable>
  33     | ROWS <replaceable class="parameter">result_rows</replaceable>
  34     | SET <replaceable class="parameter">configuration_parameter</replaceable> { TO <replaceable class="parameter">value</replaceable> | = <replaceable class="parameter">value</replaceable> | FROM CURRENT }
  35     | AS '<replaceable class="parameter">definition</replaceable>'
  36     | AS '<replaceable class="parameter">obj_file</replaceable>', '<replaceable class="parameter">link_symbol</replaceable>'
  37   } ...
  38     [ WITH ( <replaceable class="parameter">attribute</replaceable> [, ...] ) ]
  39 </synopsis>
  40  </refsynopsisdiv>
  41
  42  <refsect1 id="sql-createfunction-description">
  43   <title>Description</title>
  44
  45   <para>
  46    <command>CREATE FUNCTION</command> defines a new function.
  47    <command>CREATE OR REPLACE FUNCTION</command> will either create a
  48    new function, or replace an existing definition.
  49   </para>
  50
  51   <para>
  52    If a schema name is included, then the function is created in the
  53    specified schema.  Otherwise it is created in the current schema.
  54    The name of the new function must not match any existing function
  55    with the same input argument types in the same schema.  However,
  56    functions of different argument types can share a name (this is
  57    called <firstterm>overloading</>).
  58   </para>
  59
  60   <para>
  61    To replace the current definition of an existing function, use
  62    <command>CREATE OR REPLACE FUNCTION</command>.  It is not possible
  63    to change the name or argument types of a function this way (if you
  64    tried, you would actually be creating a new, distinct function).
  65    Also, <command>CREATE OR REPLACE FUNCTION</command> will not let
  66    you change the return type of an existing function.  To do that,
  67    you must drop and recreate the function.  (When using <literal>OUT</>
  68    parameters, that means you cannot change the names or types of any
  69    <literal>OUT</> parameters except by dropping the function.)
  70   </para>
  71
  72   <para>
  73    If you drop and then recreate a function, the new function is not
  74    the same entity as the old; you will have to drop existing rules, views,
  75    triggers, etc. that refer to the old function.  Use
  76    <command>CREATE OR REPLACE FUNCTION</command> to change a function
  77    definition without breaking objects that refer to the function.
  78    Also, <command>ALTER FUNCTION</> can be used to change most of the
  79    auxiliary properties of an existing function.
  80   </para>
  81
  82   <para>
  83    The user that creates the function becomes the owner of the function.
  84   </para>
  85  </refsect1>
  86
  87  <refsect1>
  88   <title>Parameters</title>
  89
  90    <variablelist>
  91
  92     <varlistentry>
  93      <term><replaceable class="parameter">name</replaceable></term>
  94
  95      <listitem>
  96       <para>
  97        The name (optionally schema-qualified) of the function to create.
  98       </para>
  99      </listitem>
 100     </varlistentry>
 101
 102     <varlistentry>
 103      <term><replaceable class="parameter">argmode</replaceable></term>
 104
 105      <listitem>
 106       <para>
 107        The mode of an argument: <literal>IN</>, <literal>OUT</>,
 108        <literal>INOUT</>, or <literal>VARIADIC</>.
 109        If omitted, the default is <literal>IN</>.
 110        Only <literal>OUT</> arguments can follow a <literal>VARIADIC</> one.
 111        Also, <literal>OUT</> and <literal>INOUT</> arguments cannot be used
 112        together with the <literal>RETURNS TABLE</> notation.
 113       </para>
 114      </listitem>
 115     </varlistentry>
 116
 117     <varlistentry>
 118      <term><replaceable class="parameter">argname</replaceable></term>
 119
 120      <listitem>
 121       <para>
 122        The name of an argument. Some languages (currently only PL/pgSQL) let
 123        you use the name in the function body.  For other languages the
 124        name of an input argument is just extra documentation.  But the name
 125        of an output argument is significant, since it defines the column
 126        name in the result row type.  (If you omit the name for an output
 127        argument, the system will choose a default column name.)
 128       </para>
 129      </listitem>
 130     </varlistentry>
 131
 132     <varlistentry>
 133      <term><replaceable class="parameter">argtype</replaceable></term>
 134
 135      <listitem>
 136       <para>
 137        The data type(s) of the function's arguments (optionally
 138        schema-qualified), if any. The argument types can be base, composite,
 139        or domain types, or can reference the type of a table column.
 140       </para>
 141       <para>
 142        Depending on the implementation language it might also be allowed
 143        to specify <quote>pseudotypes</> such as <type>cstring</>.
 144        Pseudotypes indicate that the actual argument type is either
 145        incompletely specified, or outside the set of ordinary SQL data types.
 146       </para>
 147       <para>
 148        The type of a column is referenced by writing
 149        <literal><replaceable
 150        class="parameter">tablename</replaceable>.<replaceable
 151        class="parameter">columnname</replaceable>%TYPE</literal>.
 152        Using this feature can sometimes help make a function independent of
 153        changes to the definition of a table.
 154       </para>
 155      </listitem>
 156     </varlistentry>
 157
 158     <varlistentry>
 159      <term><replaceable class="parameter">defexpr</replaceable></term>
 160
 161      <listitem>
 162       <para>
 163        An expression to be used as default value if the parameter is
 164        not specified.  The expression has to be coercible to the
 165        argument type of the parameter.
 166        Only input (including <literal>INOUT</>) parameters can have a default
 167         value.  All input parameters following a
 168        parameter with a default value must have default values as well.
 169       </para>
 170      </listitem>
 171     </varlistentry>
 172
 173     <varlistentry>
 174      <term><replaceable class="parameter">rettype</replaceable></term>
 175
 176      <listitem>
 177       <para>
 178        The return data type (optionally schema-qualified). The return type
 179        can be a base, composite, or domain type,
 180        or can reference the type of a table column.
 181        Depending on the implementation language it might also be allowed
 182        to specify <quote>pseudotypes</> such as <type>cstring</>.
 183        If the function is not supposed to return a value, specify
 184        <type>void</> as the return type.
 185       </para>
 186       <para>
 187        When there are <literal>OUT</> or <literal>INOUT</> parameters,
 188        the <literal>RETURNS</> clause can be omitted.  If present, it
 189        must agree with the result type implied by the output parameters:
 190        <literal>RECORD</> if there are multiple output parameters, or
 191        the same type as the single output parameter.
 192       </para>
 193       <para>
 194        The <literal>SETOF</literal>
 195        modifier indicates that the function will return a set of
 196        items, rather than a single item.
 197       </para>
 198       <para>
 199        The type of a column is referenced by writing
 200        <literal><replaceable
 201        class="parameter">tablename</replaceable>.<replaceable
 202        class="parameter">columnname</replaceable>%TYPE</literal>.
 203       </para>
 204      </listitem>
 205     </varlistentry>
 206
 207     <varlistentry>
 208      <term><replaceable class="parameter">colname</replaceable></term>
 209
 210      <listitem>
 211       <para>
 212        The name of an output column in the <literal>RETURNS TABLE</>
 213        syntax.  This is effectively another way of declaring a named
 214        <literal>OUT</> parameter, except that <literal>RETURNS TABLE</>
 215        also implies <literal>RETURNS SETOF</>.
 216       </para>
 217      </listitem>
 218     </varlistentry>
 219
 220     <varlistentry>
 221      <term><replaceable class="parameter">coltype</replaceable></term>
 222
 223      <listitem>
 224       <para>
 225        The data type of an output column in the <literal>RETURNS TABLE</>
 226        syntax.
 227       </para>
 228      </listitem>
 229     </varlistentry>
 230
 231     <varlistentry>
 232      <term><replaceable class="parameter">langname</replaceable></term>
 233
 234      <listitem>
 235       <para>
 236        The name of the language that the function is implemented in.
 237        Can be <literal>SQL</literal>, <literal>C</literal>,
 238        <literal>internal</literal>, or the name of a user-defined
 239        procedural language.  For backward compatibility,
 240        the name can be enclosed by single quotes.
 241       </para>
 242      </listitem>
 243     </varlistentry>
 244
 245     <varlistentry>
 246      <term><literal>WINDOW</literal></term>
 247
 248      <listitem>
 249       <para>
 250        <literal>WINDOW</literal> indicates that the function is a
 251        <firstterm>window function</> rather than a plain function.
 252        This is currently only useful for functions written in C.
 253        The <literal>WINDOW</> attribute cannot be changed when
 254        replacing an existing function definition.
 255       </para>
 256      </listitem>
 257     </varlistentry>
 258
 259     <varlistentry>
 260      <term><literal>IMMUTABLE</literal></term>
 261      <term><literal>STABLE</literal></term>
 262      <term><literal>VOLATILE</literal></term>
 263
 264      <listitem>
 265       <para>
 266        These attributes inform the query optimizer about the behavior
 267        of the function.  At most one choice
 268        can be specified.  If none of these appear,
 269        <literal>VOLATILE</literal> is the default assumption.
 270       </para>
 271
 272       <para>
 273        <literal>IMMUTABLE</literal> indicates that the function
 274        cannot modify the database and always
 275        returns the same result when given the same argument values; that
 276        is, it does not do database lookups or otherwise use information not
 277        directly present in its argument list.  If this option is given,
 278        any call of the function with all-constant arguments can be
 279        immediately replaced with the function value.
 280       </para>
 281
 282       <para>
 283        <literal>STABLE</literal> indicates that the function
 284        cannot modify the database,
 285        and that within a single table scan it will consistently
 286        return the same result for the same argument values, but that its
 287        result could change across SQL statements.  This is the appropriate
 288        selection for functions whose results depend on database lookups,
 289        parameter variables (such as the current time zone), etc.  Also note
 290        that the <function>current_timestamp</> family of functions qualify
 291        as stable, since their values do not change within a transaction.
 292       </para>
 293
 294       <para>
 295        <literal>VOLATILE</literal> indicates that the function value can
 296        change even within a single table scan, so no optimizations can be
 297        made.  Relatively few database functions are volatile in this sense;
 298        some examples are <literal>random()</>, <literal>currval()</>,
 299        <literal>timeofday()</>.  But note that any function that has
 300        side-effects must be classified volatile, even if its result is quite
 301        predictable, to prevent calls from being optimized away; an example is
 302        <literal>setval()</>.
 303       </para>
 304
 305       <para>
 306        For additional details see <xref linkend="xfunc-volatility">.
 307       </para>
 308      </listitem>
 309     </varlistentry>
 310
 311     <varlistentry>
 312      <term><literal>CALLED ON NULL INPUT</literal></term>
 313      <term><literal>RETURNS NULL ON NULL INPUT</literal></term>
 314      <term><literal>STRICT</literal></term>
 315
 316      <listitem>
 317       <para>
 318        <literal>CALLED ON NULL INPUT</literal> (the default) indicates
 319        that the function will be called normally when some of its
 320        arguments are null.  It is then the function author's
 321        responsibility to check for null values if necessary and respond
 322        appropriately.
 323       </para>
 324
 325       <para>
 326        <literal>RETURNS NULL ON NULL INPUT</literal> or
 327        <literal>STRICT</literal> indicates that the function always
 328        returns null whenever any of its arguments are null.  If this
 329        parameter is specified, the function is not executed when there
 330        are null arguments; instead a null result is assumed
 331        automatically.
 332       </para>
 333      </listitem>
 334     </varlistentry>
 335
 336    <varlistentry>
 337     <term><literal><optional>EXTERNAL</optional> SECURITY INVOKER</literal></term>
 338     <term><literal><optional>EXTERNAL</optional> SECURITY DEFINER</literal></term>
 339
 340     <listitem>
 341      <para>
 342       <literal>SECURITY INVOKER</literal> indicates that the function
 343       is to be executed with the privileges of the user that calls it.
 344       That is the default.  <literal>SECURITY DEFINER</literal>
 345       specifies that the function is to be executed with the
 346       privileges of the user that created it.
 347      </para>
 348
 349      <para>
 350       The key word <literal>EXTERNAL</literal> is allowed for SQL
 351       conformance, but it is optional since, unlike in SQL, this feature
 352       applies to all functions not only external ones.
 353      </para>
 354     </listitem>
 355    </varlistentry>
 356
 357     <varlistentry>
 358      <term><replaceable class="parameter">execution_cost</replaceable></term>
 359
 360      <listitem>
 361       <para>
 362        A positive number giving the estimated execution cost for the function,
 363        in units of <xref linkend="guc-cpu-operator-cost">.  If the function
 364        returns a set, this is the cost per returned row.  If the cost is
 365        not specified, 1 unit is assumed for C-language and internal functions,
 366        and 100 units for functions in all other languages.  Larger values
 367        cause the planner to try to avoid evaluating the function more often
 368        than necessary.
 369       </para>
 370      </listitem>
 371     </varlistentry>
 372
 373     <varlistentry>
 374      <term><replaceable class="parameter">result_rows</replaceable></term>
 375
 376      <listitem>
 377       <para>
 378        A positive number giving the estimated number of rows that the planner
 379        should expect the function to return.  This is only allowed when the
 380        function is declared to return a set.  The default assumption is
 381        1000 rows.
 382       </para>
 383      </listitem>
 384     </varlistentry>
 385
 386     <varlistentry>
 387      <term><replaceable>configuration_parameter</replaceable></term>
 388      <term><replaceable>value</replaceable></term>
 389      <listitem>
 390       <para>
 391        The <literal>SET</> clause causes the specified configuration
 392        parameter to be set to the specified value when the function is
 393        entered, and then restored to its prior value when the function exits.
 394        <literal>SET FROM CURRENT</> saves the session's current value of
 395        the parameter as the value to be applied when the function is entered.
 396       </para>
 397
 398       <para>
 399        See <xref linkend="sql-set" endterm="sql-set-title"> and
 400        <xref linkend="runtime-config">
 401        for more information about allowed parameter names and values.
 402       </para>
 403      </listitem>
 404     </varlistentry>
 405
 406     <varlistentry>
 407      <term><replaceable class="parameter">definition</replaceable></term>
 408
 409      <listitem>
 410       <para>
 411        A string constant defining the function; the meaning depends on the
 412        language.  It can be an internal function name, the path to an
 413        object file, an SQL command, or text in a procedural language.
 414       </para>
 415      </listitem>
 416     </varlistentry>
 417
 418     <varlistentry>
 419      <term><literal><replaceable class="parameter">obj_file</replaceable>, <replaceable class="parameter">link_symbol</replaceable></literal></term>
 420
 421      <listitem>
 422       <para>
 423        This form of the <literal>AS</literal> clause is used for
 424        dynamically loadable C language functions when the function name
 425        in the C language source code is not the same as the name of
 426        the SQL function. The string <replaceable
 427        class="parameter">obj_file</replaceable> is the name of the
 428        file containing the dynamically loadable object, and
 429        <replaceable class="parameter">link_symbol</replaceable> is the
 430        function's link symbol, that is, the name of the function in the C
 431        language source code.  If the link symbol is omitted, it is assumed
 432        to be the same as the name of the SQL function being defined.
 433       </para>
 434      </listitem>
 435     </varlistentry>
 436
 437     <varlistentry>
 438      <term><replaceable class="parameter">attribute</replaceable></term>
 439
 440      <listitem>
 441       <para>
 442        The historical way to specify optional pieces of information
 443        about the function.  The following attributes can appear here:
 444
 445       <variablelist>
 446        <varlistentry>
 447         <term><literal>isStrict</></term>
 448         <listitem>
 449          <para>
 450           Equivalent to <literal>STRICT</literal> or <literal>RETURNS NULL ON NULL INPUT</literal>.
 451          </para>
 452         </listitem>
 453        </varlistentry>
 454
 455        <varlistentry>
 456         <term><literal>isCachable</></term>
 457         <listitem>
 458          <para>
 459           <literal>isCachable</literal> is an obsolete equivalent of
 460           <literal>IMMUTABLE</literal>; it's still accepted for
 461           backwards-compatibility reasons.
 462          </para>
 463         </listitem>
 464        </varlistentry>
 465
 466       </variablelist>
 467
 468       Attribute names are not case-sensitive.
 469      </para>
 470     </listitem>
 471    </varlistentry>
 472
 473    </variablelist>
 474
 475  </refsect1>
 476
 477  <refsect1 id="sql-createfunction-notes">
 478   <title>Notes</title>
 479
 480    <para>
 481     Refer to <xref linkend="xfunc"> for further information on writing
 482     functions.
 483    </para>
 484
 485    <para>
 486     The full <acronym>SQL</acronym> type syntax is allowed for
 487     input arguments and return value. However, some details of the
 488     type specification (e.g., the precision field for
 489     type <type>numeric</type>) are the responsibility of the
 490     underlying function implementation and are silently swallowed
 491     (i.e., not recognized or
 492     enforced) by the <command>CREATE FUNCTION</command> command.
 493    </para>
 494
 495    <para>
 496     <productname>PostgreSQL</productname> allows function
 497     <firstterm>overloading</firstterm>; that is, the same name can be
 498     used for several different functions so long as they have distinct
 499     input argument types.  However, the C names of all functions must be
 500     different, so you must give overloaded C functions different C
 501     names (for example, use the argument types as part of the C
 502     names).
 503    </para>
 504
 505    <para>
 506     Two functions are considered the same if they have the same names and
 507     <emphasis>input</> argument types, ignoring any <literal>OUT</>
 508     parameters.  Thus for example these declarations conflict:
 509 <programlisting>
 510 CREATE FUNCTION foo(int) ...
 511 CREATE FUNCTION foo(int, out text) ...
 512 </programlisting>
 513    </para>
 514
 515    <para>
 516     Functions that have different argument type lists will not be considered
 517     to conflict at creation time, but if defaults are provided they might
 518     conflict in use.  For example, consider
 519 <programlisting>
 520 CREATE FUNCTION foo(int) ...
 521 CREATE FUNCTION foo(int, int default 42) ...
 522 </programlisting>
 523     A call <literal>foo(10)</> will fail due to the ambiguity about which
 524     function should be called.
 525    </para>
 526
 527    <para>
 528     When repeated <command>CREATE FUNCTION</command> calls refer to
 529     the same object file, the file is only loaded once per session.
 530     To unload and
 531     reload the file (perhaps during development), use the <xref
 532     linkend="sql-load" endterm="sql-load-title"> command.
 533    </para>
 534
 535    <para>
 536     Use <xref linkend="sql-dropfunction"
 537     endterm="sql-dropfunction-title"> to remove user-defined
 538     functions.
 539    </para>
 540
 541    <para>
 542     It is often helpful to use dollar quoting (see <xref
 543     linkend="sql-syntax-dollar-quoting">) to write the function definition
 544     string, rather than the normal single quote syntax.  Without dollar
 545     quoting, any single quotes or backslashes in the function definition must
 546     be escaped by doubling them.
 547    </para>
 548
 549    <para>
 550     If a <literal>SET</> clause is attached to a function, then
 551     the effects of a <command>SET LOCAL</> command executed inside the
 552     function for the same variable are restricted to the function: the
 553     configuration parameter's prior value is still restored at function exit.
 554     However, an ordinary
 555     <command>SET</> command (without <literal>LOCAL</>) overrides the
 556     <literal>SET</> clause, much as it would do for a previous <command>SET
 557     LOCAL</> command: the effects of such a command will persist after
 558     function exit, unless the current transaction is rolled back.
 559    </para>
 560
 561    <para>
 562     To be able to define a function, the user must have the
 563     <literal>USAGE</literal> privilege on the language.
 564    </para>
 565
 566  </refsect1>
 567
 568  <refsect1 id="sql-createfunction-examples">
 569   <title>Examples</title>
 570
 571   <para>
 572    Here are some trivial examples to help you get started.  For more
 573    information and examples, see <xref linkend="xfunc">.
 574 <programlisting>
 575 CREATE FUNCTION add(integer, integer) RETURNS integer
 576     AS 'select $1 + $2;'
 577     LANGUAGE SQL
 578     IMMUTABLE
 579     RETURNS NULL ON NULL INPUT;
 580 </programlisting>
 581   </para>
 582
 583   <para>
 584    Increment an integer, making use of an argument name, in
 585    <application>PL/pgSQL</application>:
 586 <programlisting>
 587 CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$
 588         BEGIN
 589                 RETURN i + 1;
 590         END;
 591 $$ LANGUAGE plpgsql;
 592 </programlisting>
 593   </para>
 594
 595   <para>
 596    Return a record containing multiple output parameters:
 597 <programlisting>
 598 CREATE FUNCTION dup(in int, out f1 int, out f2 text)
 599     AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
 600     LANGUAGE SQL;
 601
 602 SELECT * FROM dup(42);
 603 </programlisting>
 604    You can do the same thing more verbosely with an explicitly named
 605    composite type:
 606 <programlisting>
 607 CREATE TYPE dup_result AS (f1 int, f2 text);
 608
 609 CREATE FUNCTION dup(int) RETURNS dup_result
 610     AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
 611     LANGUAGE SQL;
 612
 613 SELECT * FROM dup(42);
 614 </programlisting>
 615    Another way to return multiple columns is to use a <literal>TABLE</>
 616    function:
 617 <programlisting>
 618 CREATE FUNCTION dup(int) RETURNS TABLE(f1 int, f2 text)
 619     AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
 620     LANGUAGE SQL;
 621
 622 SELECT * FROM dup(42);
 623 </programlisting>
 624    However, a <literal>TABLE</> function is different from the
 625    preceding examples, because it actually returns a <emphasis>set</>
 626    of records, not just one record.
 627   </para>
 628  </refsect1>
 629
 630  <refsect1 id="sql-createfunction-security">
 631   <title>Writing <literal>SECURITY DEFINER</literal> Functions Safely</title>
 632
 633    <para>
 634     Because a <literal>SECURITY DEFINER</literal> function is executed
 635     with the privileges of the user that created it, care is needed to
 636     ensure that the function cannot be misused.  For security,
 637     <xref linkend="guc-search-path"> should be set to exclude any schemas
 638     writable by untrusted users.  This prevents
 639     malicious users from creating objects that mask objects used by the
 640     function.  Particularly important in this regard is the
 641     temporary-table schema, which is searched first by default, and
 642     is normally writable by anyone.  A secure arrangement can be had
 643     by forcing the temporary schema to be searched last.  To do this,
 644     write <literal>pg_temp</> as the last entry in <varname>search_path</>.
 645     This function illustrates safe usage:
 646    </para>
 647
 648 <programlisting>
 649 CREATE FUNCTION check_password(uname TEXT, pass TEXT)
 650 RETURNS BOOLEAN AS $$
 651 DECLARE passed BOOLEAN;
 652 BEGIN
 653         SELECT  (pwd = $2) INTO passed
 654         FROM    pwds
 655         WHERE   username = $1;
 656
 657         RETURN passed;
 658 END;
 659 $$  LANGUAGE plpgsql
 660     SECURITY DEFINER
 661     -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
 662     SET search_path = admin, pg_temp;
 663 </programlisting>
 664
 665    <para>
 666     Before <productname>PostgreSQL</productname> version 8.3, the
 667     <literal>SET</> option was not available, and so older functions may
 668     contain rather complicated logic to save, set, and restore
 669     <varname>search_path</>.  The <literal>SET</> option is far easier
 670     to use for this purpose.
 671    </para>
 672
 673    <para>
 674     Another point to keep in mind is that by default, execute privilege
 675     is granted to <literal>PUBLIC</> for newly created functions
 676     (see <xref linkend="sql-grant" endterm="sql-grant-title"> for more
 677     information).  Frequently you will wish to restrict use of a security
 678     definer function to only some users.  To do that, you must revoke
 679     the default <literal>PUBLIC</> privileges and then grant execute
 680     privilege selectively.  To avoid having a window where the new function
 681     is accessible to all, create it and set the privileges within a single
 682     transaction.  For example:
 683    </para>
 684
 685 <programlisting>
 686 BEGIN;
 687 CREATE FUNCTION check_password(uname TEXT, pass TEXT) ... SECURITY DEFINER;
 688 REVOKE ALL ON FUNCTION check_password(uname TEXT, pass TEXT) FROM PUBLIC;
 689 GRANT EXECUTE ON FUNCTION check_password(uname TEXT, pass TEXT) TO admins;
 690 COMMIT;
 691 </programlisting>
 692
 693  </refsect1>
 694
 695  <refsect1 id="sql-createfunction-compat">
 696   <title>Compatibility</title>
 697
 698   <para>
 699    A <command>CREATE FUNCTION</command> command is defined in SQL:1999 and later.
 700    The <productname>PostgreSQL</productname> version is similar but
 701    not fully compatible.  The attributes are not portable, neither are the
 702    different available languages.
 703   </para>
 704
 705   <para>
 706    For compatibility with some other database systems,
 707    <replaceable class="parameter">argmode</replaceable> can be written
 708    either before or after <replaceable class="parameter">argname</replaceable>.
 709    But only the first way is standard-compliant.
 710   </para>
 711
 712   <para>
 713    The SQL standard does not specify parameter defaults.  The syntax
 714    with the <literal>DEFAULT</literal> key word is from Oracle, and it
 715    is somewhat in the spirit of the standard: SQL/PSM uses it for
 716    variable default values.  The syntax with <literal>=</literal> is
 717    used in T-SQL and Firebird.
 718   </para>
 719  </refsect1>
 720
 721
 722  <refsect1>
 723   <title>See Also</title>
 724
 725   <simplelist type="inline">
 726    <member><xref linkend="sql-alterfunction" endterm="sql-alterfunction-title"></member>
 727    <member><xref linkend="sql-dropfunction" endterm="sql-dropfunction-title"></member>
 728    <member><xref linkend="sql-grant" endterm="sql-grant-title"></member>
 729    <member><xref linkend="sql-load" endterm="sql-load-title"></member>
 730    <member><xref linkend="sql-revoke" endterm="sql-revoke-title"></member>
 731    <member><xref linkend="app-createlang" endterm="app-createlang-title"></member>
 732   </simplelist>
 733  </refsect1>
 734
 735 </refentry>