codekingpro/portable-devtools
115k
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 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 16.3 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">3CREATE [ OR REPLACE ] PROCEDURE4 <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 { LANGUAGE <em class="replaceable"><code>lang_name</code></em>6 | TRANSFORM { FOR TYPE <em class="replaceable"><code>type_name</code></em> } [, ... ]7 | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER8 | 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 }9 | AS '<em class="replaceable"><code>definition</code></em>'10 | AS '<em class="replaceable"><code>obj_file</code></em>', '<em class="replaceable"><code>link_symbol</code></em>'11 | <em class="replaceable"><code>sql_body</code></em>12 } ...13</pre></div><div class="refsect1" id="SQL-CREATEPROCEDURE-DESCRIPTION"><h2>Description</h2><p>14 <code class="command">CREATE PROCEDURE</code> defines a new procedure.15 <code class="command">CREATE OR REPLACE PROCEDURE</code> will either create a16 new procedure, or replace an existing definition.17 To be able to define a procedure, the user must have the18 <code class="literal">USAGE</code> privilege on the language.19 </p><p>20 If a schema name is included, then the procedure is created in the21 specified schema. Otherwise it is created in the current schema.22 The name of the new procedure must not match any existing procedure or function23 with the same input argument types in the same schema. However,24 procedures and functions of different argument types can share a name (this is25 called <em class="firstterm">overloading</em>).26 </p><p>27 To replace the current definition of an existing procedure, use28 <code class="command">CREATE OR REPLACE PROCEDURE</code>. It is not possible29 to change the name or argument types of a procedure this way (if you30 tried, you would actually be creating a new, distinct procedure).31 </p><p>32 When <code class="command">CREATE OR REPLACE PROCEDURE</code> is used to replace an33 existing procedure, the ownership and permissions of the procedure34 do not change. All other procedure properties are assigned the35 values specified or implied in the command. You must own the procedure36 to replace it (this includes being a member of the owning role).37 </p><p>38 The user that creates the procedure becomes the owner of the procedure.39 </p><p>40 To be able to create a procedure, you must have <code class="literal">USAGE</code>41 privilege on the argument types.42 </p><p>43 Refer to <a class="xref" href="xproc.html" title="38.4. User-Defined Procedures">Section 38.4</a> for further information on writing44 procedures.45 </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>46 The name (optionally schema-qualified) of the procedure to create.47 </p></dd><dt><span class="term"><em class="replaceable"><code>argmode</code></em></span></dt><dd><p>48 The mode of an argument: <code class="literal">IN</code>, <code class="literal">OUT</code>,49 <code class="literal">INOUT</code>, or <code class="literal">VARIADIC</code>. If omitted,50 the default is <code class="literal">IN</code>.51 </p></dd><dt><span class="term"><em class="replaceable"><code>argname</code></em></span></dt><dd><p>52 The name of an argument.53 </p></dd><dt><span class="term"><em class="replaceable"><code>argtype</code></em></span></dt><dd><p>54 The data type(s) of the procedure's arguments (optionally55 schema-qualified), if any. The argument types can be base, composite,56 or domain types, or can reference the type of a table column.57 </p><p>58 Depending on the implementation language it might also be allowed59 to specify <span class="quote">“<span class="quote">pseudo-types</span>”</span> such as <code class="type">cstring</code>.60 Pseudo-types indicate that the actual argument type is either61 incompletely specified, or outside the set of ordinary SQL data types.62 </p><p>63 The type of a column is referenced by writing64 <code class="literal"><em class="replaceable"><code>table_name</code></em>.<em class="replaceable"><code>column_name</code></em>%TYPE</code>.65 Using this feature can sometimes help make a procedure independent of66 changes to the definition of a table.67 </p></dd><dt><span class="term"><em class="replaceable"><code>default_expr</code></em></span></dt><dd><p>68 An expression to be used as default value if the parameter is69 not specified. The expression has to be coercible to the70 argument type of the parameter.71 All input parameters following a72 parameter with a default value must have default values as well.73 </p></dd><dt><span class="term"><em class="replaceable"><code>lang_name</code></em></span></dt><dd><p>74 The name of the language that the procedure is implemented in.75 It can be <code class="literal">sql</code>, <code class="literal">c</code>,76 <code class="literal">internal</code>, or the name of a user-defined77 procedural language, e.g., <code class="literal">plpgsql</code>. The default is78 <code class="literal">sql</code> if <em class="replaceable"><code>sql_body</code></em> is specified. Enclosing the79 name in single quotes is deprecated and requires matching case.80 </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>81 Lists which transforms a call to the procedure should apply. Transforms82 convert between SQL types and language-specific data types;83 see <a class="xref" href="sql-createtransform.html" title="CREATE TRANSFORM"><span class="refentrytitle">CREATE TRANSFORM</span></a>. Procedural language84 implementations usually have hardcoded knowledge of the built-in types,85 so those don't need to be listed here. If a procedural language86 implementation does not know how to handle a type and no transform is87 supplied, it will fall back to a default behavior for converting data88 types, but this depends on the implementation.89 </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 procedure90 is to be executed with the privileges of the user that calls it.91 That is the default. <code class="literal">SECURITY DEFINER</code>92 specifies that the procedure is to be executed with the93 privileges of the user that owns it.94 </p><p>95 The key word <code class="literal">EXTERNAL</code> is allowed for SQL96 conformance, but it is optional since, unlike in SQL, this feature97 applies to all procedures not only external ones.98 </p><p>99 A <code class="literal">SECURITY DEFINER</code> procedure cannot execute100 transaction control statements (for example, <code class="command">COMMIT</code>101 and <code class="command">ROLLBACK</code>, depending on the language).102 </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>103 The <code class="literal">SET</code> clause causes the specified configuration104 parameter to be set to the specified value when the procedure is105 entered, and then restored to its prior value when the procedure exits.106 <code class="literal">SET FROM CURRENT</code> saves the value of the parameter that107 is current when <code class="command">CREATE PROCEDURE</code> is executed as the value108 to be applied when the procedure is entered.109 </p><p>110 If a <code class="literal">SET</code> clause is attached to a procedure, then111 the effects of a <code class="command">SET LOCAL</code> command executed inside the112 procedure for the same variable are restricted to the procedure: the113 configuration parameter's prior value is still restored at procedure exit.114 However, an ordinary115 <code class="command">SET</code> command (without <code class="literal">LOCAL</code>) overrides the116 <code class="literal">SET</code> clause, much as it would do for a previous <code class="command">SET117 LOCAL</code> command: the effects of such a command will persist after118 procedure exit, unless the current transaction is rolled back.119 </p><p>120 If a <code class="literal">SET</code> clause is attached to a procedure, then121 that procedure cannot execute transaction control statements (for122 example, <code class="command">COMMIT</code> and <code class="command">ROLLBACK</code>,123 depending on the language).124 </p><p>125 See <a class="xref" href="sql-set.html" title="SET"><span class="refentrytitle">SET</span></a> and126 <a class="xref" href="runtime-config.html" title="Chapter 20. Server Configuration">Chapter 20</a>127 for more information about allowed parameter names and values.128 </p></dd><dt><span class="term"><em class="replaceable"><code>definition</code></em></span></dt><dd><p>129 A string constant defining the procedure; the meaning depends on the130 language. It can be an internal procedure name, the path to an131 object file, an SQL command, or text in a procedural language.132 </p><p>133 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 definition134 string, rather than the normal single quote syntax. Without dollar135 quoting, any single quotes or backslashes in the procedure definition must136 be escaped by doubling them.137 </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>138 This form of the <code class="literal">AS</code> clause is used for139 dynamically loadable C language procedures when the procedure name140 in the C language source code is not the same as the name of141 the SQL procedure. The string <em class="replaceable"><code>obj_file</code></em> is the name of the shared142 library file containing the compiled C procedure, and is interpreted143 as for the <a class="link" href="sql-load.html" title="LOAD"><code class="command">LOAD</code></a> command. The string144 <em class="replaceable"><code>link_symbol</code></em> is the145 procedure's link symbol, that is, the name of the procedure in the C146 language source code. If the link symbol is omitted, it is assumed147 to be the same as the name of the SQL procedure being defined.148 </p><p>149 When repeated <code class="command">CREATE PROCEDURE</code> calls refer to150 the same object file, the file is only loaded once per session.151 To unload and152 reload the file (perhaps during development), start a new session.153 </p></dd><dt><span class="term"><em class="replaceable"><code>sql_body</code></em></span></dt><dd><p>154 The body of a <code class="literal">LANGUAGE SQL</code> procedure. This should155 be a block156</p><pre class="programlisting">157BEGIN ATOMIC158 <em class="replaceable"><code>statement</code></em>;159 <em class="replaceable"><code>statement</code></em>;160 ...161 <em class="replaceable"><code>statement</code></em>;162END163</pre><p>164 </p><p>165 This is similar to writing the text of the procedure body as a string166 constant (see <em class="replaceable"><code>definition</code></em> above), but there167 are some differences: This form only works for <code class="literal">LANGUAGE168 SQL</code>, the string constant form works for all languages. This169 form is parsed at procedure definition time, the string constant form is170 parsed at execution time; therefore this form cannot support171 polymorphic argument types and other constructs that are not resolvable172 at procedure definition time. This form tracks dependencies between the173 procedure and objects used in the procedure body, so <code class="literal">DROP174 ... CASCADE</code> will work correctly, whereas the form using175 string literals may leave dangling procedures. Finally, this form is176 more compatible with the SQL standard and other SQL implementations.177 </p></dd></dl></div></div><div class="refsect1" id="SQL-CREATEPROCEDURE-NOTES"><h2>Notes</h2><p>178 See <a class="xref" href="sql-createfunction.html" title="CREATE FUNCTION"><span class="refentrytitle">CREATE FUNCTION</span></a> for more details on function179 creation that also apply to procedures.180 </p><p>181 Use <a class="xref" href="sql-call.html" title="CALL"><span class="refentrytitle">CALL</span></a> to execute a procedure.182 </p></div><div class="refsect1" id="SQL-CREATEPROCEDURE-EXAMPLES"><h2>Examples</h2><p>183</p><pre class="programlisting">184CREATE PROCEDURE insert_data(a integer, b integer)185LANGUAGE SQL186AS $$187INSERT INTO tbl VALUES (a);188INSERT INTO tbl VALUES (b);189$$;190</pre><p>191 or192</p><pre class="programlisting">193CREATE PROCEDURE insert_data(a integer, b integer)194LANGUAGE SQL195BEGIN ATOMIC196 INSERT INTO tbl VALUES (a);197 INSERT INTO tbl VALUES (b);198END;199</pre><p>200 and call like this:201</p><pre class="programlisting">202CALL insert_data(1, 2);203</pre></div><div class="refsect1" id="SQL-CREATEPROCEDURE-COMPAT"><h2>Compatibility</h2><p>204 A <code class="command">CREATE PROCEDURE</code> command is defined in the SQL205 standard. The <span class="productname">PostgreSQL</span> implementation can be206 used in a compatible way but has many extensions. For details see also207 <a class="xref" href="sql-createfunction.html" title="CREATE FUNCTION"><span class="refentrytitle">CREATE FUNCTION</span></a>.208 </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 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> CREATE PUBLICATION</td></tr></table></div></body></html>