PostgreSQL learning Manual (16) SQL language functions

I. Basic concepts:
SQL functions can contain any number of queries, but the function returns only the results of the last query (which must be select. In simple cases, the first row of the last query result is returned. If no row is returned for the last query, the function returns NULL. If you need this function to return all rows of the last SELECT statement, you can define the return value of the function as a set, that is, setof sometype.
The SQL function body should be a list of SQL statements separated by semicolons. The semicolon after the last statement is optional. Unless the function declaration returns void, the last statement must be select. In fact, SQL functions can contain not only SELECT query statements, but also insert, update, delete, and other standard SQL statements. However, transaction-related statements cannot contain, such as begin, commit, rollback, and savepoint.
The syntax of the create function command requires that the function body be written as a string. Generally, this text string constant is enclosed by a dollar sign ($), for example:
Create FunctionClean_emp () Returns void as $
Delete from EMP where salary <0;
$ Language SQL;
Finally, you need to note the parameters in the SQL function. PostgreSQL defines $1 as the first parameter, $2 as the second parameter, and so on. If the parameter is of the composite type, you can use the dot table display method, that is, $1. Name, to access the name field in the composite type parameter. Note that function parameters can only be used as data values, but not identifiers, such:
Insert into mytable values ( $1); -- Valid
Insert $1Values (42 ); -- Invalid (the table name is one of the identifiers)

Ii. basic types:
The simplest SQL function may be a function that has no parameters and returns the basic type, for example:
Create Function one () returns Integer as $
Select 1 as result;
$ Language SQL;
The following example declares the basic type as a function parameter.
Create Function add_em (integer, integer) returns Integer as $
Select $1 + $2;
$ Language SQL;
# Call a function through select.
Postgres = # select add_em (1, 2) as answer;
Answer
--------
3
(1 row)
In the following example, a function contains multiple SQL statements separated by semicolons.
Create Function TF1 (integer, numeric) returns numeric as $
Update Bank set balance = balance-$2 where accountno = $1;
Select balance from bank where accountno = $1;
$ Language SQL;

Iii. Composite Type :
See the following example:
1) create a data table, and the corresponding composite type is also generated.
Create Table EMP (
Name text,
Salary numeric,
Age integer,
);
2) create a function. Its parameters are of the composite type. In a function, you can reference a composite type as you reference a basic type parameter, for example, $1. Use a dot expression to access fields of the compound type, for example, $1. salary.
Create Function double_salary (EMP) returns Integer as $
Select ($1. Salary * 2): Integer as salary;
$ Language SQL;
3) In the SELECT statement, EMP. * can be used to represent an entire row of data in the EMP table.
Select name, double_salary (EMP. *) as dream from EMP where age> 30;
4). We can also use the row expression to construct a custom composite type, such:
Select name, double_salary (row (name, salary * 1.1, age) as dream from EMP;
5) create a function, whose return value is of the composite type, for example:
Create Function new_emp () returns EMP as $
Select row ('none', 1000.0, 25): EMP;
$ Language SQL;
6). Call the function that returns the composite type.
Select new_emp ();
7). Call the function that returns the composite type and access a field of the returned value.
Select (new_emp (). Name;

Iv. Functions with output parameters:
Another method can be used to return the result of function execution, that is, output parameters, such:
Create FunctionAdd_em2 (InX int,InY int,OutSum INT)As $
Select $1 + $2
$ Language SQL;
The call method and returned result are exactly the same as add_em (a function with a returned value), for example:
Select add_em (3,7 );

There is no essential difference between this function with output parameters and the previous add_em function. In fact, the real value of the output parameter is that it provides the function with a way to return multiple fields. For example,
Create FunctionSum_n_product (x int, y int,OutSum int,OutProduct INT)As $
Select $1 + $2, $1*$2
$ Language SQL;
The call method is not changed, but an extra column is returned.
Select * From sum_n_product (11,42 );
Sum | product
----- + ---------
53 | 462
(1 row)
In the preceding example, in indicates that this function parameter is an input parameter (default value, which can be ignored), and out indicates that this parameter is an output parameter.

5. Return results as Table Data source:
All SQL functions can be used in the from clause of the query. This method is particularly useful for functions that return a composite type. If this function is defined as returning a basic type, the function generates a single-word field table. If this function is defined as returning a composite type, this function generates a row composed of each attribute in the composite type. See the following example:
1) create a data table.
Create Table Foo (
Fooid int,
Foosubid int,
Fooname text
);
2) create an SQL function. The returned value is a composite type corresponding to the foo table.
Create Function getfoo (INT) returns Foo as $
Select * From Foo where fooid = $1;
$ Language SQL;
3). Call this function in the from clause.
Select *, upper (fooname) from getfoo (1) as T1;

6. Return the SQL functions of the Set:
If the return value of the SQL function is setof sometype, all data of the last SELECT query is returned when this function is called. This feature is usually used to place functions in the from clause for calling. See the following example:
Create Function getfoo (INT) returns setof Foo as $
Select * From Foo where fooid = $1;
$ Language SQL;
The from clause calls a function that returns a composite set. The result is equivalent:Select * from (select * From Foo where fooid = 1) T1;
Select * From getfoo (1) as T1;

VII. multi-state SQL functions:
SQL functions can be declared as accepted polymorphism types ( Anyelement And Anyarray ). See the following example:
1) function parameters and return values are of the polymorphism type.
Create Function make_array (Anyelement, anyelement) ReturnsAnyarrayAs $
Select array [$1, $2];
$ Language SQL;
The calling method is the same as calling other types of SQL functions. It only needs to be explicitly converted to the target type when passing string-type parameters. Otherwise, it will be considered as the unknown type, for example:
Select make_array (1, 2) As intarray, make_array ('A': Text, 'B') as textarray;
2). function parameters are of the polymorphism type, while return values are of the basic type.
Create Function is_greater (anyelement, anyelement) returns boolean as $
Select $1> $2;
$ Language SQL;
3). The polymorphism type is used for function output parameters.
Create Function DUP (F1 anyelement,OutF2 anyelement,OutF3 anyarray) as $
Select $1, array [$1, $1]
$ Language SQL;

8. Function overloading:
Multiple functions can be defined as the same function name, but their parameters must be differentiated. In other words, function names can be reloaded. This rule is similar to function overloading in object-oriented languages. See the following example:
Create Function Test (INT, real) returns...
Create Function Test (smallint, double) returns...

Because function Overloading is supported in PostgreSQL, you must specify a parameter list When deleting a function, for example:
Drop function test (INT, real );
Drop function test (smallint, double );