Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what are the options in the PostgreSQL creation function". In the daily operation, I believe that many people have doubts about the options in the PostgreSQL creation function. Xiaobian consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful for you to answer the doubts about "what are the options in the PostgreSQL creation function?" Next, please follow the editor to study!

The syntax of Create Function is as follows:

[pg12@localhost ~] $psqlExpanded display is used automatically.psql "help" for help. [local:/data/run/pg12]: 5120 pg12@testdb=#\ help create functionCommand: CREATE FUNCTIONDescription: define a new functionSyntax:CREATE [OR REPLACE] FUNCTION name ([[argmode] [argname] argtype [{DEFAULT | =} default_expr] [,...]]) [RETURNS rettype | RETURNS TABLE (column_name column_type [ ] {LANGUAGE lang_name | TRANSFORM {FOR TYPE type_name} [ | WINDOW | IMMUTABLE | STABLE | VOLATILE | [NOT] LEAKPROOF | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT | [EXTERNAL] SECURITY INVOKER | [EXTERNAL] SECURITY DEFINER | PARALLEL {UNSAFE | RESTRICTED | SAFE} | COST execution_cost | ROWS result_rows | SET configuration_parameter {TO value | = value | FROM CURRENT} | AS 'definition' | AS' obj_file' 'link_symbol'}... URL: https://www.postgresql.org/docs/12/sql-createfunction.html[local:/data/run/pg12]:5120 pg12@testdb=#

LANGUAGE

Including sql,c,internal,plpgsql and so on.

TRANSFORM

This option lists the transform. Exe that should be used when calling the function.

Transform shows how to convert data types to a programming language. For example, using the hstore type to write functions in the PL/Python language

PL/Python cannot identify data of type hstore in the Python language environment. The language uses text by default, but if an array or linked list appears

This default treatment is not appropriate.

Transform specifies two functions:

"from SQL": from SQL environment to programming language. This function processes parameters when using functions written in the language.

To SQL: from programming languages to SQL environments. Called when the return value is processed.

This option is used in programming languages such as PL/Java.

WINDOW

Use this option to indicate that the function is a window function function rather than a normal function.

IMMUTABLE | STABLE | VOLATILE

These three will affect the query optimizer. The default option is VOLATILE.

IMMUTABLE: indicates that the function cannot change the database and returns whatever parameter is given, 100% certainty.

STABLE: indicates that the function cannot change the database. During a table scan, the same results will be returned for the same parameters, but the results can be changed between SQL statements.

If the result depends on database lookup, you can use this option when the parameter variable is variable.

VOLATILE: the results can also be changed during a table scan. Such as random (), currval () function and so on.

LEAKPROOF

This option indicates that the function has no side effects. Except for the return value, there is no information about the reveals parameter, for example, if an error message is thrown because of some parameter value

Or if the parameter value is included in the error message, the function is not leafproof.

This option affects view created with the security_barrier option or row level security-enabled datasheets.

The system will execute the conditions from the security policy and the security barrier view from the query itself before the conditions provided by the user including the non-leakproof function to prevent accidental leakage of data.

Functions and operators marked leakproof are considered to be trusted and can be executed before the conditions of the security policy and security barrier view.

CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT

The default option is CALLED ON NULL INPUT, which means that functions can be called normally if some parameters are null. The writer of the function is responsible for checking the null value.

RETURNS NULL ON NULL INPUT | STRICT indicates that if the parameter is null, null is returned.

SECURITY INVOKER | SECURITY DEFINER

SECURITY INVOKER means to execute the function with the permissions of the caller, which is the default option. Security DEFINER means to execute the function with the permissions of owner.

PARALLEL UNSAFE | RESTRICTED | SAFE

PARALLEL UNSAFE indicates that the function cannot be executed in parallel mode. If the function appears in SQL, it needs to be executed serially, which is the default option.

PARALLEL RESTRICTED indicates that the function can be executed in parallel mode, but the executor is limited to the leader.

PARALLEL SAFE stands for unconstrained parallel execution.

If the function modifies the database state or uses subtransactions to modify the transaction state or attempts to access the sequence or change settings (such as setval), the function should be marked unsafe.

Functions that access temporary tables, client connection status, cursors, prepared statements, or back-end local states where the system cannot synchronize in parallel mode should be marked as restricted.

If the function is actually unsafe but marked as safe, it will result in an unpredictable dislocation.

COST

The estimated cost of function execution, in cpu_operator_cost.

ROWS

The estimated number of rows returned by the function.

SUPPORT

This function uses planner support function.

SET

When entering the function, set the configuration parameter to the specified value, and return to the original parameter value after execution.

AS

Function definition.

At this point, the study of "what are the options in the PostgreSQL creation function" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.