<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="create-function-loadable">
      </a>
      15.7.4.1 CREATE FUNCTION Statement for Loadable Functions
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045171897968">
  </a>
  <a class="indexterm" name="idm46045171896928">
  </a>
  <a class="indexterm" name="idm46045171895440">
  </a>
  <a class="indexterm" name="idm46045171893952">
  </a>
  <a class="indexterm" name="idm46045171892464">
  </a>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa57009187"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token punctuation">[</span><span class="token keyword">AGGREGATE</span><span class="token punctuation">]</span> <span class="token keyword">FUNCTION</span> <span class="token punctuation">[</span><span class="token keyword">IF</span> <span class="token operator">NOT</span> <span class="token keyword">EXISTS</span><span class="token punctuation">]</span> <em class="replaceable">function_name</em>
    <span class="token keyword">RETURNS</span> {<span class="token keyword">STRING</span><span class="token operator">|</span><span class="token datatype">INTEGER</span><span class="token operator">|</span><span class="token datatype">REAL</span><span class="token operator">|</span><span class="token datatype">DECIMAL</span>}
    <span class="token keyword">SONAME</span> <em class="replaceable">shared_library_name</em></code></pre>
  </div>
  <p>
   This statement loads the loadable function named
   <em class="replaceable">
    <code>
     function_name
    </code>
   </em>
   . (
   <code class="literal">
    CREATE
        FUNCTION
   </code>
   is also used to created stored functions; see
   <a class="xref" href="create-procedure.html" title="15.1.17 CREATE PROCEDURE and CREATE FUNCTION Statements">
    Section 15.1.17, “CREATE PROCEDURE and CREATE FUNCTION Statements”
   </a>
   .)
  </p>
  <p>
   A loadable function is a way to extend MySQL with a new function
        that works like a native (built-in) MySQL function such as
   <a class="link" href="mathematical-functions.html#function_abs">
    <code class="literal">
     ABS()
    </code>
   </a>
   or
   <a class="link" href="string-functions.html#function_concat">
    <code class="literal">
     CONCAT()
    </code>
   </a>
   . See
   <a class="ulink" href="/doc/extending-mysql/8.4/en/adding-loadable-function.html" target="_top">
    Adding a Loadable Function
   </a>
   .
  </p>
  <p>
   <em class="replaceable">
    <code>
     function_name
    </code>
   </em>
   is the name that should
        be used in SQL statements to invoke the function. The
   <code class="literal">
    RETURNS
   </code>
   clause indicates the type of the
        function's return value.
   <code class="literal">
    DECIMAL
   </code>
   is a legal
        value after
   <code class="literal">
    RETURNS
   </code>
   , but currently
   <code class="literal">
    DECIMAL
   </code>
   functions return string values and
        should be written like
   <code class="literal">
    STRING
   </code>
   functions.
  </p>
  <p>
   <code class="literal">
    IF NOT EXISTS
   </code>
   prevents an error from
        occurring if there already exists a loadable function with the
        same name. It does
   <span class="emphasis">
    <em>
     not
    </em>
   </span>
   prevent an error
        from occurring if there already exists a built-in function
        having the same name.
   <code class="literal">
    IF NOT EXISTS
   </code>
   is also
        supported for
   <code class="literal">
    CREATE FUNCTION
   </code>
   statements. See
   <a class="xref" href="function-resolution.html#function-name-resolution" title="Function Name Resolution">
    Function Name Resolution
   </a>
   .
  </p>
  <p>
   The
   <code class="literal">
    AGGREGATE
   </code>
   keyword, if given, signifies
        that the function is an aggregate (group) function. An aggregate
        function works exactly like a native MySQL aggregate function
        such as
   <a class="link" href="aggregate-functions.html#function_sum">
    <code class="literal">
     SUM()
    </code>
   </a>
   or
   <a class="link" href="aggregate-functions.html#function_count">
    <code class="literal">
     COUNT()
    </code>
   </a>
   .
  </p>
  <p>
   <em class="replaceable">
    <code>
     shared_library_name
    </code>
   </em>
   is the base name
        of the shared library file containing the code that implements
        the function. The file must be located in the plugin directory.
        This directory is given by the value of the
   <a class="link" href="server-system-variables.html#sysvar_plugin_dir">
    <code class="literal">
     plugin_dir
    </code>
   </a>
   system variable. For
        more information, see
   <a class="xref" href="function-loading.html" title="7.7.1 Installing and Uninstalling Loadable Functions">
    Section 7.7.1, “Installing and Uninstalling Loadable Functions”
   </a>
   .
  </p>
  <p>
   <a class="link" href="create-function-loadable.html" title="15.7.4.1 CREATE FUNCTION Statement for Loadable Functions">
    <code class="literal">
     CREATE
        FUNCTION
    </code>
   </a>
   requires the
   <a class="link" href="privileges-provided.html#priv_insert">
    <code class="literal">
     INSERT
    </code>
   </a>
   privilege for the
   <code class="literal">
    mysql
   </code>
   system schema because it adds a row to
        the
   <code class="literal">
    mysql.func
   </code>
   system table to register the
        function.
  </p>
  <p>
   <a class="link" href="create-function-loadable.html" title="15.7.4.1 CREATE FUNCTION Statement for Loadable Functions">
    <code class="literal">
     CREATE
        FUNCTION
    </code>
   </a>
   also adds the function to the Performance
        Schema
   <a class="link" href="performance-schema-user-defined-functions-table.html" title="29.12.22.10 The user_defined_functions Table">
    <code class="literal">
     user_defined_functions
    </code>
   </a>
   table
        that provides runtime information about installed loadable
        functions. See
   <a class="xref" href="performance-schema-user-defined-functions-table.html" title="29.12.22.10 The user_defined_functions Table">
    Section 29.12.22.10, “The user_defined_functions Table”
   </a>
   .
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    Like the
    <code class="literal">
     mysql.func
    </code>
    system table, the
          Performance Schema
    <a class="link" href="performance-schema-user-defined-functions-table.html" title="29.12.22.10 The user_defined_functions Table">
     <code class="literal">
      user_defined_functions
     </code>
    </a>
    table
          lists loadable functions installed using
    <a class="link" href="create-function-loadable.html" title="15.7.4.1 CREATE FUNCTION Statement for Loadable Functions">
     <code class="literal">
      CREATE
          FUNCTION
     </code>
    </a>
    . Unlike the
    <code class="literal">
     mysql.func
    </code>
    table, the
    <a class="link" href="performance-schema-user-defined-functions-table.html" title="29.12.22.10 The user_defined_functions Table">
     <code class="literal">
      user_defined_functions
     </code>
    </a>
    table also lists loadable functions installed automatically by
          server components or plugins. This difference makes
    <a class="link" href="performance-schema-user-defined-functions-table.html" title="29.12.22.10 The user_defined_functions Table">
     <code class="literal">
      user_defined_functions
     </code>
    </a>
    preferable
          to
    <code class="literal">
     mysql.func
    </code>
    for checking which loadable
          functions are installed.
   </p>
  </div>
  <p>
   During the normal startup sequence, the server loads functions
        registered in the
   <code class="literal">
    mysql.func
   </code>
   table. If the
        server is started with the
   <a class="link" href="server-options.html#option_mysqld_skip-grant-tables">
    <code class="option">
     --skip-grant-tables
    </code>
   </a>
   option,
        functions registered in the table are not loaded and are
        unavailable.
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    To upgrade the shared library associated with a loadable
          function, issue a
    <a class="link" href="drop-function-loadable.html" title="15.7.4.2 DROP FUNCTION Statement for Loadable Functions">
     <code class="literal">
      DROP
          FUNCTION
     </code>
    </a>
    statement, upgrade the shared library, and
          then issue a
    <a class="link" href="create-function-loadable.html" title="15.7.4.1 CREATE FUNCTION Statement for Loadable Functions">
     <code class="literal">
      CREATE
          FUNCTION
     </code>
    </a>
    statement. If you upgrade the shared
          library first and then use
    <a class="link" href="drop-function-loadable.html" title="15.7.4.2 DROP FUNCTION Statement for Loadable Functions">
     <code class="literal">
      DROP
          FUNCTION
     </code>
    </a>
    , the server may unexpectedly shut down.
   </p>
  </div>
 </div>
 <br/>
</div>
