PostgreSQL Tutorial (19): SQL language function _postgresql

First, the basic concept:

SQL functions can contain any number of queries, but the function only returns the result of the last query (which must be a select). In simple cases, returns the first row of the last query result. If the last query does not return any rows, the function returns a null value. If you need this function to return all the rows of the last SELECT statement, you can define the return value of the function as a collection, that is, Setof SomeType.

The function body of the SQL function should be a semicolon-delimited list of SQL statements, and the semicolon after the last statement is optional. The last statement must be a select unless the function declares to return void. In fact, in SQL functions, you can include not only a SELECT query statement, but also other standard SQL statements such as INSERT, update, and delete, but statements related to things cannot contain them, such as Begin, COMMIT, Rollback and savepoint and so on.

The syntax of the CREATE function command requires that the function body be written as a string literal. Typically, the literal string constant is surrounded by a dollar character ($$), such as:

 code as follows:

CREATE FUNCTION clean_emp () RETURNS void as $$
DELETE from EMP WHERE salary < 0;
$$ LANGUAGE SQL;

The last thing to note is the parameters in the SQL function, PostgreSQL defines the first argument, the second argument, and so on. If the argument is a compound type, you can use the dot notation, that is, the $1.name access to the Name field in the compound type parameter. Note that function arguments can only be used as data values, not identifiers, such as:

 code as follows:

INSERT into MyTable VALUES ($); --Legal
INSERT into VALUES (42); --Inappropriate (table name belongs to one of the identifiers)

Second, the basic type:

The simplest SQL function might be a function that has no parameters and returns the base type, such as:

 code as follows:

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.

 code as follows:

CREATE FUNCTION add_em (Integer, Integer) RETURNS integer as $$
SELECT $ + $;
$$ LANGUAGE SQL;
# Call function through SELECT.
postgres=# SELECT Add_em (1,2) as answer;
Answer
--------
3
(1 row)

In the following example, a function body contains multiple SQL statements separated by semicolons.

 code as follows:

CREATE FUNCTION TF1 (integer, numeric) RETURNS numeric as $$
UPDATE Bank SET balance = balance-$ WHERE accountno = $;
SELECT balance from bank WHERE Accountno = $;
$$ LANGUAGE SQL;

Three, compound type:

See the following example:

1. Create a data table so that the corresponding composite type is generated.

 code as follows:

CREATE TABLE EMP (
Name text,
Salary Numeric,
Age integer,
);

2. Creates a function whose arguments are compound types. In a function body, you can reference a composite type as if it were a basic type parameter, such as $. A field that accesses a compound type can use a point expression, such as: $1.salary.

 code as follows:

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.

 code 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 compound type, such as:

code as follows:

SELECT name, Double_salary (ROW (name, salary*1.1, age)) as Dream from EMP;

5. Create a function whose return value is a composite type, such as:

 code as follows:

CREATE FUNCTION new_emp () RETURNS EMP as $$
SELECT ROW (' None ', 1000.0, N):: EMP;
$$ LANGUAGE SQL;

6). Call a function that returns a composite type.

code as follows:

SELECT new_emp ();

7. Invokes a function that returns a compound type, and accesses a field of that return value.

code as follows:

SELECT (New_emp ()). Name;

Four, with output parameters of the function:

There is also a way to return the result of a function execution, that is, an output parameter, such as:

code as follows:

CREATE FUNCTION add_em2 (in x int, at y int, out sum int) as $$
SELECT $ + $
$$ LANGUAGE SQL;

The calling method and the return result are exactly the same as ADD_EM (functions with return values), such as:

 code as follows:

SELECT Add_em (3,7);

There is no essential difference between this function with an output parameter and 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. Such as

 code as follows:

CREATE FUNCTION sum_n_product (x int, y int, out sum int, out product int) as $$
SELECT $ + $, $ * $
$$ LANGUAGE SQL;

The call mode does not change, just returns the result one more column.

 code as follows:

SELECT * from Sum_n_product (11,42);
Sum | Product
-----+---------
53 | 462
(1 row)

In the example above, in is used to indicate that the function argument is an input parameter (the default value can be ignored), and out indicates that the parameter is an output parameter.

Return the result as a 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, and if the function is defined to return a basic type, the function generates a single field table, and if the function is defined to return a compound type, the function generates a row of each property in a composite type. See the following example:
1). Create a data table.

 code as follows:

CREATE TABLE foo (
fooid int,
Foosubid int,
Fooname text
);

2. Creates a SQL function that returns a composite type that corresponds to the Foo table.

 code as follows:

CREATE FUNCTION getfoo (int) RETURNS Foo as $$
SELECT * from foo WHERE fooid = $;
$$ LANGUAGE SQL;

3). Call the function in the FROM clause.

code as follows:

SELECT *, Upper (Fooname) from Getfoo (1) as T1;

Vi. returning the SQL function of the collection:

If the return value of the SQL function is Setof sometype, when the function is called, the entire data for the last select query is returned. This feature is typically used to place functions in the FROM clause, as in the following example:
CREATE FUNCTION getfoo (int) RETURNS setof foo as $$
SELECT * from foo WHERE fooid = $;
$$ LANGUAGE SQL;
A function that returns a collection of composite types is called in the FROM clause with the result equivalent to: SELECT * FROM (SELECT * from foo WHERE fooid = 1) t1;
SELECT * from Getfoo (1) as T1;

seven, polymorphic SQL functions:

SQL functions can be declared as parameters that accept polymorphic types (anyelement and Anyarray) or return values that return polymorphic types, as shown in the following example:
1). Both function parameters and return values are polymorphic types.

 code as follows:

CREATE FUNCTION Make_array (anyelement, anyelement) RETURNS Anyarray as $$
SELECT array[$1, $];
$$ LANGUAGE SQL;

It is called exactly the same as invoking other types of SQL functions, except that when passing arguments of a string type, you need to explicitly convert to the target type, otherwise it will be treated as a unknown type, such as:

 code as follows:

SELECT Make_array (1, 2) as Intarray, Make_array (' A ':: Text, ' B ') as Textarray;

2. The parameter of the function is a polymorphic type, and the return value is the base type.

 code as follows:

CREATE FUNCTION is_greater (anyelement, Anyelement) RETURNS Boolean as $$
SELECT $ > $;
$$ LANGUAGE SQL;

3). The polymorphic type is used for output parameters of the function.

 code as follows:

CREATE FUNCTION DUP (F1 anyelement, out F2 anyelement, out F3 anyarray) as $$
SELECT $, array[$1,$1]
$$ LANGUAGE SQL;

viii. function Overloading:

Multiple functions can be defined as the same function names, but their arguments must be distinguished. In other words, the function name can be overloaded, and some of this rule resembles a function overload in an object-oriented language, as shown in the following example:

 code as follows:

CREATE FUNCTION Test (int, real) RETURNS ...
CREATE FUNCTION Test (smallint, double) RETURNS ...

Because functions support overloading in PostgreSQL, you must also specify a parameter list when you delete a function, such as:

 code as follows:

DROP FUNCTION Test (int, real);
DROP FUNCTION Test (smallint,double);