Enclosing the name in single quotes is deprecated and requires matching case. The default is sql if sql_body is specified. It can be sql, c, internal, or the name of a user-defined procedural language, e.g., plpgsql. The name of the language that the procedure is implemented in. All input parameters following a parameter with a default value must have default values as well. The expression has to be coercible to the argument type of the parameter. default_exprĪn expression to be used as default value if the parameter is not specified. Using this feature can sometimes help make a procedure independent of changes to the definition of a table. The type of a column is referenced by writing table_name. Pseudo-types indicate that the actual argument type is either incompletely specified, or outside the set of ordinary SQL data types. The argument types can be base, composite, or domain types, or can reference the type of a table column.ĭepending on the implementation language it might also be allowed to specify “ pseudo-types” such as cstring. The data type(s) of the procedure's arguments (optionally schema-qualified), if any. The mode of an argument: IN, OUT, INOUT, or VARIADIC. The name (optionally schema-qualified) of the procedure to create. Refer to Section 38.4 for further information on writing procedures. To be able to create a procedure, you must have USAGE privilege on the argument types. The user that creates the procedure becomes the owner of the procedure. You must own the procedure to replace it (this includes being a member of the owning role). All other procedure properties are assigned the values specified or implied in the command. When CREATE OR REPLACE PROCEDURE is used to replace an existing procedure, the ownership and permissions of the procedure do not change. It is not possible to change the name or argument types of a procedure this way (if you tried, you would actually be creating a new, distinct procedure). To replace the current definition of an existing procedure, use CREATE OR REPLACE PROCEDURE. However, procedures and functions of different argument types can share a name (this is called overloading). The name of the new procedure must not match any existing procedure or function with the same input argument types in the same schema. Otherwise it is created in the current schema. If a schema name is included, then the procedure is created in the specified schema. To be able to define a procedure, the user must have the USAGE privilege on the language. CREATE OR REPLACE PROCEDURE will either create a new procedure, or replace an existing definition. CREATE PROCEDURE defines a new procedure.

Postgresql replace example