{"Entry":{"collection":"sql","key":"create-procedure","name":"CREATE PROCEDURE","aliases":["createprocedure"],"metadata":{"aliases":["createprocedure"],"changed_in":["14"],"changes":[{"from":"10","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":[],"removed":[]},"status":"added","synopsis":null,"to":"11"},{"from":"11","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description"],"removed":[]},"status":"changed","synopsis":null,"to":"12"},{"from":"13","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters","examples","compatibility"],"removed":[]},"status":"changed","synopsis":{"added":["| sql_body"],"removed":[]},"to":"14"},{"from":"16","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"17"},{"from":"18","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters","notes","examples"],"removed":[]},"status":"changed","synopsis":null,"to":"19"}],"content_hash":"4884e13a97922bb9d1159fe380186a47b9b2b4d1c94fbe1ccac0c1d1149f4e89","derived_content_hash":"95b776a34e6a8eefe4dccd0c6675c6467339918fb6ec48fa7d08916e3e404b71","documentation_refresh":{"batch":"20261113","original_source_rev":"wiki-repair-20261023:51eeabd094042d0d2e555f48d5ba6d5adb9cd4b28097f1c1d92e462ce64d9db9","versions":{"14":"14.25","15":"15.20","16":"16.16","17":"17.12","18":"18.7","19":"19rc2"}},"editorial":{},"first_version":"11","group":"routine","imported_at":"2026-09-30T17:43:37.2724+08:00","last_version":"20","name":"CREATE PROCEDURE","object":"PROCEDURE","position":2011,"present_in":["11","12","13","14","15","16","17","18","19","20"],"purpose":"define a new procedure","purpose_zh":"","related":["alter-procedure","drop-procedure","call","create-function"],"repair_provenance":{"original_source_revision":"a709ab85","receipt_sha256":"11798dff2bf365cfb05e77f99fe6bba9f73536be7c5789779ba3918e7f4709ed","repair":"wiki-repair-20261023"},"slug":"create-procedure","source_rev":"wiki-repair-20261023:51eeabd094042d0d2e555f48d5ba6d5adb9cd4b28097f1c1d92e462ce64d9db9","synopsis":"CREATE [ OR REPLACE ] PROCEDURE\nname ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )\n{ LANGUAGE lang_name\n| TRANSFORM { FOR TYPE type_name } [, ... ]\n| [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER\n| SET configuration_parameter { TO value | = value | FROM CURRENT }\n| AS 'definition'\n| AS 'obj_file', 'link_symbol'\n| sql_body\n} ...","verb":"CREATE"}},"Definition":{"Collection":"sql","Key":"create-procedure","SourceDatabase":"center","Version":"18","SourceTable":"sqlcmd","SourceKey":"create-procedure","SourceRevision":"wiki-repair-20261023:51eeabd094042d0d2e555f48d5ba6d5adb9cd4b28097f1c1d92e462ce64d9db9","Facts":{"anchor":"SQL-CREATEPROCEDURE","description_zh":"定义一个新过程","description_zh_html":"\u003cpre\u003eCREATE [ OR REPLACE ] PROCEDURE\nname ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )\n{ LANGUAGE lang_name\n| TRANSFORM { FOR TYPE type_name } [, ... ]\n| [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER\n| SET configuration_parameter { TO value | = value | FROM CURRENT }\n| AS \u0026#x27;definition\u0026#x27;\n| AS \u0026#x27;obj_file\u0026#x27;, \u0026#x27;link_symbol\u0026#x27;\n| sql_body\n} ...\u003c/pre\u003e\u003csection\u003e\u003ch2\u003e描述\u003c/h2\u003e\u003cp\u003e\n\u003ccode class=\"command\"\u003eCREATE PROCEDURE\u003c/code\u003e 定义一个新过程。\u003ccode class=\"command\"\u003eCREATE OR REPLACE PROCEDURE\u003c/code\u003e 要么创建一个新过程，要么替换现有定义。要能够定义过程，用户必须对该语言拥有 \u003ccode class=\"literal\"\u003eUSAGE\u003c/code\u003e 权限。\n  \u003c/p\u003e\u003cp\u003e\n   如果包含模式名，则该过程会创建在指定模式中；否则会创建在当前模式中。新过程的名称不能与同一模式中任何输入参数类型相同的现有过程或函数重名。不过，不同参数类型的过程和函数可以共享同一个名称（这称为\u003cem class=\"firstterm\"\u003e重载\u003c/em\u003e）。\n  \u003c/p\u003e\u003cp\u003e\n   要替换现有过程的当前定义，请使用 \u003ccode class=\"command\"\u003eCREATE OR REPLACE PROCEDURE\u003c/code\u003e。不能用这种方式更改过程名称或参数类型（如果这样做，实际上是在创建一个新的不同过程）。\n  \u003c/p\u003e\u003cp\u003e\n   当使用 \u003ccode class=\"command\"\u003eCREATE OR REPLACE PROCEDURE\u003c/code\u003e 替换现有过程时，该过程的所有权和权限不会改变。其他所有过程属性都会被设置为命令中指定或隐含的值。只有拥有该过程才能替换它（这也包括属于拥有者角色的成员）。\n  \u003c/p\u003e\u003cp\u003e\n   创建该过程的用户将成为该过程的拥有者。\n  \u003c/p\u003e\u003cp\u003e\n   要能够创建过程，你必须对参数类型拥有 \u003ccode class=\"literal\"\u003eUSAGE\u003c/code\u003e 权限。\n  \u003c/p\u003e\u003cp\u003e\n   有关编写过程的详细信息，请参阅\u003ca class=\"xref\" href=\"/zh/docs/18/xproc.html\" title=\"36.4. 用户定义的过程\"\u003e第 36.4 节\u003c/a\u003e。\n  \u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e参数\u003c/h2\u003e\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt id=\"ZH18-AUTO-CREATE_PROCEDURE-VARLISTENTRY-1\"\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e \u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n       要创建的过程名称（可选模式限定）。\n      \u003c/p\u003e\u003c/dd\u003e\u003cdt id=\"ZH18-AUTO-CREATE_PROCEDURE-VARLISTENTRY-2\"\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eargmode\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e \u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n      参数的模式：\u003ccode class=\"literal\"\u003eIN\u003c/code\u003e、\u003ccode class=\"literal\"\u003eOUT\u003c/code\u003e、\u003ccode class=\"literal\"\u003eINOUT\u003c/code\u003e 或 \u003ccode class=\"literal\"\u003eVARIADIC\u003c/code\u003e。如果省略，默认为 \u003ccode class=\"literal\"\u003eIN\u003c/code\u003e。\n     \u003c/p\u003e\u003c/dd\u003e\u003cdt id=\"ZH18-AUTO-CREATE_PROCEDURE-VARLISTENTRY-3\"\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eargname\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e \u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n       参数的名称。\n      \u003c/p\u003e\u003c/dd\u003e\u003cdt id=\"ZH18-AUTO-CREATE_PROCEDURE-VARLISTENTRY-4\"\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eargtype\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e \u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n       过程参数的数据类型（如果有，可选模式限定）。参数类型可以是基础类型、复合类型或域类型，也可以引用表列的类型。\n      \u003c/p\u003e\u003cp\u003e\n       根据实现语言，也可能允许指定诸如 \u003ccode class=\"type\"\u003ecstring\u003c/code\u003e 之类的\u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003e伪类型\u003c/span\u003e”\u003c/span\u003e。伪类型表示实际参数类型要么没有被完整指定，要么位于普通 SQL 数据类型集合之外。\n      \u003c/p\u003e\u003cp\u003e\n       可以通过写成 \u003ccode class=\"literal\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e.\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e%TYPE\u003c/code\u003e 来引用列的类型。使用这一特性有时可以帮助让过程不受表定义变更的影响。\n      \u003c/p\u003e\u003c/dd\u003e\u003cdt id=\"ZH18-AUTO-CREATE_PROCEDURE-VARLISTENTRY-5\"\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003edefault_expr\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e \u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n       在未指定该参数时用作默认值的表达式。该表达式必须能够强制转换为该参数的类型。任何位于带默认值参数之后的输入参数也都必须具有默认值。\n      \u003c/p\u003e\u003c/dd\u003e\u003cdt id=\"ZH18-AUTO-CREATE_PROCEDURE-VARLISTENTRY-6\"\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003elang_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e \u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n       实现该过程所用语言的名称。可以是 \u003ccode class=\"literal\"\u003esql\u003c/code\u003e、\u003ccode class=\"literal\"\u003ec\u003c/code\u003e、\u003ccode class=\"literal\"\u003einternal\u003c/code\u003e，或者用户自定义过程语言的名称，例如 \u003ccode class=\"literal\"\u003eplpgsql\u003c/code\u003e。如果指定了 \u003cem class=\"replaceable\"\u003e\u003ccode\u003esql_body\u003c/code\u003e\u003c/em\u003e，默认值是 \u003ccode class=\"literal\"\u003esql\u003c/code\u003e。不推荐将该名称放在单引号中；如果这样写，大小写必须完全匹配。\n      \u003c/p\u003e\u003c/dd\u003e\u003cdt id=\"ZH18-AUTO-CREATE_PROCEDURE-VARLISTENTRY-7\"\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eTRANSFORM { FOR TYPE \u003cem class=\"replaceable\"\u003e\u003ccode\u003etype_name\u003c/code\u003e\u003c/em\u003e } [, ... ]\u003c/code\u003e\u003c/span\u003e \u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n       列出调用该过程时应应用哪些转换。转换负责在 SQL 类型与语言特定数据类型之间进行转换；详见 \u003ca class=\"xref\" href=\"/zh/docs/18/sql-createtransform.html\" title=\"CREATE TRANSFORM\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE TRANSFORM\u003c/span\u003e\u003c/a\u003e。过程语言实现通常对内置类型有硬编码知识，因此无须在此列出。如果某种过程语言实现不知道如何处理某个类型，且又没有提供转换，则会退回到默认的数据类型转换行为，但具体行为取决于该实现。\n      \u003c/p\u003e\u003c/dd\u003e\u003cdt id=\"ZH18-AUTO-CREATE_PROCEDURE-VARLISTENTRY-8\"\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003e[\u003cspan class=\"optional\"\u003eEXTERNAL\u003c/span\u003e] SECURITY INVOKER\u003c/code\u003e\u003cbr/\u003e\u003c/span\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003e[\u003cspan class=\"optional\"\u003eEXTERNAL\u003c/span\u003e] SECURITY DEFINER\u003c/code\u003e\u003c/span\u003e \u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\u003ccode class=\"literal\"\u003eSECURITY INVOKER\u003c/code\u003e 表示该过程将以调用它的用户的权限执行。这是默认值。\u003ccode class=\"literal\"\u003eSECURITY DEFINER\u003c/code\u003e 指定该过程将以拥有它的用户的权限执行。\n     \u003c/p\u003e\u003cp\u003e\n      关键字 \u003ccode class=\"literal\"\u003eEXTERNAL\u003c/code\u003e 是为了符合 SQL 而允许使用的，但它是可选的，因为与 SQL 不同，此特性适用于所有过程，而不只是外部过程。\n     \u003c/p\u003e\u003cp\u003e\n\u003ccode class=\"literal\"\u003eSECURITY DEFINER\u003c/code\u003e 过程不能执行事务控制语句（例如 \u003ccode class=\"command\"\u003eCOMMIT\u003c/code\u003e 和 \u003ccode class=\"command\"\u003eROLLBACK\u003c/code\u003e，具体取决于语言）。\n     \u003c/p\u003e\u003c/dd\u003e\u003cdt id=\"ZH18-AUTO-CREATE_PROCEDURE-VARLISTENTRY-9\"\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003econfiguration_parameter\u003c/code\u003e\u003c/em\u003e\u003cbr/\u003e\u003c/span\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003evalue\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e \u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n\u003ccode class=\"literal\"\u003eSET\u003c/code\u003e 子句会在进入该过程时把指定的配置参数设置为指定值，并在过程退出时恢复为之前的值。\u003ccode class=\"literal\"\u003eSET FROM CURRENT\u003c/code\u003e 会把执行 \u003ccode class=\"command\"\u003eCREATE PROCEDURE\u003c/code\u003e 时该参数的当前值保存为进入过程时要应用的值。\n      \u003c/p\u003e\u003cp\u003e\n       如果为过程附加了 \u003ccode class=\"literal\"\u003eSET\u003c/code\u003e 子句，那么在该过程中针对同一变量执行的 \u003ccode class=\"command\"\u003eSET LOCAL\u003c/code\u003e 命令，其效果会被限制在该过程内：过程退出时，该配置参数的先前值仍会被恢复。不过，普通的 \u003ccode class=\"command\"\u003eSET\u003c/code\u003e 命令（不带 \u003ccode class=\"literal\"\u003eLOCAL\u003c/code\u003e）会覆盖这个 \u003ccode class=\"literal\"\u003eSET\u003c/code\u003e 子句，就像它会覆盖先前的 \u003ccode class=\"command\"\u003eSET LOCAL\u003c/code\u003e 命令一样：这种命令的效果会在过程退出后持续存在，除非当前事务被回滚。\n      \u003c/p\u003e\u003cp\u003e\n       如果为过程附加了 \u003ccode class=\"literal\"\u003eSET\u003c/code\u003e 子句，则该过程不能执行事务控制语句（例如 \u003ccode class=\"command\"\u003eCOMMIT\u003c/code\u003e 和 \u003ccode class=\"command\"\u003eROLLBACK\u003c/code\u003e，具体取决于语言）。\n      \u003c/p\u003e\u003cp\u003e\n       关于允许的参数名和值的更多信息，见 \u003ca class=\"xref\" href=\"/zh/docs/18/sql-set.html\" title=\"SET\"\u003e\u003cspan class=\"refentrytitle\"\u003eSET\u003c/span\u003e\u003c/a\u003e 和\u003ca class=\"xref\" href=\"/zh/docs/18/runtime-config.html\" title=\"第 19 章 服务器配置\"\u003e第 19 章\u003c/a\u003e。\n      \u003c/p\u003e\u003c/dd\u003e\u003cdt id=\"ZH18-AUTO-CREATE_PROCEDURE-VARLISTENTRY-10\"\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003edefinition\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e \u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n       定义该过程的字符串常量，其含义取决于所用语言。它可以是内部过程名称、对象文件路径、SQL 命令，或过程语言中的文本。\n      \u003c/p\u003e\u003cp\u003e\n       通常，使用美元引用（见\u003ca class=\"xref\" href=\"/zh/docs/18/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING\" title=\"4.1.2.4. 美元引用的字符串常量\"\u003e第 4.1.2.4 节\u003c/a\u003e）来书写过程定义字符串，会比普通单引号语法更方便。不使用美元引用时，过程定义中的任何单引号或反斜线都必须通过双写进行转义。\n      \u003c/p\u003e\u003c/dd\u003e\u003cdt id=\"ZH18-AUTO-CREATE_PROCEDURE-VARLISTENTRY-11\"\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eobj_file\u003c/code\u003e\u003c/em\u003e, \u003cem class=\"replaceable\"\u003e\u003ccode\u003elink_symbol\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e\u003c/span\u003e \u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n       当 C 语言源代码中的过程名与 SQL 过程名不同时，会为可动态加载的 C 语言过程使用 \u003ccode class=\"literal\"\u003eAS\u003c/code\u003e 子句的这种形式。字符串 \u003cem class=\"replaceable\"\u003e\u003ccode\u003eobj_file\u003c/code\u003e\u003c/em\u003e 是包含已编译 C 过程的共享库文件名，其解释方式与 \u003ca class=\"link\" href=\"/zh/docs/18/sql-load.html\" title=\"LOAD\"\u003e\u003ccode class=\"command\"\u003eLOAD\u003c/code\u003e\u003c/a\u003e 命令相同。字符串 \u003cem class=\"replaceable\"\u003e\u003ccode\u003elink_symbol\u003c/code\u003e\u003c/em\u003e 是该过程的链接符号，也就是它在 C 语言源代码中的过程名。如果省略链接符号，则假定它与所定义的 SQL 过程名相同。\n      \u003c/p\u003e\u003cp\u003e\n       当重复调用的 \u003ccode class=\"command\"\u003eCREATE PROCEDURE\u003c/code\u003e 引用同一个对象文件时，该文件在每个会话中只会被加载一次。要卸载并重新加载该文件（例如在开发期间），需要启动一个新会话。\n      \u003c/p\u003e\u003c/dd\u003e\u003cdt id=\"ZH18-AUTO-CREATE_PROCEDURE-VARLISTENTRY-12\"\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003esql_body\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e \u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n\u003ccode class=\"literal\"\u003eLANGUAGE SQL\u003c/code\u003e 过程的主体。这应当是一个语句块：\n\u003c/p\u003e\u003cpre class=\"programlisting\"\u003e\nBEGIN ATOMIC\n  \u003cem class=\"replaceable\"\u003e\u003ccode\u003estatement\u003c/code\u003e\u003c/em\u003e;\n  \u003cem class=\"replaceable\"\u003e\u003ccode\u003estatement\u003c/code\u003e\u003c/em\u003e;\n  ...\n  \u003cem class=\"replaceable\"\u003e\u003ccode\u003estatement\u003c/code\u003e\u003c/em\u003e;\nEND\n\u003c/pre\u003e\u003cp\u003e\n\u003c/p\u003e\u003cp\u003e\n       这类似于把过程体文本写成字符串常量（见上面的 \u003cem class=\"replaceable\"\u003e\u003ccode\u003edefinition\u003c/code\u003e\u003c/em\u003e），但有几点不同：这种形式只适用于 \u003ccode class=\"literal\"\u003eLANGUAGE SQL\u003c/code\u003e，而字符串常量形式适用于所有语言。这种形式在过程定义时解析，字符串常量形式则在执行时解析；因此，这种形式不支持多态参数类型以及其他无法在过程定义时解析的构造。这种形式会跟踪过程与过程体中所用对象之间的依赖关系，因此 \u003ccode class=\"literal\"\u003eDROP ... CASCADE\u003c/code\u003e 能够正常工作，而使用字符串字面量的形式则可能留下悬空过程。最后，这种形式与 SQL 标准以及其他 SQL 实现更兼容。\n      \u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e注解\u003c/h2\u003e\u003cp\u003e\n   有关函数创建且同样适用于过程的更多细节，请参见 \u003ca class=\"xref\" href=\"/zh/docs/18/sql-createfunction.html\" title=\"CREATE FUNCTION\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE FUNCTION\u003c/span\u003e\u003c/a\u003e。\n  \u003c/p\u003e\u003cp\u003e\n   使用 \u003ca class=\"xref\" href=\"/zh/docs/18/sql-call.html\" title=\"CALL\"\u003e\u003cspan class=\"refentrytitle\"\u003eCALL\u003c/span\u003e\u003c/a\u003e 执行过程。\n  \u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e示例\u003c/h2\u003e\u003cp\u003e\n\u003c/p\u003e\u003cpre class=\"programlisting\"\u003e\nCREATE PROCEDURE insert_data(a integer, b integer)\nLANGUAGE SQL\nAS $$\nINSERT INTO tbl VALUES (a);\nINSERT INTO tbl VALUES (b);\n$$;\n\u003c/pre\u003e\u003cp\u003e\n   或者\n\u003c/p\u003e\u003cpre class=\"programlisting\"\u003e\nCREATE PROCEDURE insert_data(a integer, b integer)\nLANGUAGE SQL\nBEGIN ATOMIC\n  INSERT INTO tbl VALUES (a);\n  INSERT INTO tbl VALUES (b);\nEND;\n\u003c/pre\u003e\u003cp\u003e\n   调用方式如下：\n\u003c/p\u003e\u003cpre class=\"programlisting\"\u003e\nCALL insert_data(1, 2);\n\u003c/pre\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e兼容性\u003c/h2\u003e\u003cp\u003e\n   一条 \u003ccode class=\"command\"\u003eCREATE PROCEDURE\u003c/code\u003e 命令由 SQL 标准定义。\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e 的实现可以以兼容方式使用，但也包含许多扩展。详见 \u003ca class=\"xref\" href=\"/zh/docs/18/sql-createfunction.html\" title=\"CREATE FUNCTION\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE FUNCTION\u003c/span\u003e\u003c/a\u003e。\n  \u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e另见\u003c/h2\u003e\u003cspan class=\"simplelist\"\u003e\u003ca class=\"xref\" href=\"/zh/docs/18/sql-alterprocedure.html\" title=\"ALTER PROCEDURE\"\u003e\u003cspan class=\"refentrytitle\"\u003eALTER PROCEDURE\u003c/span\u003e\u003c/a\u003e, \u003ca class=\"xref\" href=\"/zh/docs/18/sql-dropprocedure.html\" title=\"DROP PROCEDURE\"\u003e\u003cspan class=\"refentrytitle\"\u003eDROP PROCEDURE\u003c/span\u003e\u003c/a\u003e, \u003ca class=\"xref\" href=\"/zh/docs/18/sql-call.html\" title=\"CALL\"\u003e\u003cspan class=\"refentrytitle\"\u003eCALL\u003c/span\u003e\u003c/a\u003e, \u003ca class=\"xref\" href=\"/zh/docs/18/sql-createfunction.html\" title=\"CREATE FUNCTION\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE FUNCTION\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e\u003c/section\u003e","documentation_revision":{"inputs":[{"file":"sql-createprocedure.html","kind":"same-version-reviewed-manual","locale":"en","sha256":"d36d21c5ac88afaaebb35b0ed59e06177f915733e8ed804be11509c5afe619a8","version":"18.7"},{"file":"sql-createprocedure.html","kind":"same-version-reviewed-manual","locale":"zh-Hans","sha256":"e73cebfbe5318a19fb36b9565b1e7f2dfafc0ab2d3571a957f24647dbcffd0bb","version":"18.7"}],"release":"18.7","runtime_remeasured":false,"source_revision":"pgdoc:b9f0e7232f60f380d642ac9b99226606c254b552"},"file":"sql-createprocedure.html","lang":"en","name":"CREATE PROCEDURE","purpose":"define a new procedure","purpose_zh":"定义一个新过程","related":["alter-procedure","drop-procedure","call","create-function"],"sections":[{"html":"\u003cp\u003e\n\u003ccode class=\"command\"\u003eCREATE PROCEDURE\u003c/code\u003e defines a new procedure.\n   \u003ccode class=\"command\"\u003eCREATE OR REPLACE PROCEDURE\u003c/code\u003e will either create a\n   new procedure, or replace an existing definition.\n   To be able to define a procedure, the user must have the\n   \u003ccode class=\"literal\"\u003eUSAGE\u003c/code\u003e privilege on the language.\n  \u003c/p\u003e\u003cp\u003e\n   If a schema name is included, then the procedure is created in the\n   specified schema.  Otherwise it is created in the current schema.\n   The name of the new procedure must not match any existing procedure or function\n   with the same input argument types in the same schema.  However,\n   procedures and functions of different argument types can share a name (this is\n   called \u003cem class=\"firstterm\"\u003eoverloading\u003c/em\u003e).\n  \u003c/p\u003e\u003cp\u003e\n   To replace the current definition of an existing procedure, use\n   \u003ccode class=\"command\"\u003eCREATE OR REPLACE PROCEDURE\u003c/code\u003e.  It is not possible\n   to change the name or argument types of a procedure this way (if you\n   tried, you would actually be creating a new, distinct procedure).\n  \u003c/p\u003e\u003cp\u003e\n   When \u003ccode class=\"command\"\u003eCREATE OR REPLACE PROCEDURE\u003c/code\u003e is used to replace an\n   existing procedure, the ownership and permissions of the procedure\n   do not change.  All other procedure properties are assigned the\n   values specified or implied in the command.  You must own the procedure\n   to replace it (this includes being a member of the owning role).\n  \u003c/p\u003e\u003cp\u003e\n   The user that creates the procedure becomes the owner of the procedure.\n  \u003c/p\u003e\u003cp\u003e\n   To be able to create a procedure, you must have \u003ccode class=\"literal\"\u003eUSAGE\u003c/code\u003e\n   privilege on the argument types.\n  \u003c/p\u003e\u003cp\u003e\n   Refer to \u003ca class=\"xref\" href=\"/docs/18/xproc.html\" title=\"36.4. User-Defined Procedures\"\u003eSection 36.4\u003c/a\u003e for further information on writing\n   procedures.\n  \u003c/p\u003e","key":"description","title":"Description"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n       The name (optionally schema-qualified) of the procedure to create.\n      \u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eargmode\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n       The mode of an argument: \u003ccode class=\"literal\"\u003eIN\u003c/code\u003e, \u003ccode class=\"literal\"\u003eOUT\u003c/code\u003e,\n       \u003ccode class=\"literal\"\u003eINOUT\u003c/code\u003e, or \u003ccode class=\"literal\"\u003eVARIADIC\u003c/code\u003e.  If omitted,\n       the default is \u003ccode class=\"literal\"\u003eIN\u003c/code\u003e.\n      \u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eargname\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n       The name of an argument.\n      \u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eargtype\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n       The data type(s) of the procedure's arguments (optionally\n       schema-qualified), if any. The argument types can be base, composite,\n       or domain types, or can reference the type of a table column.\n      \u003c/p\u003e\u003cp\u003e\n       Depending on the implementation language it might also be allowed\n       to specify \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003epseudo-types\u003c/span\u003e”\u003c/span\u003e such as \u003ccode class=\"type\"\u003ecstring\u003c/code\u003e.\n       Pseudo-types indicate that the actual argument type is either\n       incompletely specified, or outside the set of ordinary SQL data types.\n      \u003c/p\u003e\u003cp\u003e\n       The type of a column is referenced by writing\n       \u003ccode class=\"literal\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e.\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e%TYPE\u003c/code\u003e.\n       Using this feature can sometimes help make a procedure independent of\n       changes to the definition of a table.\n      \u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003edefault_expr\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n       An expression to be used as default value if the parameter is\n       not specified.  The expression has to be coercible to the\n       argument type of the parameter.\n       All input parameters following a\n       parameter with a default value must have default values as well.\n      \u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003elang_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n       The name of the language that the procedure is implemented in.\n       It can be \u003ccode class=\"literal\"\u003esql\u003c/code\u003e, \u003ccode class=\"literal\"\u003ec\u003c/code\u003e,\n       \u003ccode class=\"literal\"\u003einternal\u003c/code\u003e, or the name of a user-defined\n       procedural language, e.g., \u003ccode class=\"literal\"\u003eplpgsql\u003c/code\u003e.  The default is\n       \u003ccode class=\"literal\"\u003esql\u003c/code\u003e if \u003cem class=\"replaceable\"\u003e\u003ccode\u003esql_body\u003c/code\u003e\u003c/em\u003e is specified.  Enclosing the\n       name in single quotes is deprecated and requires matching case.\n      \u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eTRANSFORM { FOR TYPE \u003cem class=\"replaceable\"\u003e\u003ccode\u003etype_name\u003c/code\u003e\u003c/em\u003e } [, ... ]\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n       Lists which transforms a call to the procedure should apply.  Transforms\n       convert between SQL types and language-specific data types;\n       see \u003ca class=\"xref\" href=\"/docs/18/sql-createtransform.html\" title=\"CREATE TRANSFORM\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE TRANSFORM\u003c/span\u003e\u003c/a\u003e.  Procedural language\n       implementations usually have hardcoded knowledge of the built-in types,\n       so those don't need to be listed here.  If a procedural language\n       implementation does not know how to handle a type and no transform is\n       supplied, it will fall back to a default behavior for converting data\n       types, but this depends on the implementation.\n      \u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003e[\u003cspan class=\"optional\"\u003eEXTERNAL\u003c/span\u003e] SECURITY INVOKER\u003c/code\u003e\u003cbr/\u003e\u003c/span\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003e[\u003cspan class=\"optional\"\u003eEXTERNAL\u003c/span\u003e] SECURITY DEFINER\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\u003ccode class=\"literal\"\u003eSECURITY INVOKER\u003c/code\u003e indicates that the procedure\n      is to be executed with the privileges of the user that calls it.\n      That is the default.  \u003ccode class=\"literal\"\u003eSECURITY DEFINER\u003c/code\u003e\n      specifies that the procedure is to be executed with the\n      privileges of the user that owns it.\n     \u003c/p\u003e\u003cp\u003e\n      The key word \u003ccode class=\"literal\"\u003eEXTERNAL\u003c/code\u003e is allowed for SQL\n      conformance, but it is optional since, unlike in SQL, this feature\n      applies to all procedures not only external ones.\n     \u003c/p\u003e\u003cp\u003e\n      A \u003ccode class=\"literal\"\u003eSECURITY DEFINER\u003c/code\u003e procedure cannot execute\n      transaction control statements (for example, \u003ccode class=\"command\"\u003eCOMMIT\u003c/code\u003e\n      and \u003ccode class=\"command\"\u003eROLLBACK\u003c/code\u003e, depending on the language).\n     \u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003econfiguration_parameter\u003c/code\u003e\u003c/em\u003e\u003cbr/\u003e\u003c/span\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003evalue\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n       The \u003ccode class=\"literal\"\u003eSET\u003c/code\u003e clause causes the specified configuration\n       parameter to be set to the specified value when the procedure is\n       entered, and then restored to its prior value when the procedure exits.\n       \u003ccode class=\"literal\"\u003eSET FROM CURRENT\u003c/code\u003e saves the value of the parameter that\n       is current when \u003ccode class=\"command\"\u003eCREATE PROCEDURE\u003c/code\u003e is executed as the value\n       to be applied when the procedure is entered.\n      \u003c/p\u003e\u003cp\u003e\n       If a \u003ccode class=\"literal\"\u003eSET\u003c/code\u003e clause is attached to a procedure, then\n       the effects of a \u003ccode class=\"command\"\u003eSET LOCAL\u003c/code\u003e command executed inside the\n       procedure for the same variable are restricted to the procedure: the\n       configuration parameter's prior value is still restored at procedure exit.\n       However, an ordinary\n       \u003ccode class=\"command\"\u003eSET\u003c/code\u003e command (without \u003ccode class=\"literal\"\u003eLOCAL\u003c/code\u003e) overrides the\n       \u003ccode class=\"literal\"\u003eSET\u003c/code\u003e clause, much as it would do for a previous \u003ccode class=\"command\"\u003eSET\n       LOCAL\u003c/code\u003e command: the effects of such a command will persist after\n       procedure exit, unless the current transaction is rolled back.\n      \u003c/p\u003e\u003cp\u003e\n       If a \u003ccode class=\"literal\"\u003eSET\u003c/code\u003e clause is attached to a procedure, then\n       that procedure cannot execute transaction control statements (for\n       example, \u003ccode class=\"command\"\u003eCOMMIT\u003c/code\u003e and \u003ccode class=\"command\"\u003eROLLBACK\u003c/code\u003e,\n       depending on the language).\n      \u003c/p\u003e\u003cp\u003e\n       See \u003ca class=\"xref\" href=\"/docs/18/sql-set.html\" title=\"SET\"\u003e\u003cspan class=\"refentrytitle\"\u003eSET\u003c/span\u003e\u003c/a\u003e and\n       \u003ca class=\"xref\" href=\"/docs/18/runtime-config.html\" title=\"Chapter 19. Server Configuration\"\u003eChapter 19\u003c/a\u003e\n       for more information about allowed parameter names and values.\n      \u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003edefinition\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n       A string constant defining the procedure; the meaning depends on the\n       language.  It can be an internal procedure name, the path to an\n       object file, an SQL command, or text in a procedural language.\n      \u003c/p\u003e\u003cp\u003e\n       It is often helpful to use dollar quoting (see \u003ca class=\"xref\" href=\"/docs/18/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING\" title=\"4.1.2.4. Dollar-Quoted String Constants\"\u003eSection 4.1.2.4\u003c/a\u003e) to write the procedure definition\n       string, rather than the normal single quote syntax.  Without dollar\n       quoting, any single quotes or backslashes in the procedure definition must\n       be escaped by doubling them.\n      \u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eobj_file\u003c/code\u003e\u003c/em\u003e, \u003cem class=\"replaceable\"\u003e\u003ccode\u003elink_symbol\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n       This form of the \u003ccode class=\"literal\"\u003eAS\u003c/code\u003e clause is used for\n       dynamically loadable C language procedures when the procedure name\n       in the C language source code is not the same as the name of\n       the SQL procedure. The string \u003cem class=\"replaceable\"\u003e\u003ccode\u003eobj_file\u003c/code\u003e\u003c/em\u003e is the name of the shared\n       library file containing the compiled C procedure, and is interpreted\n       as for the \u003ca class=\"link\" href=\"/docs/18/sql-load.html\" title=\"LOAD\"\u003e\u003ccode class=\"command\"\u003eLOAD\u003c/code\u003e\u003c/a\u003e command.  The string\n       \u003cem class=\"replaceable\"\u003e\u003ccode\u003elink_symbol\u003c/code\u003e\u003c/em\u003e is the\n       procedure's link symbol, that is, the name of the procedure in the C\n       language source code.  If the link symbol is omitted, it is assumed\n       to be the same as the name of the SQL procedure being defined.\n      \u003c/p\u003e\u003cp\u003e\n       When repeated \u003ccode class=\"command\"\u003eCREATE PROCEDURE\u003c/code\u003e calls refer to\n       the same object file, the file is only loaded once per session.\n       To unload and\n       reload the file (perhaps during development), start a new session.\n      \u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003esql_body\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n       The body of a \u003ccode class=\"literal\"\u003eLANGUAGE SQL\u003c/code\u003e procedure.  This should\n       be a block\n\u003c/p\u003e\u003cpre class=\"programlisting\"\u003e\nBEGIN ATOMIC\n  \u003cem class=\"replaceable\"\u003e\u003ccode\u003estatement\u003c/code\u003e\u003c/em\u003e;\n  \u003cem class=\"replaceable\"\u003e\u003ccode\u003estatement\u003c/code\u003e\u003c/em\u003e;\n  ...\n  \u003cem class=\"replaceable\"\u003e\u003ccode\u003estatement\u003c/code\u003e\u003c/em\u003e;\nEND\n\u003c/pre\u003e\u003cp\u003e\n\u003c/p\u003e\u003cp\u003e\n       This is similar to writing the text of the procedure body as a string\n       constant (see \u003cem class=\"replaceable\"\u003e\u003ccode\u003edefinition\u003c/code\u003e\u003c/em\u003e above), but there\n       are some differences: This form only works for \u003ccode class=\"literal\"\u003eLANGUAGE\n       SQL\u003c/code\u003e, the string constant form works for all languages.  This\n       form is parsed at procedure definition time, the string constant form is\n       parsed at execution time; therefore this form cannot support\n       polymorphic argument types and other constructs that are not resolvable\n       at procedure definition time.  This form tracks dependencies between the\n       procedure and objects used in the procedure body, so \u003ccode class=\"literal\"\u003eDROP\n       ... CASCADE\u003c/code\u003e will work correctly, whereas the form using\n       string literals may leave dangling procedures.  Finally, this form is\n       more compatible with the SQL standard and other SQL implementations.\n      \u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e","key":"parameters","title":"Parameters"},{"html":"\u003cp\u003e\n   See \u003ca class=\"xref\" href=\"/docs/18/sql-createfunction.html\" title=\"CREATE FUNCTION\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE FUNCTION\u003c/span\u003e\u003c/a\u003e for more details on function\n   creation that also apply to procedures.\n  \u003c/p\u003e\u003cp\u003e\n   Use \u003ca class=\"xref\" href=\"/docs/18/sql-call.html\" title=\"CALL\"\u003e\u003cspan class=\"refentrytitle\"\u003eCALL\u003c/span\u003e\u003c/a\u003e to execute a procedure.\n  \u003c/p\u003e","key":"notes","title":"Notes"},{"html":"\u003cp\u003e\n\u003c/p\u003e\u003cpre class=\"programlisting\"\u003e\nCREATE PROCEDURE insert_data(a integer, b integer)\nLANGUAGE SQL\nAS $$\nINSERT INTO tbl VALUES (a);\nINSERT INTO tbl VALUES (b);\n$$;\n\u003c/pre\u003e\u003cp\u003e\n   or\n\u003c/p\u003e\u003cpre class=\"programlisting\"\u003e\nCREATE PROCEDURE insert_data(a integer, b integer)\nLANGUAGE SQL\nBEGIN ATOMIC\n  INSERT INTO tbl VALUES (a);\n  INSERT INTO tbl VALUES (b);\nEND;\n\u003c/pre\u003e\u003cp\u003e\n   and call like this:\n\u003c/p\u003e\u003cpre class=\"programlisting\"\u003e\nCALL insert_data(1, 2);\n\u003c/pre\u003e","key":"examples","title":"Examples"},{"html":"\u003cp\u003e\n   A \u003ccode class=\"command\"\u003eCREATE PROCEDURE\u003c/code\u003e command is defined in the SQL\n   standard.  The \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e implementation can be\n   used in a compatible way but has many extensions.  For details see also\n   \u003ca class=\"xref\" href=\"/docs/18/sql-createfunction.html\" title=\"CREATE FUNCTION\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE FUNCTION\u003c/span\u003e\u003c/a\u003e.\n  \u003c/p\u003e","key":"compatibility","title":"Compatibility"},{"html":"\u003cspan class=\"simplelist\"\u003e\u003ca class=\"xref\" href=\"/docs/18/sql-alterprocedure.html\" title=\"ALTER PROCEDURE\"\u003e\u003cspan class=\"refentrytitle\"\u003eALTER PROCEDURE\u003c/span\u003e\u003c/a\u003e, \u003ca class=\"xref\" href=\"/docs/18/sql-dropprocedure.html\" title=\"DROP PROCEDURE\"\u003e\u003cspan class=\"refentrytitle\"\u003eDROP PROCEDURE\u003c/span\u003e\u003c/a\u003e, \u003ca class=\"xref\" href=\"/docs/18/sql-call.html\" title=\"CALL\"\u003e\u003cspan class=\"refentrytitle\"\u003eCALL\u003c/span\u003e\u003c/a\u003e, \u003ca class=\"xref\" href=\"/docs/18/sql-createfunction.html\" title=\"CREATE FUNCTION\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE FUNCTION\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e","key":"see_also","title":"See Also"}],"sections_same_as":"","slug":"18","synopsis_html":"CREATE [ OR REPLACE ] PROCEDURE\n    \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e ( [ [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eargmode\u003c/code\u003e\u003c/em\u003e ] [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eargname\u003c/code\u003e\u003c/em\u003e ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003eargtype\u003c/code\u003e\u003c/em\u003e [ { DEFAULT | = } \u003cem class=\"replaceable\"\u003e\u003ccode\u003edefault_expr\u003c/code\u003e\u003c/em\u003e ] [, ...] ] )\n  { LANGUAGE \u003cem class=\"replaceable\"\u003e\u003ccode\u003elang_name\u003c/code\u003e\u003c/em\u003e\n    | TRANSFORM { FOR TYPE \u003cem class=\"replaceable\"\u003e\u003ccode\u003etype_name\u003c/code\u003e\u003c/em\u003e } [, ... ]\n    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER\n    | SET \u003cem class=\"replaceable\"\u003e\u003ccode\u003econfiguration_parameter\u003c/code\u003e\u003c/em\u003e { TO \u003cem class=\"replaceable\"\u003e\u003ccode\u003evalue\u003c/code\u003e\u003c/em\u003e | = \u003cem class=\"replaceable\"\u003e\u003ccode\u003evalue\u003c/code\u003e\u003c/em\u003e | FROM CURRENT }\n    | AS '\u003cem class=\"replaceable\"\u003e\u003ccode\u003edefinition\u003c/code\u003e\u003c/em\u003e'\n    | AS '\u003cem class=\"replaceable\"\u003e\u003ccode\u003eobj_file\u003c/code\u003e\u003c/em\u003e', '\u003cem class=\"replaceable\"\u003e\u003ccode\u003elink_symbol\u003c/code\u003e\u003c/em\u003e'\n    | \u003cem class=\"replaceable\"\u003e\u003ccode\u003esql_body\u003c/code\u003e\u003c/em\u003e\n  } ...","synopsis_text":"CREATE [ OR REPLACE ] PROCEDURE\nname ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )\n{ LANGUAGE lang_name\n| TRANSFORM { FOR TYPE type_name } [, ... ]\n| [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER\n| SET configuration_parameter { TO value | = value | FROM CURRENT }\n| AS 'definition'\n| AS 'obj_file', 'link_symbol'\n| sql_body\n} ...","translation_provenance":{"inputs":[{"kind":"current-pgdoc-translation","path":"/Users/vonng/pgsty/pgdoc/zh/18/html/sql-createprocedure.html","sha256":"c61edd15eedfa66ebd1403a7b5a04015c0a7de2e0461e53debce4ea8b770235a"}],"kind":"same-version-pgdoc","locale":"zh-Hans","source_revision":"b1b500221dbe5437cfca004d6b04d3f6e422765d","version":"18"},"zh_from":"same-version-manual"},"ManualEvidence":{},"MeasuredEvidence":{}},"Text":{"Collection":"sql","Key":"create-procedure","SourceDatabase":"center","Version":"18","Locale":"en","Title":"CREATE PROCEDURE","Summary":"define a new procedure","BodyHTML":"\u003cpre\u003eCREATE [ OR REPLACE ] PROCEDURE\nname ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )\n{ LANGUAGE lang_name\n| TRANSFORM { FOR TYPE type_name } [, ... ]\n| [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER\n| SET configuration_parameter { TO value | = value | FROM CURRENT }\n| AS \u0026#39;definition\u0026#39;\n| AS \u0026#39;obj_file\u0026#39;, \u0026#39;link_symbol\u0026#39;\n| sql_body\n} ...\u003c/pre\u003e\u003csection\u003e\u003ch2\u003eDescription\u003c/h2\u003e\u003cp\u003e\n\u003ccode\u003eCREATE PROCEDURE\u003c/code\u003e defines a new procedure.\n   \u003ccode\u003eCREATE OR REPLACE PROCEDURE\u003c/code\u003e will either create a\n   new procedure, or replace an existing definition.\n   To be able to define a procedure, the user must have the\n   \u003ccode\u003eUSAGE\u003c/code\u003e privilege on the language.\n  \u003c/p\u003e\u003cp\u003e\n   If a schema name is included, then the procedure is created in the\n   specified schema.  Otherwise it is created in the current schema.\n   The name of the new procedure must not match any existing procedure or function\n   with the same input argument types in the same schema.  However,\n   procedures and functions of different argument types can share a name (this is\n   called \u003cem\u003eoverloading\u003c/em\u003e).\n  \u003c/p\u003e\u003cp\u003e\n   To replace the current definition of an existing procedure, use\n   \u003ccode\u003eCREATE OR REPLACE PROCEDURE\u003c/code\u003e.  It is not possible\n   to change the name or argument types of a procedure this way (if you\n   tried, you would actually be creating a new, distinct procedure).\n  \u003c/p\u003e\u003cp\u003e\n   When \u003ccode\u003eCREATE OR REPLACE PROCEDURE\u003c/code\u003e is used to replace an\n   existing procedure, the ownership and permissions of the procedure\n   do not change.  All other procedure properties are assigned the\n   values specified or implied in the command.  You must own the procedure\n   to replace it (this includes being a member of the owning role).\n  \u003c/p\u003e\u003cp\u003e\n   The user that creates the procedure becomes the owner of the procedure.\n  \u003c/p\u003e\u003cp\u003e\n   To be able to create a procedure, you must have \u003ccode\u003eUSAGE\u003c/code\u003e\n   privilege on the argument types.\n  \u003c/p\u003e\u003cp\u003e\n   Refer to \u003ca href=\"/docs/18/xproc.html\" rel=\"nofollow\"\u003eSection 36.4\u003c/a\u003e for further information on writing\n   procedures.\n  \u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003eParameters\u003c/h2\u003e\u003cdiv\u003e\u003cdl\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n       The name (optionally schema-qualified) of the procedure to create.\n      \u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003eargmode\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n       The mode of an argument: \u003ccode\u003eIN\u003c/code\u003e, \u003ccode\u003eOUT\u003c/code\u003e,\n       \u003ccode\u003eINOUT\u003c/code\u003e, or \u003ccode\u003eVARIADIC\u003c/code\u003e.  If omitted,\n       the default is \u003ccode\u003eIN\u003c/code\u003e.\n      \u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003eargname\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n       The name of an argument.\n      \u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003eargtype\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n       The data type(s) of the procedure\u0026#39;s arguments (optionally\n       schema-qualified), if any. The argument types can be base, composite,\n       or domain types, or can reference the type of a table column.\n      \u003c/p\u003e\u003cp\u003e\n       Depending on the implementation language it might also be allowed\n       to specify \u003cspan\u003e“\u003cspan\u003epseudo-types\u003c/span\u003e”\u003c/span\u003e such as \u003ccode\u003ecstring\u003c/code\u003e.\n       Pseudo-types indicate that the actual argument type is either\n       incompletely specified, or outside the set of ordinary SQL data types.\n      \u003c/p\u003e\u003cp\u003e\n       The type of a column is referenced by writing\n       \u003ccode\u003e\u003cem\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e.\u003cem\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e%TYPE\u003c/code\u003e.\n       Using this feature can sometimes help make a procedure independent of\n       changes to the definition of a table.\n      \u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003edefault_expr\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n       An expression to be used as default value if the parameter is\n       not specified.  The expression has to be coercible to the\n       argument type of the parameter.\n       All input parameters following a\n       parameter with a default value must have default values as well.\n      \u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003elang_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n       The name of the language that the procedure is implemented in.\n       It can be \u003ccode\u003esql\u003c/code\u003e, \u003ccode\u003ec\u003c/code\u003e,\n       \u003ccode\u003einternal\u003c/code\u003e, or the name of a user-defined\n       procedural language, e.g., \u003ccode\u003eplpgsql\u003c/code\u003e.  The default is\n       \u003ccode\u003esql\u003c/code\u003e if \u003cem\u003e\u003ccode\u003esql_body\u003c/code\u003e\u003c/em\u003e is specified.  Enclosing the\n       name in single quotes is deprecated and requires matching case.\n      \u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eTRANSFORM { FOR TYPE \u003cem\u003e\u003ccode\u003etype_name\u003c/code\u003e\u003c/em\u003e } [, ... ]\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n       Lists which transforms a call to the procedure should apply.  Transforms\n       convert between SQL types and language-specific data types;\n       see \u003ca href=\"/docs/18/sql-createtransform.html\" title=\"CREATE TRANSFORM\" rel=\"nofollow\"\u003e\u003cspan\u003eCREATE TRANSFORM\u003c/span\u003e\u003c/a\u003e.  Procedural language\n       implementations usually have hardcoded knowledge of the built-in types,\n       so those don\u0026#39;t need to be listed here.  If a procedural language\n       implementation does not know how to handle a type and no transform is\n       supplied, it will fall back to a default behavior for converting data\n       types, but this depends on the implementation.\n      \u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003e[\u003cspan\u003eEXTERNAL\u003c/span\u003e] SECURITY INVOKER\u003c/code\u003e\u003cbr/\u003e\u003c/span\u003e\u003cspan\u003e\u003ccode\u003e[\u003cspan\u003eEXTERNAL\u003c/span\u003e] SECURITY DEFINER\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\u003ccode\u003eSECURITY INVOKER\u003c/code\u003e indicates that the procedure\n      is to be executed with the privileges of the user that calls it.\n      That is the default.  \u003ccode\u003eSECURITY DEFINER\u003c/code\u003e\n      specifies that the procedure is to be executed with the\n      privileges of the user that owns it.\n     \u003c/p\u003e\u003cp\u003e\n      The key word \u003ccode\u003eEXTERNAL\u003c/code\u003e is allowed for SQL\n      conformance, but it is optional since, unlike in SQL, this feature\n      applies to all procedures not only external ones.\n     \u003c/p\u003e\u003cp\u003e\n      A \u003ccode\u003eSECURITY DEFINER\u003c/code\u003e procedure cannot execute\n      transaction control statements (for example, \u003ccode\u003eCOMMIT\u003c/code\u003e\n      and \u003ccode\u003eROLLBACK\u003c/code\u003e, depending on the language).\n     \u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003econfiguration_parameter\u003c/code\u003e\u003c/em\u003e\u003cbr/\u003e\u003c/span\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003evalue\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n       The \u003ccode\u003eSET\u003c/code\u003e clause causes the specified configuration\n       parameter to be set to the specified value when the procedure is\n       entered, and then restored to its prior value when the procedure exits.\n       \u003ccode\u003eSET FROM CURRENT\u003c/code\u003e saves the value of the parameter that\n       is current when \u003ccode\u003eCREATE PROCEDURE\u003c/code\u003e is executed as the value\n       to be applied when the procedure is entered.\n      \u003c/p\u003e\u003cp\u003e\n       If a \u003ccode\u003eSET\u003c/code\u003e clause is attached to a procedure, then\n       the effects of a \u003ccode\u003eSET LOCAL\u003c/code\u003e command executed inside the\n       procedure for the same variable are restricted to the procedure: the\n       configuration parameter\u0026#39;s prior value is still restored at procedure exit.\n       However, an ordinary\n       \u003ccode\u003eSET\u003c/code\u003e command (without \u003ccode\u003eLOCAL\u003c/code\u003e) overrides the\n       \u003ccode\u003eSET\u003c/code\u003e clause, much as it would do for a previous \u003ccode\u003eSET\n       LOCAL\u003c/code\u003e command: the effects of such a command will persist after\n       procedure exit, unless the current transaction is rolled back.\n      \u003c/p\u003e\u003cp\u003e\n       If a \u003ccode\u003eSET\u003c/code\u003e clause is attached to a procedure, then\n       that procedure cannot execute transaction control statements (for\n       example, \u003ccode\u003eCOMMIT\u003c/code\u003e and \u003ccode\u003eROLLBACK\u003c/code\u003e,\n       depending on the language).\n      \u003c/p\u003e\u003cp\u003e\n       See \u003ca href=\"/docs/18/sql-set.html\" title=\"SET\" rel=\"nofollow\"\u003e\u003cspan\u003eSET\u003c/span\u003e\u003c/a\u003e and\n       \u003ca href=\"/docs/18/runtime-config.html\" rel=\"nofollow\"\u003eChapter 19\u003c/a\u003e\n       for more information about allowed parameter names and values.\n      \u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003edefinition\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n       A string constant defining the procedure; the meaning depends on the\n       language.  It can be an internal procedure name, the path to an\n       object file, an SQL command, or text in a procedural language.\n      \u003c/p\u003e\u003cp\u003e\n       It is often helpful to use dollar quoting (see \u003ca href=\"/docs/18/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING\" rel=\"nofollow\"\u003eSection 4.1.2.4\u003c/a\u003e) to write the procedure definition\n       string, rather than the normal single quote syntax.  Without dollar\n       quoting, any single quotes or backslashes in the procedure definition must\n       be escaped by doubling them.\n      \u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003e\u003cem\u003e\u003ccode\u003eobj_file\u003c/code\u003e\u003c/em\u003e, \u003cem\u003e\u003ccode\u003elink_symbol\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n       This form of the \u003ccode\u003eAS\u003c/code\u003e clause is used for\n       dynamically loadable C language procedures when the procedure name\n       in the C language source code is not the same as the name of\n       the SQL procedure. The string \u003cem\u003e\u003ccode\u003eobj_file\u003c/code\u003e\u003c/em\u003e is the name of the shared\n       library file containing the compiled C procedure, and is interpreted\n       as for the \u003ca href=\"/docs/18/sql-load.html\" title=\"LOAD\" rel=\"nofollow\"\u003e\u003ccode\u003eLOAD\u003c/code\u003e\u003c/a\u003e command.  The string\n       \u003cem\u003e\u003ccode\u003elink_symbol\u003c/code\u003e\u003c/em\u003e is the\n       procedure\u0026#39;s link symbol, that is, the name of the procedure in the C\n       language source code.  If the link symbol is omitted, it is assumed\n       to be the same as the name of the SQL procedure being defined.\n      \u003c/p\u003e\u003cp\u003e\n       When repeated \u003ccode\u003eCREATE PROCEDURE\u003c/code\u003e calls refer to\n       the same object file, the file is only loaded once per session.\n       To unload and\n       reload the file (perhaps during development), start a new session.\n      \u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003esql_body\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n       The body of a \u003ccode\u003eLANGUAGE SQL\u003c/code\u003e procedure.  This should\n       be a block\n\u003c/p\u003e\u003cpre\u003e\nBEGIN ATOMIC\n  \u003cem\u003e\u003ccode\u003estatement\u003c/code\u003e\u003c/em\u003e;\n  \u003cem\u003e\u003ccode\u003estatement\u003c/code\u003e\u003c/em\u003e;\n  ...\n  \u003cem\u003e\u003ccode\u003estatement\u003c/code\u003e\u003c/em\u003e;\nEND\n\u003c/pre\u003e\u003cp\u003e\n\u003c/p\u003e\u003cp\u003e\n       This is similar to writing the text of the procedure body as a string\n       constant (see \u003cem\u003e\u003ccode\u003edefinition\u003c/code\u003e\u003c/em\u003e above), but there\n       are some differences: This form only works for \u003ccode\u003eLANGUAGE\n       SQL\u003c/code\u003e, the string constant form works for all languages.  This\n       form is parsed at procedure definition time, the string constant form is\n       parsed at execution time; therefore this form cannot support\n       polymorphic argument types and other constructs that are not resolvable\n       at procedure definition time.  This form tracks dependencies between the\n       procedure and objects used in the procedure body, so \u003ccode\u003eDROP\n       ... CASCADE\u003c/code\u003e will work correctly, whereas the form using\n       string literals may leave dangling procedures.  Finally, this form is\n       more compatible with the SQL standard and other SQL implementations.\n      \u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003eNotes\u003c/h2\u003e\u003cp\u003e\n   See \u003ca href=\"/docs/18/sql-createfunction.html\" title=\"CREATE FUNCTION\" rel=\"nofollow\"\u003e\u003cspan\u003eCREATE FUNCTION\u003c/span\u003e\u003c/a\u003e for more details on function\n   creation that also apply to procedures.\n  \u003c/p\u003e\u003cp\u003e\n   Use \u003ca href=\"/docs/18/sql-call.html\" title=\"CALL\" rel=\"nofollow\"\u003e\u003cspan\u003eCALL\u003c/span\u003e\u003c/a\u003e to execute a procedure.\n  \u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003eExamples\u003c/h2\u003e\u003cp\u003e\n\u003c/p\u003e\u003cpre\u003e\nCREATE PROCEDURE insert_data(a integer, b integer)\nLANGUAGE SQL\nAS $$\nINSERT INTO tbl VALUES (a);\nINSERT INTO tbl VALUES (b);\n$$;\n\u003c/pre\u003e\u003cp\u003e\n   or\n\u003c/p\u003e\u003cpre\u003e\nCREATE PROCEDURE insert_data(a integer, b integer)\nLANGUAGE SQL\nBEGIN ATOMIC\n  INSERT INTO tbl VALUES (a);\n  INSERT INTO tbl VALUES (b);\nEND;\n\u003c/pre\u003e\u003cp\u003e\n   and call like this:\n\u003c/p\u003e\u003cpre\u003e\nCALL insert_data(1, 2);\n\u003c/pre\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003eCompatibility\u003c/h2\u003e\u003cp\u003e\n   A \u003ccode\u003eCREATE PROCEDURE\u003c/code\u003e command is defined in the SQL\n   standard.  The \u003cspan\u003ePostgreSQL\u003c/span\u003e implementation can be\n   used in a compatible way but has many extensions.  For details see also\n   \u003ca href=\"/docs/18/sql-createfunction.html\" title=\"CREATE FUNCTION\" rel=\"nofollow\"\u003e\u003cspan\u003eCREATE FUNCTION\u003c/span\u003e\u003c/a\u003e.\n  \u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003eSee Also\u003c/h2\u003e\u003cspan\u003e\u003ca href=\"/docs/18/sql-alterprocedure.html\" title=\"ALTER PROCEDURE\" rel=\"nofollow\"\u003e\u003cspan\u003eALTER PROCEDURE\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/docs/18/sql-dropprocedure.html\" title=\"DROP PROCEDURE\" rel=\"nofollow\"\u003e\u003cspan\u003eDROP PROCEDURE\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/docs/18/sql-call.html\" title=\"CALL\" rel=\"nofollow\"\u003e\u003cspan\u003eCALL\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/docs/18/sql-createfunction.html\" title=\"CREATE FUNCTION\" rel=\"nofollow\"\u003e\u003cspan\u003eCREATE FUNCTION\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e\u003c/section\u003e","SourceRevision":"wiki-repair-20261023:51eeabd094042d0d2e555f48d5ba6d5adb9cd4b28097f1c1d92e462ce64d9db9","ContentHash":"bae8e6b55403c1d3601a5446d911eb60c5af3355072122e991fee7aff65c5218","Payload":{"related":["alter-procedure","drop-procedure","call","create-function"],"sections":[{"html":"\u003cp\u003e\n\u003ccode class=\"command\"\u003eCREATE PROCEDURE\u003c/code\u003e defines a new procedure.\n   \u003ccode class=\"command\"\u003eCREATE OR REPLACE PROCEDURE\u003c/code\u003e will either create a\n   new procedure, or replace an existing definition.\n   To be able to define a procedure, the user must have the\n   \u003ccode class=\"literal\"\u003eUSAGE\u003c/code\u003e privilege on the language.\n  \u003c/p\u003e\u003cp\u003e\n   If a schema name is included, then the procedure is created in the\n   specified schema.  Otherwise it is created in the current schema.\n   The name of the new procedure must not match any existing procedure or function\n   with the same input argument types in the same schema.  However,\n   procedures and functions of different argument types can share a name (this is\n   called \u003cem class=\"firstterm\"\u003eoverloading\u003c/em\u003e).\n  \u003c/p\u003e\u003cp\u003e\n   To replace the current definition of an existing procedure, use\n   \u003ccode class=\"command\"\u003eCREATE OR REPLACE PROCEDURE\u003c/code\u003e.  It is not possible\n   to change the name or argument types of a procedure this way (if you\n   tried, you would actually be creating a new, distinct procedure).\n  \u003c/p\u003e\u003cp\u003e\n   When \u003ccode class=\"command\"\u003eCREATE OR REPLACE PROCEDURE\u003c/code\u003e is used to replace an\n   existing procedure, the ownership and permissions of the procedure\n   do not change.  All other procedure properties are assigned the\n   values specified or implied in the command.  You must own the procedure\n   to replace it (this includes being a member of the owning role).\n  \u003c/p\u003e\u003cp\u003e\n   The user that creates the procedure becomes the owner of the procedure.\n  \u003c/p\u003e\u003cp\u003e\n   To be able to create a procedure, you must have \u003ccode class=\"literal\"\u003eUSAGE\u003c/code\u003e\n   privilege on the argument types.\n  \u003c/p\u003e\u003cp\u003e\n   Refer to \u003ca class=\"xref\" href=\"/docs/18/xproc.html\" title=\"36.4. User-Defined Procedures\"\u003eSection 36.4\u003c/a\u003e for further information on writing\n   procedures.\n  \u003c/p\u003e","key":"description","title":"Description"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n       The name (optionally schema-qualified) of the procedure to create.\n      \u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eargmode\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n       The mode of an argument: \u003ccode class=\"literal\"\u003eIN\u003c/code\u003e, \u003ccode class=\"literal\"\u003eOUT\u003c/code\u003e,\n       \u003ccode class=\"literal\"\u003eINOUT\u003c/code\u003e, or \u003ccode class=\"literal\"\u003eVARIADIC\u003c/code\u003e.  If omitted,\n       the default is \u003ccode class=\"literal\"\u003eIN\u003c/code\u003e.\n      \u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eargname\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n       The name of an argument.\n      \u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eargtype\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n       The data type(s) of the procedure's arguments (optionally\n       schema-qualified), if any. The argument types can be base, composite,\n       or domain types, or can reference the type of a table column.\n      \u003c/p\u003e\u003cp\u003e\n       Depending on the implementation language it might also be allowed\n       to specify \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003epseudo-types\u003c/span\u003e”\u003c/span\u003e such as \u003ccode class=\"type\"\u003ecstring\u003c/code\u003e.\n       Pseudo-types indicate that the actual argument type is either\n       incompletely specified, or outside the set of ordinary SQL data types.\n      \u003c/p\u003e\u003cp\u003e\n       The type of a column is referenced by writing\n       \u003ccode class=\"literal\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003etable_name\u003c/code\u003e\u003c/em\u003e.\u003cem class=\"replaceable\"\u003e\u003ccode\u003ecolumn_name\u003c/code\u003e\u003c/em\u003e%TYPE\u003c/code\u003e.\n       Using this feature can sometimes help make a procedure independent of\n       changes to the definition of a table.\n      \u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003edefault_expr\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n       An expression to be used as default value if the parameter is\n       not specified.  The expression has to be coercible to the\n       argument type of the parameter.\n       All input parameters following a\n       parameter with a default value must have default values as well.\n      \u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003elang_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n       The name of the language that the procedure is implemented in.\n       It can be \u003ccode class=\"literal\"\u003esql\u003c/code\u003e, \u003ccode class=\"literal\"\u003ec\u003c/code\u003e,\n       \u003ccode class=\"literal\"\u003einternal\u003c/code\u003e, or the name of a user-defined\n       procedural language, e.g., \u003ccode class=\"literal\"\u003eplpgsql\u003c/code\u003e.  The default is\n       \u003ccode class=\"literal\"\u003esql\u003c/code\u003e if \u003cem class=\"replaceable\"\u003e\u003ccode\u003esql_body\u003c/code\u003e\u003c/em\u003e is specified.  Enclosing the\n       name in single quotes is deprecated and requires matching case.\n      \u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eTRANSFORM { FOR TYPE \u003cem class=\"replaceable\"\u003e\u003ccode\u003etype_name\u003c/code\u003e\u003c/em\u003e } [, ... ]\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n       Lists which transforms a call to the procedure should apply.  Transforms\n       convert between SQL types and language-specific data types;\n       see \u003ca class=\"xref\" href=\"/docs/18/sql-createtransform.html\" title=\"CREATE TRANSFORM\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE TRANSFORM\u003c/span\u003e\u003c/a\u003e.  Procedural language\n       implementations usually have hardcoded knowledge of the built-in types,\n       so those don't need to be listed here.  If a procedural language\n       implementation does not know how to handle a type and no transform is\n       supplied, it will fall back to a default behavior for converting data\n       types, but this depends on the implementation.\n      \u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003e[\u003cspan class=\"optional\"\u003eEXTERNAL\u003c/span\u003e] SECURITY INVOKER\u003c/code\u003e\u003cbr/\u003e\u003c/span\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003e[\u003cspan class=\"optional\"\u003eEXTERNAL\u003c/span\u003e] SECURITY DEFINER\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\u003ccode class=\"literal\"\u003eSECURITY INVOKER\u003c/code\u003e indicates that the procedure\n      is to be executed with the privileges of the user that calls it.\n      That is the default.  \u003ccode class=\"literal\"\u003eSECURITY DEFINER\u003c/code\u003e\n      specifies that the procedure is to be executed with the\n      privileges of the user that owns it.\n     \u003c/p\u003e\u003cp\u003e\n      The key word \u003ccode class=\"literal\"\u003eEXTERNAL\u003c/code\u003e is allowed for SQL\n      conformance, but it is optional since, unlike in SQL, this feature\n      applies to all procedures not only external ones.\n     \u003c/p\u003e\u003cp\u003e\n      A \u003ccode class=\"literal\"\u003eSECURITY DEFINER\u003c/code\u003e procedure cannot execute\n      transaction control statements (for example, \u003ccode class=\"command\"\u003eCOMMIT\u003c/code\u003e\n      and \u003ccode class=\"command\"\u003eROLLBACK\u003c/code\u003e, depending on the language).\n     \u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003econfiguration_parameter\u003c/code\u003e\u003c/em\u003e\u003cbr/\u003e\u003c/span\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003evalue\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n       The \u003ccode class=\"literal\"\u003eSET\u003c/code\u003e clause causes the specified configuration\n       parameter to be set to the specified value when the procedure is\n       entered, and then restored to its prior value when the procedure exits.\n       \u003ccode class=\"literal\"\u003eSET FROM CURRENT\u003c/code\u003e saves the value of the parameter that\n       is current when \u003ccode class=\"command\"\u003eCREATE PROCEDURE\u003c/code\u003e is executed as the value\n       to be applied when the procedure is entered.\n      \u003c/p\u003e\u003cp\u003e\n       If a \u003ccode class=\"literal\"\u003eSET\u003c/code\u003e clause is attached to a procedure, then\n       the effects of a \u003ccode class=\"command\"\u003eSET LOCAL\u003c/code\u003e command executed inside the\n       procedure for the same variable are restricted to the procedure: the\n       configuration parameter's prior value is still restored at procedure exit.\n       However, an ordinary\n       \u003ccode class=\"command\"\u003eSET\u003c/code\u003e command (without \u003ccode class=\"literal\"\u003eLOCAL\u003c/code\u003e) overrides the\n       \u003ccode class=\"literal\"\u003eSET\u003c/code\u003e clause, much as it would do for a previous \u003ccode class=\"command\"\u003eSET\n       LOCAL\u003c/code\u003e command: the effects of such a command will persist after\n       procedure exit, unless the current transaction is rolled back.\n      \u003c/p\u003e\u003cp\u003e\n       If a \u003ccode class=\"literal\"\u003eSET\u003c/code\u003e clause is attached to a procedure, then\n       that procedure cannot execute transaction control statements (for\n       example, \u003ccode class=\"command\"\u003eCOMMIT\u003c/code\u003e and \u003ccode class=\"command\"\u003eROLLBACK\u003c/code\u003e,\n       depending on the language).\n      \u003c/p\u003e\u003cp\u003e\n       See \u003ca class=\"xref\" href=\"/docs/18/sql-set.html\" title=\"SET\"\u003e\u003cspan class=\"refentrytitle\"\u003eSET\u003c/span\u003e\u003c/a\u003e and\n       \u003ca class=\"xref\" href=\"/docs/18/runtime-config.html\" title=\"Chapter 19. Server Configuration\"\u003eChapter 19\u003c/a\u003e\n       for more information about allowed parameter names and values.\n      \u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003edefinition\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n       A string constant defining the procedure; the meaning depends on the\n       language.  It can be an internal procedure name, the path to an\n       object file, an SQL command, or text in a procedural language.\n      \u003c/p\u003e\u003cp\u003e\n       It is often helpful to use dollar quoting (see \u003ca class=\"xref\" href=\"/docs/18/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING\" title=\"4.1.2.4. Dollar-Quoted String Constants\"\u003eSection 4.1.2.4\u003c/a\u003e) to write the procedure definition\n       string, rather than the normal single quote syntax.  Without dollar\n       quoting, any single quotes or backslashes in the procedure definition must\n       be escaped by doubling them.\n      \u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eobj_file\u003c/code\u003e\u003c/em\u003e, \u003cem class=\"replaceable\"\u003e\u003ccode\u003elink_symbol\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n       This form of the \u003ccode class=\"literal\"\u003eAS\u003c/code\u003e clause is used for\n       dynamically loadable C language procedures when the procedure name\n       in the C language source code is not the same as the name of\n       the SQL procedure. The string \u003cem class=\"replaceable\"\u003e\u003ccode\u003eobj_file\u003c/code\u003e\u003c/em\u003e is the name of the shared\n       library file containing the compiled C procedure, and is interpreted\n       as for the \u003ca class=\"link\" href=\"/docs/18/sql-load.html\" title=\"LOAD\"\u003e\u003ccode class=\"command\"\u003eLOAD\u003c/code\u003e\u003c/a\u003e command.  The string\n       \u003cem class=\"replaceable\"\u003e\u003ccode\u003elink_symbol\u003c/code\u003e\u003c/em\u003e is the\n       procedure's link symbol, that is, the name of the procedure in the C\n       language source code.  If the link symbol is omitted, it is assumed\n       to be the same as the name of the SQL procedure being defined.\n      \u003c/p\u003e\u003cp\u003e\n       When repeated \u003ccode class=\"command\"\u003eCREATE PROCEDURE\u003c/code\u003e calls refer to\n       the same object file, the file is only loaded once per session.\n       To unload and\n       reload the file (perhaps during development), start a new session.\n      \u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003esql_body\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e\n       The body of a \u003ccode class=\"literal\"\u003eLANGUAGE SQL\u003c/code\u003e procedure.  This should\n       be a block\n\u003c/p\u003e\u003cpre class=\"programlisting\"\u003e\nBEGIN ATOMIC\n  \u003cem class=\"replaceable\"\u003e\u003ccode\u003estatement\u003c/code\u003e\u003c/em\u003e;\n  \u003cem class=\"replaceable\"\u003e\u003ccode\u003estatement\u003c/code\u003e\u003c/em\u003e;\n  ...\n  \u003cem class=\"replaceable\"\u003e\u003ccode\u003estatement\u003c/code\u003e\u003c/em\u003e;\nEND\n\u003c/pre\u003e\u003cp\u003e\n\u003c/p\u003e\u003cp\u003e\n       This is similar to writing the text of the procedure body as a string\n       constant (see \u003cem class=\"replaceable\"\u003e\u003ccode\u003edefinition\u003c/code\u003e\u003c/em\u003e above), but there\n       are some differences: This form only works for \u003ccode class=\"literal\"\u003eLANGUAGE\n       SQL\u003c/code\u003e, the string constant form works for all languages.  This\n       form is parsed at procedure definition time, the string constant form is\n       parsed at execution time; therefore this form cannot support\n       polymorphic argument types and other constructs that are not resolvable\n       at procedure definition time.  This form tracks dependencies between the\n       procedure and objects used in the procedure body, so \u003ccode class=\"literal\"\u003eDROP\n       ... CASCADE\u003c/code\u003e will work correctly, whereas the form using\n       string literals may leave dangling procedures.  Finally, this form is\n       more compatible with the SQL standard and other SQL implementations.\n      \u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e","key":"parameters","title":"Parameters"},{"html":"\u003cp\u003e\n   See \u003ca class=\"xref\" href=\"/docs/18/sql-createfunction.html\" title=\"CREATE FUNCTION\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE FUNCTION\u003c/span\u003e\u003c/a\u003e for more details on function\n   creation that also apply to procedures.\n  \u003c/p\u003e\u003cp\u003e\n   Use \u003ca class=\"xref\" href=\"/docs/18/sql-call.html\" title=\"CALL\"\u003e\u003cspan class=\"refentrytitle\"\u003eCALL\u003c/span\u003e\u003c/a\u003e to execute a procedure.\n  \u003c/p\u003e","key":"notes","title":"Notes"},{"html":"\u003cp\u003e\n\u003c/p\u003e\u003cpre class=\"programlisting\"\u003e\nCREATE PROCEDURE insert_data(a integer, b integer)\nLANGUAGE SQL\nAS $$\nINSERT INTO tbl VALUES (a);\nINSERT INTO tbl VALUES (b);\n$$;\n\u003c/pre\u003e\u003cp\u003e\n   or\n\u003c/p\u003e\u003cpre class=\"programlisting\"\u003e\nCREATE PROCEDURE insert_data(a integer, b integer)\nLANGUAGE SQL\nBEGIN ATOMIC\n  INSERT INTO tbl VALUES (a);\n  INSERT INTO tbl VALUES (b);\nEND;\n\u003c/pre\u003e\u003cp\u003e\n   and call like this:\n\u003c/p\u003e\u003cpre class=\"programlisting\"\u003e\nCALL insert_data(1, 2);\n\u003c/pre\u003e","key":"examples","title":"Examples"},{"html":"\u003cp\u003e\n   A \u003ccode class=\"command\"\u003eCREATE PROCEDURE\u003c/code\u003e command is defined in the SQL\n   standard.  The \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e implementation can be\n   used in a compatible way but has many extensions.  For details see also\n   \u003ca class=\"xref\" href=\"/docs/18/sql-createfunction.html\" title=\"CREATE FUNCTION\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE FUNCTION\u003c/span\u003e\u003c/a\u003e.\n  \u003c/p\u003e","key":"compatibility","title":"Compatibility"},{"html":"\u003cspan class=\"simplelist\"\u003e\u003ca class=\"xref\" href=\"/docs/18/sql-alterprocedure.html\" title=\"ALTER PROCEDURE\"\u003e\u003cspan class=\"refentrytitle\"\u003eALTER PROCEDURE\u003c/span\u003e\u003c/a\u003e, \u003ca class=\"xref\" href=\"/docs/18/sql-dropprocedure.html\" title=\"DROP PROCEDURE\"\u003e\u003cspan class=\"refentrytitle\"\u003eDROP PROCEDURE\u003c/span\u003e\u003c/a\u003e, \u003ca class=\"xref\" href=\"/docs/18/sql-call.html\" title=\"CALL\"\u003e\u003cspan class=\"refentrytitle\"\u003eCALL\u003c/span\u003e\u003c/a\u003e, \u003ca class=\"xref\" href=\"/docs/18/sql-createfunction.html\" title=\"CREATE FUNCTION\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE FUNCTION\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e","key":"see_also","title":"See Also"}],"sections_same_as":"","synopsis_html":"CREATE [ OR REPLACE ] PROCEDURE\n    \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e ( [ [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eargmode\u003c/code\u003e\u003c/em\u003e ] [ \u003cem class=\"replaceable\"\u003e\u003ccode\u003eargname\u003c/code\u003e\u003c/em\u003e ] \u003cem class=\"replaceable\"\u003e\u003ccode\u003eargtype\u003c/code\u003e\u003c/em\u003e [ { DEFAULT | = } \u003cem class=\"replaceable\"\u003e\u003ccode\u003edefault_expr\u003c/code\u003e\u003c/em\u003e ] [, ...] ] )\n  { LANGUAGE \u003cem class=\"replaceable\"\u003e\u003ccode\u003elang_name\u003c/code\u003e\u003c/em\u003e\n    | TRANSFORM { FOR TYPE \u003cem class=\"replaceable\"\u003e\u003ccode\u003etype_name\u003c/code\u003e\u003c/em\u003e } [, ... ]\n    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER\n    | SET \u003cem class=\"replaceable\"\u003e\u003ccode\u003econfiguration_parameter\u003c/code\u003e\u003c/em\u003e { TO \u003cem class=\"replaceable\"\u003e\u003ccode\u003evalue\u003c/code\u003e\u003c/em\u003e | = \u003cem class=\"replaceable\"\u003e\u003ccode\u003evalue\u003c/code\u003e\u003c/em\u003e | FROM CURRENT }\n    | AS '\u003cem class=\"replaceable\"\u003e\u003ccode\u003edefinition\u003c/code\u003e\u003c/em\u003e'\n    | AS '\u003cem class=\"replaceable\"\u003e\u003ccode\u003eobj_file\u003c/code\u003e\u003c/em\u003e', '\u003cem class=\"replaceable\"\u003e\u003ccode\u003elink_symbol\u003c/code\u003e\u003c/em\u003e'\n    | \u003cem class=\"replaceable\"\u003e\u003ccode\u003esql_body\u003c/code\u003e\u003c/em\u003e\n  } ...","synopsis_text":"CREATE [ OR REPLACE ] PROCEDURE\nname ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )\n{ LANGUAGE lang_name\n| TRANSFORM { FOR TYPE type_name } [, ... ]\n| [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER\n| SET configuration_parameter { TO value | = value | FROM CURRENT }\n| AS 'definition'\n| AS 'obj_file', 'link_symbol'\n| sql_body\n} ..."}},"RequestedLocale":"en","Fallback":false,"Versions":["11","12","13","14","15","16","17","18","19","20"],"Locales":["en","zh-Hans"],"Signatures":null,"Spellings":null,"SQLState":null,"Evidence":null}