Function

Defines a function

Properties

Property	Type	Required	Description
schema	string	yes	The schema the function is created in.
name	string	yes	The function name.
owner	string	—	The role that owns the function.
sql	string	—	User-provided raw SQL snippet.
parameters	array of object	—	An array of IN, OUT, BOTH, VARADIC, and TABLE args.
returns	string	—	The return type for the funciton.
language	string	—	The name of the language that the function is implemented in. It can be sql, c, internal, or the name of a user-defined procedural language, e.g. plpgsql.
transform_types	array of string	—	Lists which transforms a call to the function should apply.
window	boolean	—	Indicates that the function is a window function rather than a plain function. This is currently only useful for functions written in C.
immutable	boolean	—	Indicates that the function cannot modify the database and always returns the same result when given the same argument values; that is, it does not do database lookups or otherwise use information not 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	boolean	—	Indicates that the function cannot modify the database, and that within a single table scan it will consistently return the same result for the same argument values, but that its result could change across SQL statements. This is the appropriate selection for functions whose results depend on database lookups, parameter variables (such as the current time zone), etc. (It is inappropriate for AFTER triggers that wish to query rows modified by the current command.) Also note that the current_timestamp family of functions qualify as stable, since their values do not change within a transaction.
volatile	boolean	—	Indicates that the function value can change even within a single table scan, so no optimizations can be made. Relatively few database functions are volatile in this sense; some examples are random(), currval(), timeofday(). But note that any function that has side-effects must be classified volatile, even if its result is quite predictable, to prevent calls from being optimized away; an example is setval().
leak_proof	boolean	—	indicates that the function has no side effects. It reveals no information about its arguments other than by its return value. For example, a function which throws an error message for some argument values but not others, or which includes the argument values in any error message, is not leakproof. This affects how the system executes queries against views created with the security_barrier option or tables with row level security enabled. The system will enforce conditions from security policies and security barrier views before any user-supplied conditions from the query itself that contain non-leakproof functions, in order to prevent the inadvertent exposure of data. Functions and operators marked as leakproof are assumed to be trustworthy, and may be executed before conditions from security policies and security barrier views. In addition, functions which do not take arguments or which are not passed any arguments from the security barrier view or table do not have to be marked as leakproof to be executed before security conditions.
called_on_null_input	boolean	—	Indicates that the function will be called normally when some of its arguments are null. It is then the function author's responsibility to check for null values if necessary and respond appropriately. Default: true.
strict	boolean	—	Indicates that the function always returns null whenever any of its arguments are null. If this parameter is specified, the function is not executed when there are null arguments; instead a null result is assumed automatically. Default: false.
security	INVOKER | DEFINER	—	INVOKER indicates that the function is to be executed with the privileges of the user that calls it. That is the default. DEFINER specifies that the function is to be executed with the privileges of the user that owns it.
parallel	SAFE | UNSAFE | RESTRICTED	—	UNSAFE indicates that the function can't be executed in parallel mode and the presence of such a function in an SQL statement forces a serial execution plan. This is the default. PARALLEL indicates that the function can be executed in parallel mode, but the execution is restricted to parallel group leader. SAFE indicates that the function is safe to run in parallel mode without restriction.
cost	integer	—	A positive number giving the estimated execution cost for the function, in units of cpu_operator_cost. If the function returns a set, this is the cost per returned row. If the cost is not specified, 1 unit is assumed for C-language and internal functions, and 100 units for functions in all other languages. Larger values cause the planner to try to avoid evaluating the function more often than necessary.
rows	integer	—	A positive number giving the estimated number of rows that the planner should expect the function to return. This is only allowed when the function is declared to return a set. The default assumption is 1000 rows.
support	string	—	The name (optionally schema-qualified) of a planner support function to use for this function.
configuration	object	—	Configuration parameters to be set to the specified value when the function is entered, and then restored to its prior value when the function exits.
definition	string	—	A string constant defining the function; the meaning depends on the language. It can be an internal function name, the path to an object file, an SQL command, or text in a procedural language.
object_file	string	—	Used for dynamically loadable C language functions when the function name in the C language source code is not the same as the name of the SQL function.
link_symbol	string	—	The string link_symbol is the function's link symbol when used in conjunction with object_file, that is, the name of the function 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 function being defined. The C names of all functions must be different, so you must give overloaded C functions different C names (for example, use the argument types as part of the C names).
comment	string	—	An optional comment about the function.
dependencies	dependencies	—	Database objects this object is dependent upon.

parameters[]

Property	Type	Required	Description
mode	IN | OUT | BOTH | VARADIC | TABLE	yes	Parameter Mode.
name	string	—	Parameter Name.
data_type	string	yes	Parameter Data Type.
default	boolean | integer | null | number | string	—	Default value for Paramter.

Mutually exclusive forms

Exactly one of these must hold:

• language and returns and definition, without sql or object_file or link_symbol
• sql, without returns or language or transform_types or window or immutable or stable or volatile or leak_proof or called_on_null_input or strict or security or parallel or cost or rows or support or configuration or definition or object_file or link_symbol

No other properties are accepted.

---

Source: schemata/function.yml · Resolved JSON Schema: function.json