PostgreSQL Learning Manual (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:

The code is as follows:	Copy code
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:

The code is as follows:	Copy code
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:

The code is as follows:	Copy code
Create function one () RETURNS integer AS $ SELECT 1 AS result; $ Language SQL;

The following example declares the basic type as a function parameter.
  

The code is as follows:	Copy code
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.
  

The code is as follows:	Copy code
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.
   

The code is as follows:	Copy code
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.
   

The code is as follows:	Copy code
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.
   

The code is as follows:	Copy code
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:
   

The code is as follows:	Copy code
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:
  

The code is as follows:	Copy code
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.
   

The code is as follows:	Copy code
SELECT new_emp ();

7). Call the function that returns the composite type and access a field of the returned value.
   

The code is as follows:	Copy code
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:
   

The code is as follows:	Copy code
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:
   

The code is as follows:	Copy code
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,
   

The code is as follows:	Copy code
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.

The code is as follows:	Copy code
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.
   

The code is as follows:	Copy code
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.
   

The code is as follows:	Copy code
Create function getfoo (int) RETURNS foo AS $ SELECT * FROM foo WHERE fooid = $1; $ Language SQL;

3). Call this function in the FROM clause.
  

The code is as follows:	Copy code
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:
  

The code is as follows:	Copy code
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:

The code is as follows:	Copy code
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.

The code is as follows:	Copy code
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:
   

The code is as follows:	Copy code
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.

The code is as follows:	Copy code
Create function is_greater (anyelement, anyelement) RETURNS boolean AS $ SELECT $1> $2; $ Language SQL;

3). The polymorphism type is used for function output parameters.
 

The code is as follows:	Copy code
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:

The code is as follows:	Copy code
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:

The code is as follows:	Copy code
Drop function test (int, real ); Drop function test (smallint, double );