Introduction to basic knowledge of functions

  PostgreSQL, like most databases, can group SQL statements together and parse them as a unit, with different parameters entered each time it is run. This mechanism is called by different names in different databases. Some are called stored procedures, some are called user defined functions, and PostgreSQL calls them collectively:

The basic structure of a function

CREATE OR REPLACE FUNCTION func_name(arg1 arg1_datatype DEFAULT arg1_default)
RETURNS some type | set of some type | TABLE (..) AS
?
BODY of function
?
LANGUAGE language_of_function
Copy the code

Definition of function parameters:

  • Named parameter:
big_elephant(ear_size numeric, skin_color text DEFAULT 'blue',
name text DEFAULT 'Dumbo')
-- Arguments can have default values
Copy the code
  • Anonymous parameters
Big_elephant (ear_size, skin_color, name)Copy the code
  • Named parameters, using the call with the parameter name
Big_elephant (name => 'Wooly', ear_size => 1.2) -- This parameter can be added regardless of the orderCopy the code

I suggest that there is no need to use fancy way, according to the order of function entry parameters, convenient reading and subsequent management optimization and other operations. PostgreSQL9.5 calls the method name with the parameter name: name=>’Wooly’. For compatibility purposes, PostgreSQL9.5 calls the method name: ‘Wooly’ can still be used in PostgreSQL9.5 and later, but try not to be an afterthought as it will be removed in later versions!

PostgreSQL markers

  • LANGUAGE(programming LANGUAGE used)

Indicates the programming language used by this function, which of course must be installed in the database where the current function resides. Run the SELECT lanname FROM pg_language command. You can view a list of installed languages.

  • The VOLATILITY of the results

This marker can tell the query planner whether the result can be cached for future use when the function is finished executing. It has the following optional values:

1. IMMUTABLE

In any case, as long as you call the function with the same input, you always get the same output. That is, the internal logic of the function is completely independent of the outside world. The most typical example of this type of function is a mathematical calculation function. Note that functional indexes must be defined IMMUTABLE. 2. STABLE

If you call this function multiple times within the same query, you will always get the same output each time, as long as you use the same input. That is, the internal logic of the function has constant output in the context of the current SQL.

3. VOLATILE

The result may be different each time the function is called, even if the same input is used each time. Functions that change data and those that depend on environment Settings such as system time are VOLATILE. This is also the default. Note that the VOLATILITY marker merely provides a hint to the planner, which does not necessarily act on this setting. If the function is marked VOLATILE, the planner will re-parse and re-execute it each time it encounters it. If it is marked as a different type, the planner may not cache the results of its execution because the planner might think it would be faster to recalculate.

  • STRICT (STRICT mode)

For a strictly modal function, if any input is NULL, the planner does not execute the function at all and returns NULL. If STRICT mode is not explicitly specified, functions default to STRICT mode. Be careful with STRICT when writing functions, because using STRICT may cause the planner not to use indexes.

  • COST (Implementation COST estimate)

This is a relative measure of how intensive the computation is in the marker function. If SQL or PL/pgSQL is used, the value is 100. If the C language is used, the value is 1. This value affects the priority the planner gives to executing the function in the WHERE clause and the likelihood of caching the result set for that function. The larger this value is, the more time the planner thinks it will take to execute the function.

  • ROWS (returns an estimate of the number of ROWS in the result set)

This token is useful only if the function returns a result set. This value is an estimate of the number of records returned in the result set. The planner uses this value to determine the best execution strategy for this function analysis.

  • SECURITY DEFINER

If a security control is set, the function is executed with the permissions of the user who created it; If not set, the function is executed with the permission of the user who called it. If a user does not have permission to work on a table and needs to work on it, the user who created the table can be asked to provide a function with the SECURITY DEFINER identifier to work on the table. As you can see, this security control is useful when you need to control access to tables.

  • PARALLEL = PARALLEL

This tag is new in PostgreSQL 9.6. This flag allows the planner to run in parallel mode. By default, the function is set to PARALLEL UNSAFE, which means that any statements calling the function will not be distributed over multiple worker processes for concurrent execution. For details, please refer to the official manual “Parallel Security”. The supported options are as follows:

1. SAFE

This option allows the function to be executed in parallel. Setting SAFE is generally fine if a function is IMMUTABLE, or if it does not update data or change transaction state or other variable values.

2. The UNSAFE

Functions that modify non-temporary data, access sequence number generators, or transaction state should all be set to UNSAFE. UNSAFE functions cannot be executed in parallel because they may corrupt table data or other system state if executed in parallel mode.

3. The RESTRICTED

You can use this option for functions that use temporary tables, pre-parse statements, or client connection state. The RESTRICTED statement is not prohibited from parallel execution, but it can only be run in the lead of a parallel group. This means that the function itself is not executed in parallel, but it does not prevent the SQL statement calling it from being executed in parallel. The PARALLEL tag is used in many of the examples in this chapter. If you are working on PostgreSQL prior to 9.6, please remove the PARALLEL tag when executing the examples.