1. Basic concepts:
SQL functions can contain any number of queries, but the function only returns the result of the last query (must be SELECT). In simple cases, return the first row of the last query result. If the last query does not return any rows, the function returns a NULL value. If the function needs to return all rows of the last SELECT statement, the return value of the function can be defined as a collection, that is, SETOF sometype.
The function body of an SQL function should be a semicolon-separated list of SQL statements, where the semicolon after the last statement is optional. Unless the function is declared to return void, the last statement must be SELECT. In fact, in SQL functions, not only SELECT query statements can be included, but also other standard SQL statements such as INSERT, UPDATE, and DELETE, but statements related to things cannot be included, such as BEGIN, COMMIT, ROLLBACK, and SAVEPOINT.
The syntax of the CREATE FUNCTION command requires the function body to be written into a string text. Generally speaking, the text string constant is surrounded by a dollar sign ($$), such as:
CREATE FUNCTION clean_emp() RETURNS void AS $$
DELETE FROM emp WHERE salary < 0;
$$ LANGUAGE SQL;
The last thing to be explained is the parameters in the SQL function. PostgreSQL defines $1 to represent the first parameter, $2 to represent the second parameter and so on. If the parameter is a composite type, you can use point notation, i.e., $ access the name field in the composite type parameter. It should be noted that function parameters can only be used as data values, but cannot be used as identifiers, such as:
INSERT INTO mytable VALUES ($1); --Legal
INSERT INTO $1 VALUES (42); --Illegal (the table name belongs to one of the identifiers)
2. Basic types:
The simplest SQL function may be a function that has no parameters and returns a basic type, such as:
CREATE FUNCTION one() RETURNS integer AS $$
SELECT 1 AS result;
$$ LANGUAGE SQL;
The following example declares the basic type as a parameter of the function.
CREATE FUNCTION add_em(integer, integer) RETURNS integer AS $$
SELECT $1 + $2;
$$ LANGUAGE SQL;
# Call the function through select.
postgres=# SELECT add_em(1,2) AS answer;
answer
--------
3
(1 row)
In the following example, the function body 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;
3. Compound type:
See the following example:
1). Create a data table so that the corresponding compound type is generated as well.
CREATE TABLE emp (
name text,
salary numeric,
age integer,
);
2). Create a function whose parameters are compound types. In the function body, compound types can be referenced like $1, like referenced as basic type parameters. To access fields of compound types, use dot expressions, such as: $.
CREATE FUNCTION double_salary(emp) RETURNS integer AS $$
SELECT ($ * 2)::integer AS salary;
$$ LANGUAGE SQL;
3). In the select statement, you can use emp.* to represent a whole row of data in the emp table.
SELECT name, double_salary(emp.*) AS dream FROM emp WHERE age > 30;
4). We can also use ROW expressions to construct custom composite types, such as:
SELECT name, double_salary(ROW(name, salary*1.1, age)) AS dream FROM emp;
5). Create a function whose return value is a compound type, such as:
CREATE FUNCTION new_emp() RETURNS emp AS $$
SELECT ROW('None', 1000.0, 25)::emp;
$$ LANGUAGE SQL;
6). Call a function that returns compound type.
SELECT new_emp();
7). Call a function that returns compound type and accesses a field of the return value at the same time.
SELECT (new_emp()).name;
4. Functions with output parameters:
There is another method that can be used to return the result of function execution, that is, output parameters, such as:
CREATE FUNCTION add_em2 (IN x int, IN y int, OUT sum int) AS $$
SELECT $1 + $2
$$ LANGUAGE SQL;
The calling method and return result are exactly the same as add_em (a function with a return value), such as:
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 a way for the function to return multiple fields. like,
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 has not changed, but the result is returned with one more column.
SELECT * FROM sum_n_product(11,42);
sum | product
-----+---------
53 | 462
(1 row)
In the above example, IN is used to indicate that the function parameter is an input parameter (the default value can be ignored), and OUT means that the parameter is an output parameter.
5. Return the result as the 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 compound types. If the function is defined to return a primitive type, the function generates a single-field table, and if the function is defined to return a compound type, the function generates a row composed of each attribute in the compound type. See the following example:
1). Create a data table.
CREATE TABLE foo (
fooid int,
foosubid int,
fooname text
);
2). Create an SQL function with the return value of the compound 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 function of the collection:
If the return value of the SQL function is SETOF sometype, then when the function is called, all the data of the last SELECT query will be returned. This feature is usually used to call functions in FROM clauses, see the following example:
CREATE FUNCTION getfoo(int) RETURNS setof foo AS $$
SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;
The function returning a collection of compound types is called in the FROM clause, and the result is equivalent to: SELECT * FROM (SELECT * FROM foo WHERE fooid = 1) t1;
SELECT * FROM getfoo(1) AS t1;
7. Polymorphic SQL functions:
SQL functions can be declared as accepting parameters of polymorphic types (anyelement and anyarray) or returning the return value of polymorphic types, see the following example:
1). The function parameters and return values are both polymorphic types.
CREATE FUNCTION make_array(anyelement, anyelement) RETURNS anyarray AS $$
SELECT ARRAY[$1, $2];
$$ LANGUAGE SQL;
The call method is exactly the same as calling other types of SQL functions. However, when passing the parameters of string type, it needs to be explicitly converted to the target type, otherwise it will be regarded as unknown type, such as:
SELECT make_array(1, 2) AS intarray, make_array('a'::text, 'b') AS textarray;
2). The parameters of the function are polymorphic types, while the return value is the basic type.
CREATE FUNCTION is_greater(anyelement, anyelement) RETURNS boolean AS $$
SELECT $1 > $2;
$$ LANGUAGE SQL;
3). Polymorphic types are used for output parameters of functions.
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 distinguished. In other words, function names can be overloaded, and this rule is somewhat similar to function overloading in object-oriented languages, see the following example:
CREATE FUNCTION test(int, real) RETURNS ...
CREATE FUNCTION test(smallint, double) RETURNS ...
Since functions support overloading in PostgreSQL, when deleting functions, a parameter list must also be specified, such as:
DROP FUNCTION test(int, real);
DROP FUNCTION test(smallint,double);