Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages

  
 
  

home | help
CREATE PROCEDURE(7)	 PostgreSQL 12.4 Documentation	   CREATE PROCEDURE(7)

NAME
       CREATE_PROCEDURE	- define a new procedure

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'
	 } ...

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 the	USAGE
       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 called overloading).

       To replace the current definition of an existing	procedure, use CREATE
       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).

       When CREATE 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 have USAGE privilege on the
       argument	types.

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

       argmode
	   The mode of an argument: IN,	INOUT, or VARIADIC. If omitted,	the
	   default is IN. (OUT arguments are currently not supported for
	   procedures. Use INOUT instead.)

       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 as cstring. 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 writing
	   table_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 be sql, c, internal, or the name	of a user-defined procedural
	   language, e.g.  plpgsql. Enclosing the name in single quotes	is
	   deprecated and requires matching case.

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

	   A SECURITY DEFINER procedure	cannot execute transaction control
	   statements (for example, COMMIT and ROLLBACK, depending on the
	   language).

       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.

	   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 is 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.	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	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(7)
	   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 is	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. To	unload and reload the
	   file	(perhaps during	development), start a new session.

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

       Use CALL(7) to execute a	procedure.

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

	   CALL	insert_data(1, 2);

COMPATIBILITY
       A CREATE	PROCEDURE command is defined in	the SQL	standard. The
       PostgreSQL version is similar but not fully compatible. For details see
       also CREATE FUNCTION (CREATE_FUNCTION(7)).

SEE ALSO
       ALTER PROCEDURE (ALTER_PROCEDURE(7)), DROP PROCEDURE
       (DROP_PROCEDURE(7)), CALL(7), CREATE FUNCTION (CREATE_FUNCTION(7))

PostgreSQL 12.4			     2020		   CREATE PROCEDURE(7)

NAME | SYNOPSIS | DESCRIPTION | PARAMETERS | NOTES | EXAMPLES | COMPATIBILITY | SEE ALSO

Want to link to this manual page? Use this URL:
<https://www.freebsd.org/cgi/man.cgi?query=CREATE_PROCEDURE&sektion=7&manpath=FreeBSD+12.2-RELEASE+and+Ports>

home | help