Adding upstream version 15.4.upstream/15.4 upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
author: Daniel Baumann <daniel.baumann@progress-linux.org> 2024-04-16 19:46:48 +0000
committer: Daniel Baumann <daniel.baumann@progress-linux.org> 2024-04-16 19:46:48 +0000
commit: 311bcfc6b3acdd6fd152798c7f287ddf74fa2a98 (patch)
tree: 0ec307299b1dada3701e42f4ca6eda57d708261e /doc/src/sgml/html/sql-createprocedure.html
parent: Initial commit. (diff)
download: postgresql-15-311bcfc6b3acdd6fd152798c7f287ddf74fa2a98.tar.xz
postgresql-15-311bcfc6b3acdd6fd152798c7f287ddf74fa2a98.zip
1 files changed, 208 insertions, 0 deletions
diff --git a/doc/src/sgml/html/sql-createprocedure.html b/doc/src/sgml/html/sql-createprocedure.html
new file mode 100644
index 0000000..9247be1
--- /dev/null
+++ b/doc/src/sgml/html/sql-createprocedure.html
@@ -0,0 +1,208 @@
+<?xml version="1.0" encoding="UTF-8" standalone="no"?>
+<!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 PROCEDURE</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-createpolicy.html" title="CREATE POLICY" /><link rel="next" href="sql-createpublication.html" title="CREATE PUBLICATION" /></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 PROCEDURE</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-createpolicy.html" title="CREATE POLICY">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 15.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-createpublication.html" title="CREATE PUBLICATION">Next</a></td></tr></table><hr /></div><div class="refentry" id="SQL-CREATEPROCEDURE"><div class="titlepage"></div><a id="id-1.9.3.76.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">CREATE PROCEDURE</span></h2><p>CREATE PROCEDURE — define a new procedure</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis">
+CREATE [ OR REPLACE ] PROCEDURE
+    <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> ] [, ...] ] )
+  { LANGUAGE <em class="replaceable"><code>lang_name</code></em>
+    | TRANSFORM { FOR TYPE <em class="replaceable"><code>type_name</code></em> } [, ... ]
+    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
+    | 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 }
+    | AS '<em class="replaceable"><code>definition</code></em>'
+    | AS '<em class="replaceable"><code>obj_file</code></em>', '<em class="replaceable"><code>link_symbol</code></em>'
+    | <em class="replaceable"><code>sql_body</code></em>
+  } ...
+</pre></div><div class="refsect1" id="SQL-CREATEPROCEDURE-DESCRIPTION"><h2>Description</h2><p>
+   <code class="command">CREATE PROCEDURE</code> defines a new procedure.
+   <code class="command">CREATE OR REPLACE PROCEDURE</code> will either create a
+   new procedure, or replace an existing definition.
+   To be able to define a procedure, the user must have the
+   <code class="literal">USAGE</code> privilege on the language.
+  </p><p>
+   If a schema name is included, then the procedure is created in the
+   specified schema.  Otherwise it is created in the current schema.
+   The name of the new procedure must not match any existing procedure or function
+   with the same input argument types in the same schema.  However,
+   procedures and functions of different argument types can share a name (this is
+   called <em class="firstterm">overloading</em>).
+  </p><p>
+   To replace the current definition of an existing procedure, use
+   <code class="command">CREATE OR REPLACE PROCEDURE</code>.  It is not possible
+   to change the name or argument types of a procedure this way (if you
+   tried, you would actually be creating a new, distinct procedure).
+  </p><p>
+   When <code class="command">CREATE OR REPLACE PROCEDURE</code> is used to replace an
+   existing procedure, the ownership and permissions of the procedure
+   do not change.  All other procedure properties are assigned the
+   values specified or implied in the command.  You must own the procedure
+   to replace it (this includes being a member of the owning role).
+  </p><p>
+   The user that creates the procedure becomes the owner of the procedure.
+  </p><p>
+   To be able to create a procedure, you must have <code class="literal">USAGE</code>
+   privilege on the argument types.
+  </p><p>
+   Refer to <a class="xref" href="xproc.html" title="38.4. User-Defined Procedures">Section 38.4</a> for further information on writing
+   procedures.
+  </p></div><div class="refsect1" id="id-1.9.3.76.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>
+       The name (optionally schema-qualified) of the procedure to create.
+      </p></dd><dt><span class="term"><em class="replaceable"><code>argmode</code></em></span></dt><dd><p>
+       The mode of an argument: <code class="literal">IN</code>, <code class="literal">OUT</code>,
+       <code class="literal">INOUT</code>, or <code class="literal">VARIADIC</code>.  If omitted,
+       the default is <code class="literal">IN</code>.
+      </p></dd><dt><span class="term"><em class="replaceable"><code>argname</code></em></span></dt><dd><p>
+       The name of an argument.
+      </p></dd><dt><span class="term"><em class="replaceable"><code>argtype</code></em></span></dt><dd><p>
+       The data type(s) of the procedure's arguments (optionally
+       schema-qualified), if any. The argument types can be base, composite,
+       or domain types, or can reference the type of a table column.
+      </p><p>
+       Depending on the implementation language it might also be allowed
+       to specify <span class="quote">“<span class="quote">pseudo-types</span>”</span> such as <code class="type">cstring</code>.
+       Pseudo-types indicate that the actual argument type is either
+       incompletely specified, or outside the set of ordinary SQL data types.
+      </p><p>
+       The type of a column is referenced by writing
+       <code class="literal"><em class="replaceable"><code>table_name</code></em>.<em class="replaceable"><code>column_name</code></em>%TYPE</code>.
+       Using this feature can sometimes help make a procedure independent of
+       changes to the definition of a table.
+      </p></dd><dt><span class="term"><em class="replaceable"><code>default_expr</code></em></span></dt><dd><p>
+       An expression to be used as default value if the parameter is
+       not specified.  The expression has to be coercible to the
+       argument type of the parameter.
+       All input parameters following a
+       parameter with a default value must have default values as well.
+      </p></dd><dt><span class="term"><em class="replaceable"><code>lang_name</code></em></span></dt><dd><p>
+       The name of the language that the procedure is implemented in.
+       It can be <code class="literal">sql</code>, <code class="literal">c</code>,
+       <code class="literal">internal</code>, or the name of a user-defined
+       procedural language, e.g., <code class="literal">plpgsql</code>.  The default is
+       <code class="literal">sql</code> if <em class="replaceable"><code>sql_body</code></em> is specified.  Enclosing the
+       name in single quotes is deprecated and requires matching case.
+      </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>
+       Lists which transforms a call to the procedure should apply.  Transforms
+       convert between SQL types and language-specific data types;
+       see <a class="xref" href="sql-createtransform.html" title="CREATE TRANSFORM"><span class="refentrytitle">CREATE TRANSFORM</span></a>.  Procedural language
+       implementations usually have hardcoded knowledge of the built-in types,
+       so those don't need to be listed here.  If a procedural language
+       implementation does not know how to handle a type and no transform is
+       supplied, it will fall back to a default behavior for converting data
+       types, but this depends on the implementation.
+      </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 procedure
+      is to be executed with the privileges of the user that calls it.
+      That is the default.  <code class="literal">SECURITY DEFINER</code>
+      specifies that the procedure is to be executed with the
+      privileges of the user that owns it.
+     </p><p>
+      The key word <code class="literal">EXTERNAL</code> is allowed for SQL
+      conformance, but it is optional since, unlike in SQL, this feature
+      applies to all procedures not only external ones.
+     </p><p>
+      A <code class="literal">SECURITY DEFINER</code> procedure cannot execute
+      transaction control statements (for example, <code class="command">COMMIT</code>
+      and <code class="command">ROLLBACK</code>, depending on the language).
+     </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>
+       The <code class="literal">SET</code> clause causes the specified configuration
+       parameter to be set to the specified value when the procedure is
+       entered, and then restored to its prior value when the procedure exits.
+       <code class="literal">SET FROM CURRENT</code> saves the value of the parameter that
+       is current when <code class="command">CREATE PROCEDURE</code> is executed as the value
+       to be applied when the procedure is entered.
+      </p><p>
+       If a <code class="literal">SET</code> clause is attached to a procedure, then
+       the effects of a <code class="command">SET LOCAL</code> command executed inside the
+       procedure for the same variable are restricted to the procedure: the
+       configuration parameter's prior value is still restored at procedure exit.
+       However, an ordinary
+       <code class="command">SET</code> command (without <code class="literal">LOCAL</code>) overrides the
+       <code class="literal">SET</code> clause, much as it would do for a previous <code class="command">SET
+       LOCAL</code> command: the effects of such a command will persist after
+       procedure exit, unless the current transaction is rolled back.
+      </p><p>
+       If a <code class="literal">SET</code> clause is attached to a procedure, then
+       that procedure cannot execute transaction control statements (for
+       example, <code class="command">COMMIT</code> and <code class="command">ROLLBACK</code>,
+       depending on the language).
+      </p><p>
+       See <a class="xref" href="sql-set.html" title="SET"><span class="refentrytitle">SET</span></a> and
+       <a class="xref" href="runtime-config.html" title="Chapter 20. Server Configuration">Chapter 20</a>
+       for more information about allowed parameter names and values.
+      </p></dd><dt><span class="term"><em class="replaceable"><code>definition</code></em></span></dt><dd><p>
+       A string constant defining the procedure; the meaning depends on the
+       language.  It can be an internal procedure name, the path to an
+       object file, an SQL command, or text in a procedural language.
+      </p><p>
+       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 procedure definition
+       string, rather than the normal single quote syntax.  Without dollar
+       quoting, any single quotes or backslashes in the procedure definition must
+       be escaped by doubling them.
+      </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>
+       This form of the <code class="literal">AS</code> clause is used for
+       dynamically loadable C language procedures when the procedure name
+       in the C language source code is not the same as the name of
+       the SQL procedure. The string <em class="replaceable"><code>obj_file</code></em> is the name of the shared
+       library file containing the compiled C procedure, and is interpreted
+       as for the <a class="link" href="sql-load.html" title="LOAD"><code class="command">LOAD</code></a> command.  The string
+       <em class="replaceable"><code>link_symbol</code></em> is the
+       procedure's link symbol, that is, the name of the procedure in the C
+       language source code.  If the link symbol is omitted, it is assumed
+       to be the same as the name of the SQL procedure being defined.
+      </p><p>
+       When repeated <code class="command">CREATE PROCEDURE</code> calls refer to
+       the same object file, the file is only loaded once per session.
+       To unload and
+       reload the file (perhaps during development), start a new session.
+      </p></dd><dt><span class="term"><em class="replaceable"><code>sql_body</code></em></span></dt><dd><p>
+       The body of a <code class="literal">LANGUAGE SQL</code> procedure.  This should
+       be a block
+</p><pre class="programlisting">
+BEGIN ATOMIC
+  <em class="replaceable"><code>statement</code></em>;
+  <em class="replaceable"><code>statement</code></em>;
+  ...
+  <em class="replaceable"><code>statement</code></em>;
+END
+</pre><p>
+      </p><p>
+       This is similar to writing the text of the procedure body as a string
+       constant (see <em class="replaceable"><code>definition</code></em> above), but there
+       are some differences: This form only works for <code class="literal">LANGUAGE
+       SQL</code>, the string constant form works for all languages.  This
+       form is parsed at procedure definition time, the string constant form is
+       parsed at execution time; therefore this form cannot support
+       polymorphic argument types and other constructs that are not resolvable
+       at procedure definition time.  This form tracks dependencies between the
+       procedure and objects used in the procedure body, so <code class="literal">DROP
+       ... CASCADE</code> will work correctly, whereas the form using
+       string literals may leave dangling procedures.  Finally, this form is
+       more compatible with the SQL standard and other SQL implementations.
+      </p></dd></dl></div></div><div class="refsect1" id="SQL-CREATEPROCEDURE-NOTES"><h2>Notes</h2><p>
+   See <a class="xref" href="sql-createfunction.html" title="CREATE FUNCTION"><span class="refentrytitle">CREATE FUNCTION</span></a> for more details on function
+   creation that also apply to procedures.
+  </p><p>
+   Use <a class="xref" href="sql-call.html" title="CALL"><span class="refentrytitle">CALL</span></a> to execute a procedure.
+  </p></div><div class="refsect1" id="SQL-CREATEPROCEDURE-EXAMPLES"><h2>Examples</h2><p>
+</p><pre class="programlisting">
+CREATE PROCEDURE insert_data(a integer, b integer)
+LANGUAGE SQL
+AS $$
+INSERT INTO tbl VALUES (a);
+INSERT INTO tbl VALUES (b);
+$$;
+</pre><p>
+   or
+</p><pre class="programlisting">
+CREATE PROCEDURE insert_data(a integer, b integer)
+LANGUAGE SQL
+BEGIN ATOMIC
+  INSERT INTO tbl VALUES (a);
+  INSERT INTO tbl VALUES (b);
+END;
+</pre><p>
+   and call like this:
+</p><pre class="programlisting">
+CALL insert_data(1, 2);
+</pre></div><div class="refsect1" id="SQL-CREATEPROCEDURE-COMPAT"><h2>Compatibility</h2><p>
+   A <code class="command">CREATE PROCEDURE</code> command is defined in the SQL
+   standard.  The <span class="productname">PostgreSQL</span> implementation can be
+   used in a compatible way but has many extensions.  For details see also
+   <a class="xref" href="sql-createfunction.html" title="CREATE FUNCTION"><span class="refentrytitle">CREATE FUNCTION</span></a>.
+  </p></div><div class="refsect1" id="id-1.9.3.76.10"><h2>See Also</h2><span class="simplelist"><a class="xref" href="sql-alterprocedure.html" title="ALTER PROCEDURE"><span class="refentrytitle">ALTER PROCEDURE</span></a>, <a class="xref" href="sql-dropprocedure.html" title="DROP PROCEDURE"><span class="refentrytitle">DROP PROCEDURE</span></a>, <a class="xref" href="sql-call.html" title="CALL"><span class="refentrytitle">CALL</span></a>, <a class="xref" href="sql-createfunction.html" title="CREATE FUNCTION"><span class="refentrytitle">CREATE FUNCTION</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-createpolicy.html" title="CREATE POLICY">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-createpublication.html" title="CREATE PUBLICATION">Next</a></td></tr><tr><td width="40%" align="left" valign="top">CREATE POLICY </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.4 Documentation">Home</a></td><td width="40%" align="right" valign="top"> CREATE PUBLICATION</td></tr></table></div></body></html>
+\ No newline at end of file
author	Daniel Baumann <daniel.baumann@progress-linux.org>	2024-04-16 19:46:48 +0000
committer	Daniel Baumann <daniel.baumann@progress-linux.org>	2024-04-16 19:46:48 +0000
commit	311bcfc6b3acdd6fd152798c7f287ddf74fa2a98 (patch)
tree	0ec307299b1dada3701e42f4ca6eda57d708261e /doc/src/sgml/html/sql-createprocedure.html
parent	Initial commit. (diff)
download	postgresql-15-311bcfc6b3acdd6fd152798c7f287ddf74fa2a98.tar.xz postgresql-15-311bcfc6b3acdd6fd152798c7f287ddf74fa2a98.zip