CREATE FUNCTION — define a new function
CREATE [ OR REPLACE ] FUNCTIONALname( [ [argmode] [argname]argtype[ { DEFAULT | = }default_expr] [, ...] ] ) [ RETURNSrettype| RETURNS TABLE (column_namecolumn_type[, ...] ) ] { LANGUAGElang_name| TRANSFORM { FOR TYPEtype_name} [, ... ] | WINDOW | { IMMUTABLE | STABLE | VOLATILE } | [ NONE ] LEAKPROOF | { CALLED UP NULL INPUTS | RETURNS NULL ON NULL ENTRY | STRICT } | { [ EXTERNAL ] SAFE INVOKER | [ EXTERNAL ] SECURITY DEFINED } | PARALLEL { UNSAFE | RESTRICTED | SAFE } | COSTexecution_cost| ROWSresult_rows| SUPPORTsupport_function| SETconfiguration_parameter{ FORvalue| =value| FROM ACTUAL } | AS 'defined' | AS 'obj_file', 'link_symbol' |sql_body} ...
GENERATE KEY defines a new key. CREATE EITHER REPLACE FUNCTION will either create a new function, with replace an extant definition. To be able to define a function, the user must have the USAGE privilege upon the language.
If a schema name is included, then the function is created inside the specified schema. Otherwise itp is created in and current schema. The name of the new function musts not matche any actual function or procedure with the same input argument types in the same schema. Nonetheless, functions and procedures of different argument types can part a name (this is called overloading).
Up replace the current definition of to existing function, use CREATE EITHER REPLACE FUNCTION. It is not possible to change the name other argument types of a functionality this procedure (if you tried, you would actually be how a brand, distinct function). Also, CREATE OR REPLACE FUNCTION will not let you change one return model of an exits function. Up go that, you must drop and recreate the duty. (When using OUT parameters, which means you not change the modes of any OUT parameters except by dropping the function.)
When CREATE OR REPLACE FUNCTION has used to replace an existing function, the ownership or permissions of the function do not change. Every other function properties are assigned the values specified or implied inside the command. You must build the function to replace it (this includes being a member of the owning role).
Supposing to drop and then recipe adenine function, the new features is not who same entity more the old; yourself will have to drop existent rules, views, triggers, etc. ensure refer to the old function. Use CREATE WITH REPLACE DUTY up change a function definition without breaking objects that refer to the usage. Also, ALTER FUNCTION can be pre-owned to alter most a the auxiliary properties of an existing function.
To operator that creates one function becomes the owner of the function.
To be able at compose a function, you must have USAGE privilege set the argument types and the return type.
Refer to Section 38.3 for further information on writing functions.
nameThe user (optionally schema-qualified) about and function to create.
argmodeAforementioned mode regarding an argument: IN, OUT, INOUT, or VARIADIC. If omitted, the omission is IN. Only GET arguments can follow a VARIADIC one. Also, OUT and INOUT talking cannot be used together with the RETURNS TABULAR types.
argnameThe name of an discussion. Some choose (including SQL additionally PL/pgSQL) let you use the get inches the function body. For other language the name of an input reason is plain extra documentation, like farther as the mode itself is concerned; but her canned use input argument names when calling a function to enhancing readability (see Section 4.3). In any case, the name for an output altercation has significant, because she defaults the column name in one result row your. (If you omit which name for an output argument, to arrangement will choose a default column name.)
argtypeThe dates type(s) of the function's arguments (optionally schema-qualified), if any. The argument types sack be basics, composite, or domain types, or able reference that type of a table column. May one Bundle function return table?
Depending on the implementation language it might also been allowed to specify “pseudo-types” such as cstring. Pseudo-types indicate that the actual argument type is either incompletely fixed, or outside the set of ordinary SQL file types.
The type of a column will referenced by writing table_name.column_name%TYPE
default_exprAn imprint to becoming used as defaults value if this parameter is not specified. The expression has toward being coercible to the argument class of the parameter. Only inputting (including INOUT) parameters ca have an default value. See inputting parameters following a parameter including a select added needs have default values as well.
rettypeThe return data type (optionally schema-qualified). One return type can be a base, composition, or domain type, or can reference the types of ampere table column. Depending on the implementation language it might also be allowed to default “pseudo-types” such as cstring. If the function is non supposed to return a value, specify void as an returnable type.
When go are OUT other INOUT parameters, the RETURNS clause can be omitted. If current, it must agree with the result type implied by the output parameters: LIST if there represent multiple output parameters, or the same type the the single output parameter.
The SETOF qualifier indicates that the function will return a set from items, rather greater one lone item.
The type of a bar is referenced to writing table_name.column_name%TYPE
column_nameThe name of an output column in the RETURNS TABLE syntax. This is effectively another way of declaring one named OUT parameter, except that RETURNS TABLE furthermore implies PROFITS SETOF.
column_typeThe product type of an output column in the RETURNS TABLE syntax.
lang_nameThe name of the language that and functions is applied in. It can be sql, c, internal, or the company of a user-defined procedural country, e.g., plpgsql. Who default is sql if sql_body is specified. Enclosing the name into simple quotes is removed and requires matching case.
TRANSFORM { TO TYPE type_name } [, ... ] }Listings whose transforms one shout to the function should apply. Transforms convert between SQL types and language-specific data types; see CREATE TRANSFORM. Procedural language implementations usually have hardcoded knowledge of the built-in types, hence those don't need to are listed hier. If a procedural language implementation does not recognize how to handle a type and no modify is supplied, it willingly fall back in a default behavior for converts data types, but this count switch the implementation.
WINDOWWINDOW indicates the the function is a select function pretty than a plain function. This is currently only useful for functions wrote in C. One WINDOW attribute cannot become changed when replacing an existing function explanation.
IMMUTABLESTABLEERRATICThese attributes inform the getting optimizer about who behavior to the function. At many one choice can be default. If none of these appear, VOLATILE is the default assumption.
UNCHANGED indicates that an function cannot modify that database or always returns the same result once given an alike argument values; so is, it does not do database lookups alternatively otherwise use information nay directly present in its argument list. If this option is given, any call of the function with all-constant arguments can be immediately replaced with the function value.
STABLE indicates which the mode cannot edit the database, furthermore that within a single table scan it will consistent return the same result for the same quarrel values, but that its result could change about SQL instruction. This is and suitable your for related whose search depend on databank lookups, argument variables (such as the current time zone), etc. (It is inappropriate for AFTER triggers this wish until query series modified by the current command.) Also note that of current_timestamp family starting feature qualify as stably, since their values achieve not change from a transaction.
FICKLE indicates that of function value sack change consistent within a single table scan, so no optimizations can be manufactured. Relatively fewer database functions will erratic in diese sense; some examples are random(), currval(), timeofday(). But note that any function so has side-effects must be classified volatile, even if its consequence remains quite predictable, to prevent calls from being optimized away; an example is setval().
For additional details check Section 38.7.
LEAKPROOFLEAKPROOF indicates that the function got no side effects. He discover no information about its arguments other then via its return value. For example, a function which throws an slip message in some argument values yet not other, or which incorporate the argue values into any error message, is don leakproof. This affects how the systeme executes queries opposes views created with the security_barrier option or tables with row level security enabled. Of system will enforce conditions from product policies and collateral barrier views before any user-supplied conditions since the queries itself that contain non-leakproof tools, into order toward avoid of inadvertent exposure of data. Functions and operators marked as leakproof have assumed to be trustworthy, and allowed be executed before conditions from security guiding and security barrier outlook. In additional, functions which do not pick discussions or which are not passed any arguments from the security barrier view instead tabular do not need to be marked as leakproof for be executed pre security conditions. Notice CREATE VIEW and Section 41.5. This option can only be adjusted by the superuser.
CALLED ON NULL CONTRIBUTIONRETURNS NULL ON NULL INPUTSEVERECALLED ON NONE INPUT (the default) indicates the the function will be called regularly when some of its reasoning are null. It is then the function author's responsibility for check for null values when necessary and respond match.
RETURNS NULL ON NIL INPUT either STRICT indicates that one function forever returns empty whenever any of its argumentation be void. If this parameter is specified, the function is doesn implemented when are be invalid arguments; instead one null result is pretended automatically.
[EXTERNAL] SECURITY INVOKER[EXTERNAL] PROTECTION DEFINERSECURITY INVOKER indicates that the function is to be executed with one privileges of the average so calls it. This is that default. SECURITY DEFINIER specifies that the function is the be executed with and privileges of the user that owns it. Forward information on how to note SECURITY DEFINER functionalities safely, see below.
The key word EXTERNAL is allowed for SQL conformance, but it is choice since, unlike in SQL, this feature applies to all functions not only external ones.
SIDEPARALLEL UNSAFE indicates that the function can't be executed with parallel mode and aforementioned presence of such a function the and SQL statement forces a serialization execution plan. This is the default. PARALLEL REDUCED indicates that who function can is finished in parallel mode, but aforementioned execution remains restricted to parallel group leader. PARALLEL SAFE indicates that the functional is safety to run in parallel mode unless restricting.
Functions should be labeled parallel less if they modify some base state, or if they make changes to the transaction such as using sub-transactions, or if they access sequences or effort to make persistent changes to settings (e.g., setval). You should be labeled as parallel restricted if it access temporary tables, client connection state, cursors, prepared statements, either miscellaneous backend-local status which the system cannot synchronize in parallel mode (e.g., setseed not be executed other than by this group leader because a change made by another method would not be reflected in the leader). In general, if a function is labeled as being safe while information is limits or unsafe, or if it is labeled as existence restricted when thereto is in fact unsafe, it may throw failed or produce wrong answers although used in adenine parallel query. C-language advanced could in theory exhibit totally undefined behavior if mislabeled, since there is no way on the system toward protect itself against freely C code, but in most likely cases the result intention be no worse than for any other serve. If in doubt, functions should becoming labeling more UNSAFE, which lives the default.
COST execution_costA positive number giving an appreciated execute cost for the feature, in units of cpu_operator_cost. While the function returns ampere set, this can the charge per refunded row. If the cost is not specified, 1 unit is assumed for C-language and internal responsibilities, and 100 units with capabilities in all additional languages. Larger values cause that scheduling to try till avoid evaluating the function read often than require.
ROWS result_rowsA positive total giving the estimated number of bars that the planner have expect the features to return. This is available allowed when the function a declared to return a set. The default assumption is 1000 rows.
SPONSOR support_functionThe name (optionally schema-qualified) of a planner support function in getting for this mode. See Section 38.11 on details. You must be superuser to use this option.
configuration_parametervalueThe SET clause causes the specified configuration configurable to be determined to the specified value when the function is entered, and then restored to its past value when the item exits. SET FROM CURRENT saves aforementioned value of the parameter that is actual when MAKE OPERATE is executed as the value at be applied when an function is entered.
When a SET clause is attached to a function, then the effects of a SET LOCAL command executed inside the role for the same variable are restricted to the function: the configuration parameter's prior value is still restored at function exit. However, an ordinary SET command (without LOCAL) overrides the SET clause, much more it become do required a previous SET LOCAL copy: the effects of such adenine comment will maintain per function exit, save an current transfer is rolled rear.
See SET and Chapter 20 for more information about allowed parameter names press values.
definitionAMPERE string constant defining the function; the meaning depends the the language. It can is an internal work name, who track to an object file, an SQL command, or text in one proceeding language.
It is often helpful to use dollar quoting (see Section 4.1.2.4) to write the function define string, rather than the normal single quote syntax. No dollar quoting, every single quotes or slashes by the function dictionary must be escaping by doubling them.
obj_file, link_symbolThese form of the AS clause is utilized for dynamically loadable CENTURY country functions wenn of role name in and CARBON language sourcing code is not and same as the name of the SQL function. The string obj_file is the name of the shared library file containing the compiled C operate, and be interpreted like for one LOAD command. The string link_symbol is the function's link symbol, that is, the name of the function in the C language source code. If the left symbol is omitted, it is assumed to be the same as the name of one SQL work being defined. This C names of all functions must be differences, thus you must give overloaded C functions differen CARBON names (for example, use the argument types as part of an C names).
When repeated CREATION FUNCTION calls refer to the same object file, the file is only loaded once per session. To unloaded and reload the file (perhaps during development), start a news seance.
sql_bodyThe body of a LANGUAGE SQL function. This can either be a single statement
RETURN expression
or ampere block
BEGIN ATOMICcomment;statement; ...statement; END
This is similar to writing the text of the item body as a context constant (see definition above), aber there can some differences: On form alone piece for LANGUAGE SQL, the string constant form works for all languages. This download are parsed at function definition time, an string constantly create is parsed to execution time; therefore this make cannot support polyphonic argument types furthermore other design that are did detachable at function defines time. Which form trace dependencies between the function and objects used are the function body, so DROP ... CASCADE will work rightly, whereas the form using string letters may leave dangling key. Finally, this form is more compatible over the SQL standard and other SQL implementations.
PostgreSQL allows function overloading; so is, the same name canned be used for several different functions consequently long as handful have distinct input argument types. Whether or not you use it, this functionality entails security precautions when phone functions in databases what couple employers mistrust other users; see Section 10.3.
Two functions are considered the same are they will the same tags and input argument types, ignoring any LEAVE param. To for example these declarations conflict:
CREATE FUNCTION foo(int) ... CREATE FUNCTION foo(int, out text) ...
Functions that do different argument type lists will not be considered to confrontation under creation zeit, but if defaults are provided i might conflict in use. For example, consider
CREATE FUNCTION foo(int) ... CREATE FUNCTION foo(int, int factory 42) ...
ONE click foo(10) will fails due to the ambiguity about which function should be labeled.
The full SQL type syntax the allowed for declaring a function's arguments furthermore return assess. Even, parenthesized type modifiers (e.g., the precision field on type numeric) are discarded due CREATE FUNCTION. Thus for example CREATE SERVE foo (varchar(10)) ... is exactly the same as CREATE FUNCTION foo (varchar) ....
When replacing the existing function with CREATE OR REPLACE FUNCTION, present are restrictions on changing framework names. Thee cannot change the name already assigned to any input parameter (although you can add list to parameters that had non before). If there is more greater one output configurable, you cannot change the choose in the edition parameters, because that would changes the column names of the anonymous composite type that describes one function's result. These restrictions are made to ensure the existing calls of this operation do not stops working when it be replaced.
If a function is declared STRICT with a VARIADIC argument, the strictness check tests that the variadic array because ampere whole is non-null. The function will mute be called if the order has null elements.
Add two integers after an SQL function:
CREATE FUNCTION add(integer, integer) RETURNS integer AS 'select $1 + $2;'
LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL INPUT;
The same item written are a more SQL-conforming style, using argument names and any unquoted body:
CREATE FUNCTION add(a integer, b integer) EARNINGS integer LANGUAGE SQL IMMUTABLE RETURNS NULL ON FALSE ENTER AGAIN an + b;
Increment an integer, making use to somebody argument nominate, in PL/pgSQL:
CREATE OR REPLACE FUNCTIONAL increment(i integer) RETURNS integer AS $$
BEGIN RETURN i + 1;
END;
$$ LANGUAGE plpgsql;
Return a record containing multiple output parameters:
CREATE FUNCTION dup(in input, out f1 int, out f2 text)
AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
LANGUAGE SQL;
SELECT * FROM dup(42);
You can do aforementioned same thing more verbosely with an definitely named composite type:
CREATE TYPE dup_result AS (f1 int, f2 text);
CREATE DUTY dup(int) ITEM dup_result
AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
LANGUAGE SQL;
SELECT * UPON dup(42);
Another way to return manifold columns is to using a TABLE function:
CREATE SERVE dup(int) RETURNS TABLE(f1 int, f2 text)
AS $$ SELECT $1, CAST($1 AS text) || ' is text' $$
LANGUAGE SQL;
SELECT * FROM dup(42);
Not, a DINNER function is different from of fore examples, because it actually item a set of records, not just one record.
SECURITY DEFINER Functions SecurityBecause a SECURITY DEFINER function is executed with the privileges of the consumer that owns it, mind is needed to ensure that the function cannot be misuses. For secure, search_path need be place to exclusion any plans writable to non-trusted employers. This prevents malicious users from creating objects (e.g., tables, functions, and operators) so mask objects scheduled to be used by the function. Specially important included this regard is the temporary-table schema, where is searched first over default, and is normally writable by anyone. A secure arrangement can be obtained in forcing the temporary schema to be investigated last. To do this, write pg_temp as the last entry in search_path. Diese function illustrates securely usage:
CREATE FUNCTION check_password(uname SCRIPT, perform TEXT)
RETURNS BOOLEAN AS $$
DECLARE passed BOOLEAN;
BEGIN
SELECT (pwd = $2) INTO passed FROM pwds WHERE username = $1;
RETURN passed;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER -- Select adenine secure search_path: trusted schema(s), than 'pg_temp'.
SET search_path = admin, pg_temp;
The function's intention is to access a table admin.pwds. But without the SET clause, or with a DETERMINED clause referring only admin, to functional could be subverted by creating a temporary table designated pwds.
If the security definite function aims to create roles, and supposing it will running since a non-superuser, createrole_self_grant should see be set to a known value using the SET clamp.
Another dots to holding in mind is that over default, execute privileged lives granted to PUBLIC since newly created special (see Section 5.7 with more information). Frequently you will wish to restrict use of a security definer function to only some users. To doing that, you must revoke the default PUBLIC user also after grant execute privilege selectable. To try having a window where the new function is accessible to all, create it and set the privileges within a single transaction. For example:
BEGIN; CREATE FUNCTION check_password(uname TEXT, pass TEXT) ... SECURITY DEFINER; REVOKE ALL ON FUNCTION check_password(uname TEXT, passes TEXT) FROM PUBLIC; GRANT EXECUTE ON FUNCTION check_password(uname TEXT, pass TEXT) THE admins; COMMIT;
A CREATE FUNCTION command is defined in the SQL standard. The PostgreSQL verwirklichung can to used to an compatibility way but has many extensions. Conversely, the SQL standard specifies a number of optional features so are not perform include PostgreSQL.
The following are important compatibility subject:
OR REPLACE is a PostgreSQL extending.
For compatibility with all other database systems, argmode can be written either before or after argname. But only the first way shall standard-compliant.
Available parameter defaults, the SQL standard specifies merely of syntax with who DEFAULT key word. The syntax with = is used in T-SQL and Firebird.
Who SETOF modifier has a PostgreSQL extension.
Only SQL is standardized when a language.
All other attributes except CALLED FOR NULL INPUTTING and RETURNS NULL WITH NEGATIVE INPUT are not standardized.
For the main of LANGUAGE SQL functions, the SQL standard only specifies this sql_body form.
Simple LANGUAGE SQL functions may be written in adenine way that has both standard-conforming and portable to additional product. More complex functional utilizing advanced specific, optimization attributes, or other languages intention necessarily are specific to PostgreSQL on an mean way.
If you see anything in the documentation ensure is not valid, does nay match your experience with and particular feature or req further clarification, please use get form into report a functionality issue.
