PostgreSQL tutorial of xix: SQL language functions
- 2020-05-06 11:52:35
- OfStack
1. Basic concepts:
The SQL function can contain any number of queries, but the function only returns the result of the last query (which must be SELECT). In the simple case, the first row of the last query result is returned. If the last query does not return any rows, the function returns the NULL value. If you need the function to return all the rows of the last SELECT statement, you can define the return value of the function as a collection, SETOF sometype.
The function body of the SQL function should be a semicolon-separated list of SQL statements, with a semicolon optional after the last statement. Unless the function is declared to return void, the last statement must be SELECT. In fact, the SQL function can contain not only SELECT queries, but other standard SQL statements such as INSERT, UPDATE, and DELETE, but statements related to things such as BEGIN, COMMIT, ROLLBACK, and SAVEPOINT.
The syntax of the CREATE FUNCTION command requires that the body of the function be written as a string of text. Typically, this text string constant is surrounded by the dollar character ($$), such as
CREATE FUNCTION clean_emp() RETURNS void AS $$
DELETE FROM emp WHERE salary < 0;
$$ LANGUAGE SQL;
The last thing that needs to explain is the parameter in SQL function. PostgreSQL defines $1 as the first parameter, $2 as the second parameter, and so on. If the parameter is a composite type, the name field in the composite type parameter can be accessed using the dot notation, $1.name. It is important to note that function parameters can only be used as data values, not as identifiers, such as
INSERT INTO mytable VALUES ($1); -- legal
INSERT INTO $1 VALUES (42); -- illegal ( The table name is one of the identifiers )
2. Basic type:
The simplest SQL function, , is probably a function that takes no arguments and returns a primitive type, such as
CREATE FUNCTION one() RETURNS integer AS $$
SELECT 1 AS result;
$$ LANGUAGE SQL;
The following example declares the base type as an argument to a function.
CREATE FUNCTION add_em(integer, integer) RETURNS integer AS $$
SELECT $1 + $2;
$$ LANGUAGE SQL;
# through select Call the function.
postgres=# SELECT add_em(1,2) AS answer;
answer
--------
3
(1 row)
In the following example, the body of the 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 the data table so that the corresponding composite type is also generated.
CREATE TABLE emp (
name text,
salary numeric,
age integer,
);
2). Creates a function whose arguments are of compound type. In a function body, you can refer to a composite type as if it were a primitive type parameter, such as $1. Fields of a composite type can be accessed using a dot expression, such as $1.salary.
CREATE FUNCTION double_salary(emp) RETURNS integer AS $$
SELECT ($1.salary * 2)::integer AS salary;
$$ LANGUAGE SQL;
3). In the select statement, you can use emp.* 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 ROW expressions to construct custom composite types, such as
SELECT name, double_salary(ROW(name, salary*1.1, age)) AS dream FROM emp;
5). Creates a function that returns a value of a compound type, such as
CREATE FUNCTION new_emp() RETURNS emp AS $$
SELECT ROW('None', 1000.0, 25)::emp;
$$ LANGUAGE SQL;
6). Calls a function that returns a compound type.
SELECT new_emp();
7). Calls a function that returns a compound type while accessing a field of the return value.
SELECT (new_emp()).name;
Function with output parameters:
there is another way to return the result of a function's execution, which is to output parameters, such as
CREATE FUNCTION add_em2 (IN x int, IN y int, OUT sum int) AS $$
SELECT $1 + $2
$$ LANGUAGE SQL;
calls methods and returns exactly the same results as add_em(a function with a return value), such as
SELECT add_em(3,7);
this function with output parameters is not substantially different from the previous add_em function. In fact, the real value of an output parameter is that it provides a way for a function to return multiple fields. For example,
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 does not change, but returns an extra column of results.
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 is used to indicate that the parameter is an output parameter.
5. Return results as table data sources:
All SQL functions are available in the FROM clause of the query. This method is particularly useful for functions that return a compound type, which generates a single-field table if the function is defined to return a primitive type, and a row for each property in the compound type if the function is defined to return a compound type. See the following example:
1). Create a data table.
CREATE TABLE foo (
fooid int,
foosubid int,
fooname text
);
2). Creates an SQL function that returns a composite type corresponding to the foo table.
CREATE FUNCTION getfoo(int) RETURNS foo AS $$
SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;
3). This function is called in the FROM clause.
INSERT INTO mytable VALUES ($1); -- legal
INSERT INTO $1 VALUES (42); -- illegal ( The table name is one of the identifiers )
vi. Return the SQL function of the collection:
if the return value of the SQL function is SETOF sometype, all the data for the last SELECT query will be returned when the function is called. This feature is often used to call functions in an FROM clause, as shown in the following example:
CREATE FUNCTION getfoo(int) RETURNS setof foo AS $$
SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;
calls a function in the FROM clause that returns a collection of complex types, and the result is the same as: SELECT * FROM (SELECT * FROM foo WHERE fooid = 1) t1;
SELECT * FROM getfoo(1) AS t1;
vii. Polymorphic SQL function:
The SQL function can be declared to accept parameters of polymorphic types (anyelement and anyarray) or to return the return value of polymorphic types, as shown in the following example:
1). Function parameters and return values are both polymorphic.
INSERT INTO mytable VALUES ($1); -- legal
INSERT INTO $1 VALUES (42); -- illegal ( The table name is one of the identifiers )
is called in exactly the same way as other types of SQL functions, except that when you pass string-type arguments, you need to explicitly convert to the target type, otherwise it will be treated as unknown, such as
CREATE FUNCTION one() RETURNS integer AS $$
SELECT 1 AS result;
$$ LANGUAGE SQL;
2).
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 with the same function name, but their arguments must be distinguished. In other words, function names can be overridden, and this rule is somewhat similar to function overloading in object-oriented languages, as shown in the following example:
CREATE FUNCTION test(int, real) RETURNS ...
CREATE FUNCTION test(smallint, double) RETURNS ...
Since functions support overloading in PostgreSQL, you must also specify a parameter list when deleting functions, such as
DROP FUNCTION test(int, real);
DROP FUNCTION test(smallint,double);