CREATE_PROCEDURE(7pg17)

📦 version 17.11, in opensuse-leap 16.0

💅 CREATE_PROCEDURE — okay so you wanna make a whole new procedure

This is the PostgreSQL 17.11 docs, bestie — specifically the CREATE PROCEDURE(7) page, and I'm gonna walk you through it like we're texting at 2am. Grab a strawberry drink 🍓, this is a long one but it eats.

✨ NAME ✨

CREATE_PROCEDURE — literally the command that defines a brand-new procedure. That's it. That's the whole vibe. 👑

🦋 SYNOPSIS 🦋

CREATE [ OR REPLACE ] PROCEDURE
    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
  { LANGUAGE lang_name
    | TRANSFORM { FOR TYPE type_name } [, ... ]
    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    | AS 'obj_file', 'link_symbol'
    | sql_body
  } ...

Look at all those braces, she's flexible 💅 — you pick your clauses and stack as many as you need (that's what the trailing ... is telling you).

🌸 DESCRIPTION 🌸

So CREATE PROCEDURE defines a new procedure. And CREATE OR REPLACE PROCEDURE? Iconic behaviour — it either creates a fresh new procedure or replaces an existing definition. Same command, two moods.

Before you can even define a procedure, you need the USAGE privilege on the language. No privilege, no procedure. Periodt.

If you throw a schema name in there, the procedure gets created in that schema. If you don't? It lands in the current schema, ofc. And heads up — the name of your new procedure must not match any existing procedure or function with the same input argument types in the same schema. But procedures and functions with different argument types can totally share a name — that's called overloading, and it's giving main character energy. 🌈

Wanna replace the current definition of something that already exists? Use CREATE OR REPLACE PROCEDURE. But ngl, you cannot change the name or the argument types of a procedure this way. If you try, you're actually just creating a new, distinct procedure. Surprise! 🙈

When CREATE OR REPLACE PROCEDURE replaces an existing procedure, the ownership and permissions of the procedure do not change. Everything else — all the other procedure properties — gets assigned the values specified or implied in the command. And you must own the procedure to replace it (and yes, that includes being a member of the owning role).

The user that creates the procedure becomes the owner of the procedure. You made it, you own it, slay. 👑

Also: to be able to create a procedure you must have USAGE privilege on the argument types. Don't forget that part!

For further info on writing procedures, refer to Section 36.4. 📖

🎀 PARAMETERS 🎀

  • name — the name (optionally schema-qualified) of the procedure you're creating. Duh, but fr fr.
  • argmode — the mode of an argument: IN, OUT, INOUT, or VARIADIC. If you omit it, the default is IN.
  • argname — the name of an argument. Simple as.
  • argtype — the data type(s) of the procedure's arguments (optionally schema-qualified), if any. These types can be base, composite, or domain types, or they can reference the type of a table column.

Depending on the implementation language, it might also be allowed to specify "pseudo-types" such as cstring. Pseudo-types are saying "the actual argument type is either incompletely specified, or outside the set of ordinary SQL data types." Very mysterious of them.

You can reference a column's type by writing table_name.column_name%TYPE. Using this feature can sometimes help make a procedure independent of changes to the definition of a table — lowkey a glow-up for maintainability. ✨

  • default_expr — an expression to be used as the default value if the parameter is not specified. The expression has to be coercible to the argument type of the parameter. And here's the catch: all input parameters following a parameter with a default value must have default values as well. No skipping, bestie. 🚨
  • lang_name — the name of the language the procedure is implemented in. It can be sql, c, internal, or the name of a user-defined procedural language, e.g., plpgsql. The default is sql if sql_body is specified. Wrapping the name in single quotes is deprecated and requires matching case — so like, don't? 🥴
  • TRANSFORM { FOR TYPE type_name } [, ... ] } — lists which transforms a call to the procedure should apply. Transforms convert between SQL types and language-specific data types; see CREATE TRANSFORM (CREATE_TRANSFORM(7)). Procedural language implementations usually have hardcoded knowledge of the built-in types, so those don't need to be listed here. If a procedural language implementation doesn't know how to handle a type and no transform is supplied, it will fall back to a default behavior for converting data types — but that depends on the implementation, so no promises. 🫶
  • [EXTERNAL] SECURITY INVOKER / [EXTERNAL] SECURITY DEFINER — SECURITY INVOKER indicates that the procedure is to be executed with the privileges of the user that calls it. That is the default. SECURITY DEFINER specifies that the procedure is to be executed with the privileges of the user that owns it.

