PostgreSQL tutorial (19th): SQL language functions, postgresqlsql

PostgreSQL tutorial (19th): SQL language functions, postgresqlsql

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:
 Copy codeThe Code is as follows:
Create function clean_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 notation, 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:
 Copy codeThe Code is as follows:
Insert into mytable VALUES ($1); -- valid
Insert into $1 VALUES (42); -- invalid (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:
 Copy codeThe Code is as follows:
Create function one () RETURNS integer AS $
SELECT 1 AS result;
$ Language SQL;
 
The following example declares the basic type as a function parameter.
 Copy codeThe Code is as follows:
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.
 Copy codeThe Code is as follows:
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.
 Copy codeThe Code is as follows:
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.
Copy codeThe Code is as follows:
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.
 Copy codeThe Code is as follows:
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:
 Copy codeThe Code is as follows:
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:
 Copy codeThe Code is as follows:
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.
 Copy codeThe Code is as follows:
SELECT new_emp ();
 
7). Call the function that returns the composite type and access a field of the returned value.
 Copy codeThe Code is as follows:
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:
 Copy codeThe Code is as follows:
Create function add_em2 (IN x int, IN y int, OUT sum 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:
 Copy codeThe Code is as follows:
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,
 Copy codeThe Code is as follows:
Create function sum_n_product (x int, y int, OUT sum int, OUT product int) AS $
SELECT $1 + $2, $1*$2
$ Language SQL;
 
The call method is not changed, but an extra column is returned.
 Copy codeThe Code is as follows:
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.
 Copy codeThe Code is as follows:
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.
 Copy codeThe Code is as follows:
Create function getfoo (int) RETURNS foo AS $
SELECT * FROM foo WHERE fooid = $1;
$ Language SQL;
 
3). Call this function in the FROM clause.
 Copy codeThe Code is as follows:
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 to: 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 parameters that accept the polymorphism (anyelement and anyarray) or return the return value of the polymorphism type, as shown in the following example:
1) function parameters and return values are of the polymorphism type.
 Copy codeThe Code is as follows:
Create function make_array (anyelement, anyelement) RETURNS anyarray AS $
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:
 Copy codeThe Code is as follows:
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.
 Copy codeThe Code is as follows:
Create function is_greater (anyelement, anyelement) RETURNS boolean AS $
SELECT $1> $2;
$ Language SQL;
 
3). The polymorphism type is used for function output parameters.
 Copy codeThe Code is as follows:
Create function dup (f1 anyelement, OUT f2 anyelement, OUT f3 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:
 Copy codeThe Code is as follows:
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:
 Copy codeThe Code is as follows:
Drop function test (int, real );
Drop function test (smallint, double );