Movatterモバイル変換

CREATE PROCEDURE
Prev	Up	SQL Commands	Home	Next

CREATE PROCEDURE

CREATE PROCEDURE — define a new procedure

Synopsis

CREATE [ OR REPLACE ] PROCEDUREname ( [ [argmode ] [argname ]argtype [ { DEFAULT | = }default_expr ] [, ...] ] )  { LANGUAGElang_name    | TRANSFORM { FOR TYPEtype_name } [, ... ]    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER    | SETconfiguration_parameter { TOvalue | =value | FROM CURRENT }    | AS 'definition'    | AS 'obj_file', 'link_symbol'    |sql_body  } ...

Description

CREATE PROCEDURE defines a new procedure.CREATE OR REPLACE PROCEDURE will either create a new procedure, or replace an existing definition. To be able to define a procedure, the user must have theUSAGE privilege on the language.

If a schema name is included, then the procedure is created in the specified schema. Otherwise it is created in the current schema. The name of the new procedure must not match any existing procedure or function with the same input argument types in the same schema. However, procedures and functions of different argument types can share a name (this is calledoverloading).

To replace the current definition of an existing procedure, useCREATE OR REPLACE PROCEDURE. 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).

WhenCREATE OR REPLACE PROCEDURE is used to replace an existing procedure, the ownership and permissions of the procedure do not change. All other procedure properties are assigned the values specified or implied in the command. You must own the procedure to replace it (this includes being a member of the owning role).

The user that creates the procedure becomes the owner of the procedure.

To be able to create a procedure, you must haveUSAGE privilege on the argument types.

Refer toSection 36.4 for further information on writing procedures.

Parameters

name

The name (optionally schema-qualified) of the procedure to create.

argmode

The mode of an argument:IN,OUT,INOUT, orVARIADIC. If omitted, the default isIN.

argname

The name of an argument.

argtype

The data type(s) of the procedure's arguments (optionally schema-qualified), if any. The argument types can be base, composite, or domain types, or can reference the type of a table column.

Depending on the implementation language it might also be allowed to specify“pseudo-types” such ascstring. Pseudo-types indicate that the actual argument type is either incompletely specified, or outside the set of ordinary SQL data types.

The type of a column is referenced by writingtable_name.column_name%TYPE. Using this feature can sometimes help make a procedure independent of changes to the definition of a table.

default_expr

An expression to be used as default value if the parameter is not specified. The expression has to be coercible to the argument type of the parameter. All input parameters following a parameter with a default value must have default values as well.

lang_name

The name of the language that the procedure is implemented in. It can besql,c,internal, or the name of a user-defined procedural language, e.g.,plpgsql. The default issql ifsql_body is specified. Enclosing the name in single quotes is deprecated and requires matching case.

TRANSFORM { FOR TYPEtype_name } [, ... ] }

Lists which transforms a call to the procedure should apply. Transforms convert between SQL types and language-specific data types; seeCREATE TRANSFORM. 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 does not know how to handle a type and no transform is supplied, it will fall back to a default behavior for converting data types, but this depends on the implementation.

[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 wordEXTERNAL is allowed for SQL conformance, but it is optional since, unlike in SQL, this feature applies to all procedures not only external ones.

ASECURITY DEFINER procedure cannot execute transaction control statements (for example,COMMIT andROLLBACK, depending on the language).

configuration_parameter value

TheSET 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 whenCREATE PROCEDURE is executed as the value to be applied when the procedure is entered.

If aSET clause is attached to a procedure, then the effects of aSET 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 ordinarySET command (withoutLOCAL) overrides theSET clause, much as it would do for a previousSET LOCAL command: the effects of such a command will persist after procedure exit, unless the current transaction is rolled back.

If aSET clause is attached to a procedure, then that procedure cannot execute transaction control statements (for example,COMMIT andROLLBACK, depending on the language).

SeeSET andChapter 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 is often helpful to use dollar quoting (seeSection 4.1.2.4) to write the procedure definition string, rather than the normal single quote syntax. Without dollar quoting, any single quotes or backslashes in the procedure definition must be escaped by doubling them.

obj_file,link_symbol

This form of theAS 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 stringobj_file is the name of the shared library file containing the compiled C procedure, and is interpreted as for theLOAD command. The stringlink_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 is assumed to be the same as the name of the SQL procedure being defined.

When repeatedCREATE PROCEDURE calls refer to the same object file, the file is only loaded once per session. To unload and reload the file (perhaps during development), start a new session.

sql_body

The body of aLANGUAGE SQL procedure. This should be a block

BEGIN ATOMICstatement;statement;  ...statement;END

This is similar to writing the text of the procedure body as a string constant (seedefinition above), but there are some differences: This form only works forLANGUAGE 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, soDROP ... 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.

Notes

SeeCREATE FUNCTION for more details on function creation that also apply to procedures.

UseCALL to execute a procedure.

Examples

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

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

and call like this:

CALL insert_data(1, 2);

Compatibility

ACREATE PROCEDURE command is defined in the SQL standard. ThePostgreSQL implementation can be used in a compatible way but has many extensions. For details see alsoCREATE FUNCTION.