The key word EXTERNAL is allowed for SQL conformance, but it's optional — because unlike in SQL, this feature applies to all procedures, not only external ones.

🚨 Real talk: a SECURITY DEFINER procedure cannot execute transaction control statements (for example, COMMIT and ROLLBACK, depending on the language). Don't try it.
  • configuration_parameter / value — the SET clause causes the specified configuration parameter to be set to the specified value when the procedure is entered, and then restored to its prior value when the procedure exits. SET FROM CURRENT saves the value of the parameter that is current when CREATE PROCEDURE is executed as the value to be applied when the procedure is entered.

If a SET clause is attached to a procedure, then the effects of a SET LOCAL command executed inside the procedure for the same variable are restricted to the procedure: the configuration parameter's prior value is still restored at procedure exit. However, an ordinary SET command (without LOCAL) overrides the SET clause, much as it would do for a previous SET LOCAL command: the effects of such a command will persist after procedure exit, unless the current transaction is rolled back. 😭

Also a hard limit: if a SET clause is attached to a procedure, then that procedure cannot execute transaction control statements (for example, COMMIT and ROLLBACK, depending on the language).

See SET(7) and Chapter 19 for more information about allowed parameter names and values.

  • definition — a string constant defining the procedure; the meaning depends on the language. It can be an internal procedure name, the path to an object file, an SQL command, or text in a procedural language.

It's often helpful to use dollar quoting (see Section 4.1.2.4) to write the procedure definition string, rather than the normal single quote syntax. Because without dollar quoting, any single quotes or backslashes in the procedure definition must be escaped by doubling them, and honestly? exhausting. 💀

  • obj_file, link_symbol — this form of the AS clause is used for dynamically loadable C language procedures when the procedure name in the C language source code is not the same as the name of the SQL procedure. The string obj_file is the name of the shared library file containing the compiled C procedure, and is interpreted as for the LOAD command. The string link_symbol is the procedure's link symbol — that is, the name of the procedure in the C language source code. If the link symbol is omitted, it's assumed to be the same as the name of the SQL procedure being defined.

When repeated CREATE PROCEDURE calls refer to the same object file, the file is only loaded once per session. Wanna unload and reload the file (maybe you're in your dev era)? Start a new session. 🌱

  • sql_body — the body of a LANGUAGE SQL procedure. This should be a block:
BEGIN ATOMIC
  statement;
  statement;
  ...
  statement;
END

This is similar to writing the text of the procedure body as a string constant (see definition above), but there are some differences, and they matter:

  • This form only works for LANGUAGE SQL; the string constant form works for all languages.
  • This form is parsed at procedure definition time; the string constant form is parsed at execution time. Therefore this form cannot support polymorphic argument types and other constructs that are not resolvable at procedure definition time.
  • This form tracks dependencies between the procedure and objects used in the procedure body, so DROP ... CASCADE will work correctly — whereas the form using string literals may leave dangling procedures. 😬
  • Finally, this form is more compatible with the SQL standard and other SQL implementations. Fancy.

💜 NOTES 💜

See CREATE FUNCTION (CREATE_FUNCTION(7)) for more details on function creation that also apply to procedures. Sibling energy. 🫶

And use CALL(7) to execute a procedure — that's the whole point, bestie.

🔥 EXAMPLES 🔥

Okay so picture this, you're inserting some data:

CREATE PROCEDURE insert_data(a integer, b integer)
LANGUAGE SQL
AS $$
INSERT INTO tbl VALUES (a);
INSERT INTO tbl VALUES (b);
$$;

or, alternatively, the BEGIN ATOMIC way:

CREATE PROCEDURE insert_data(a integer, b integer)
LANGUAGE SQL
BEGIN ATOMIC
  INSERT INTO tbl VALUES (a);
  INSERT INTO tbl VALUES (b);
END;

and then call it like this:

CALL insert_data(1, 2);

Ate. No crumbs. 🔥

👑 COMPATIBILITY 👑

A CREATE PROCEDURE command is defined in the SQL standard. The PostgreSQL implementation can be used in a compatible way, but it has many extensions — she's built different. For details see also CREATE FUNCTION (CREATE_FUNCTION(7)).

🌈 SEE ALSO 🌈

And that's the whole page, bestie 🌸 — brought to you by the PostgreSQL 17.11 documentation, dated 2026, page CREATE PROCEDURE(7). Now go forth and write procedures, you absolute icon 💖✨🎀

📦 where this page comes from