diff options
Diffstat (limited to 'doc/src/sgml/html/sql-createprocedure.html')
| -rw-r--r-- | doc/src/sgml/html/sql-createprocedure.html | 208 |
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..8767b4b --- /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 xmlns="http://www.w3.org/TR/xhtml1/transitional" 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 14.5 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></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 xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navfooter"><hr></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 14.5 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 |