{
    "mode": "man",
    "parameter": "CREATE_PROCEDURE",
    "section": "7",
    "url": "https://www.chedong.com/phpMan.php/man/CREATE_PROCEDURE/7/json",
    "generated": "2026-07-05T11:56:41Z",
    "synopsis": "CREATE [ OR REPLACE ] PROCEDURE\nname ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } defaultexpr ] [, ...] ] )\n{ LANGUAGE langname\n| TRANSFORM { FOR TYPE typename } [, ... ]\n| [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER\n| SET configurationparameter { TO value | = value | FROM CURRENT }\n| AS 'definition'\n| AS 'objfile', 'linksymbol'\n| sqlbody\n} ...",
    "sections": {
        "NAME": {
            "content": "CREATEPROCEDURE - define a new procedure\n",
            "subsections": []
        },
        "SYNOPSIS": {
            "content": "CREATE [ OR REPLACE ] PROCEDURE\nname ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } defaultexpr ] [, ...] ] )\n{ LANGUAGE langname\n| TRANSFORM { FOR TYPE typename } [, ... ]\n| [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER\n| SET configurationparameter { TO value | = value | FROM CURRENT }\n| AS 'definition'\n| AS 'objfile', 'linksymbol'\n| sqlbody\n} ...\n",
            "subsections": []
        },
        "DESCRIPTION": {
            "content": "CREATE PROCEDURE defines a new procedure.  CREATE OR REPLACE PROCEDURE will either create a\nnew procedure, or replace an existing definition. To be able to define a procedure, the user\nmust have the USAGE privilege on the language.\n\nIf a schema name is included, then the procedure is created in the specified schema.\nOtherwise it is created in the current schema. The name of the new procedure must not match\nany existing procedure or function with the same input argument types in the same schema.\nHowever, procedures and functions of different argument types can share a name (this is\ncalled overloading).\n\nTo replace the current definition of an existing procedure, use CREATE OR REPLACE PROCEDURE.\nIt is not possible to change the name or argument types of a procedure this way (if you\ntried, you would actually be creating a new, distinct procedure).\n\nWhen CREATE OR REPLACE PROCEDURE is used to replace an existing procedure, the ownership and\npermissions of the procedure do not change. All other procedure properties are assigned the\nvalues specified or implied in the command. You must own the procedure to replace it (this\nincludes being a member of the owning role).\n\nThe user that creates the procedure becomes the owner of the procedure.\n\nTo be able to create a procedure, you must have USAGE privilege on the argument types.\n\nRefer to Section 38.4 for further information on writing procedures.\n",
            "subsections": []
        },
        "PARAMETERS": {
            "content": "name\nThe name (optionally schema-qualified) of the procedure to create.\n\nargmode\nThe mode of an argument: IN, OUT, INOUT, or VARIADIC. If omitted, the default is IN.\n\nargname\nThe name of an argument.\n\nargtype\nThe data type(s) of the procedure's arguments (optionally schema-qualified), if any. The\nargument types can be base, composite, or domain types, or can reference the type of a\ntable column.\n\nDepending on the implementation language it might also be allowed to specify\n“pseudo-types” such as cstring. Pseudo-types indicate that the actual argument type is\neither incompletely specified, or outside the set of ordinary SQL data types.\n\nThe type of a column is referenced by writing tablename.columnname%TYPE. Using this\nfeature can sometimes help make a procedure independent of changes to the definition of a\ntable.\n\ndefaultexpr\nAn expression to be used as default value if the parameter is not specified. The\nexpression has to be coercible to the argument type of the parameter. All input\nparameters following a parameter with a default value must have default values as well.\n\nlangname\nThe name of the language that the procedure is implemented in. It can be sql, c,\ninternal, or the name of a user-defined procedural language, e.g., plpgsql. The default\nis sql if sqlbody is specified. Enclosing the name in single quotes is deprecated and\nrequires matching case.\n\nTRANSFORM { FOR TYPE typename } [, ... ] }\nLists which transforms a call to the procedure should apply. Transforms convert between\nSQL types and language-specific data types; see CREATE TRANSFORM (CREATETRANSFORM(7)).\nProcedural language implementations usually have hardcoded knowledge of the built-in\ntypes, so those don't need to be listed here. If a procedural language implementation\ndoes not know how to handle a type and no transform is supplied, it will fall back to a\ndefault behavior for converting data types, but this depends on the implementation.\n\n[EXTERNAL] SECURITY INVOKER\n[EXTERNAL] SECURITY DEFINER\nSECURITY INVOKER indicates that the procedure is to be executed with the privileges of\nthe user that calls it. That is the default.  SECURITY DEFINER specifies that the\nprocedure is to be executed with the privileges of the user that owns it.\n\nThe key word EXTERNAL is allowed for SQL conformance, but it is optional since, unlike in\nSQL, this feature applies to all procedures not only external ones.\n\nA SECURITY DEFINER procedure cannot execute transaction control statements (for example,\nCOMMIT and ROLLBACK, depending on the language).\n\nconfigurationparameter\nvalue\nThe SET clause causes the specified configuration parameter to be set to the specified\nvalue when the procedure is entered, and then restored to its prior value when the\nprocedure exits.  SET FROM CURRENT saves the value of the parameter that is current when\nCREATE PROCEDURE is executed as the value to be applied when the procedure is entered.\n\nIf a SET clause is attached to a procedure, then the effects of a SET LOCAL command\nexecuted inside the procedure for the same variable are restricted to the procedure: the\nconfiguration parameter's prior value is still restored at procedure exit. However, an\nordinary SET command (without LOCAL) overrides the SET clause, much as it would do for a\nprevious SET LOCAL command: the effects of such a command will persist after procedure\nexit, unless the current transaction is rolled back.\n\nIf a SET clause is attached to a procedure, then that procedure cannot execute\ntransaction control statements (for example, COMMIT and ROLLBACK, depending on the\nlanguage).\n\nSee SET(7) and Chapter 20 for more information about allowed parameter names and values.\n\ndefinition\nA string constant defining the procedure; the meaning depends on the language. It can be\nan internal procedure name, the path to an object file, an SQL command, or text in a\nprocedural language.\n\nIt is often helpful to use dollar quoting (see Section 4.1.2.4) to write the procedure\ndefinition string, rather than the normal single quote syntax. Without dollar quoting,\nany single quotes or backslashes in the procedure definition must be escaped by doubling\nthem.\n\nobjfile, linksymbol\nThis form of the AS clause is used for dynamically loadable C language procedures when\nthe procedure name in the C language source code is not the same as the name of the SQL\nprocedure. The string objfile is the name of the shared library file containing the\ncompiled C procedure, and is interpreted as for the LOAD command. The string linksymbol\nis the procedure's link symbol, that is, the name of the procedure in the C language\nsource code. If the link symbol is omitted, it is assumed to be the same as the name of\nthe SQL procedure being defined.\n\nWhen repeated CREATE PROCEDURE calls refer to the same object file, the file is only\nloaded once per session. To unload and reload the file (perhaps during development),\nstart a new session.\n\nsqlbody\nThe body of a LANGUAGE SQL procedure. This should be a block\n\nBEGIN ATOMIC\nstatement;\nstatement;\n...\nstatement;\nEND\n\nThis is similar to writing the text of the procedure body as a string constant (see\ndefinition above), but there are some differences: This form only works for LANGUAGE SQL,\nthe string constant form works for all languages. This form is parsed at procedure\ndefinition time, the string constant form is parsed at execution time; therefore this\nform cannot support polymorphic argument types and other constructs that are not\nresolvable at procedure definition time. This form tracks dependencies between the\nprocedure and objects used in the procedure body, so DROP ... CASCADE will work\ncorrectly, whereas the form using string literals may leave dangling procedures. Finally,\nthis form is more compatible with the SQL standard and other SQL implementations.\n",
            "subsections": []
        },
        "NOTES": {
            "content": "See CREATE FUNCTION (CREATEFUNCTION(7)) for more details on function creation that also\napply to procedures.\n\nUse CALL(7) to execute a procedure.\n",
            "subsections": []
        },
        "EXAMPLES": {
            "content": "CREATE PROCEDURE insertdata(a integer, b integer)\nLANGUAGE SQL\nAS $$\nINSERT INTO tbl VALUES (a);\nINSERT INTO tbl VALUES (b);\n$$;\n\nor\n\nCREATE PROCEDURE insertdata(a integer, b integer)\nLANGUAGE SQL\nBEGIN ATOMIC\nINSERT INTO tbl VALUES (a);\nINSERT INTO tbl VALUES (b);\nEND;\n\nand call like this:\n\nCALL insertdata(1, 2);\n",
            "subsections": []
        },
        "COMPATIBILITY": {
            "content": "A CREATE PROCEDURE command is defined in the SQL standard. The PostgreSQL implementation can\nbe used in a compatible way but has many extensions. For details see also CREATE FUNCTION\n(CREATEFUNCTION(7)).\n",
            "subsections": []
        },
        "SEE ALSO": {
            "content": "ALTER PROCEDURE (ALTERPROCEDURE(7)), DROP PROCEDURE (DROPPROCEDURE(7)), CALL(7), CREATE\nFUNCTION (CREATEFUNCTION(7))\n\n\n\nPostgreSQL 14.23                                2026                             CREATE PROCEDURE(7)",
            "subsections": []
        }
    },
    "summary": "CREATEPROCEDURE - define a new procedure",
    "flags": [],
    "examples": [
        "CREATE PROCEDURE insertdata(a integer, b integer)",
        "LANGUAGE SQL",
        "AS $$",
        "INSERT INTO tbl VALUES (a);",
        "INSERT INTO tbl VALUES (b);",
        "$$;",
        "or",
        "CREATE PROCEDURE insertdata(a integer, b integer)",
        "LANGUAGE SQL",
        "BEGIN ATOMIC",
        "INSERT INTO tbl VALUES (a);",
        "INSERT INTO tbl VALUES (b);",
        "END;",
        "and call like this:",
        "CALL insertdata(1, 2);"
    ],
    "see_also": [
        {
            "name": "ALTERPROCEDURE",
            "section": "7",
            "url": "https://www.chedong.com/phpMan.php/man/ALTERPROCEDURE/7/json"
        },
        {
            "name": "DROPPROCEDURE",
            "section": "7",
            "url": "https://www.chedong.com/phpMan.php/man/DROPPROCEDURE/7/json"
        },
        {
            "name": "CALL",
            "section": "7",
            "url": "https://www.chedong.com/phpMan.php/man/CALL/7/json"
        },
        {
            "name": "CREATEFUNCTION",
            "section": "7",
            "url": "https://www.chedong.com/phpMan.php/man/CREATEFUNCTION/7/json"
        },
        {
            "name": "PROCEDURE",
            "section": "7",
            "url": "https://www.chedong.com/phpMan.php/man/PROCEDURE/7/json"
        }
    ]
}