PostgreSQL Tutorial (19): SQL language function _postgresql

Source: Internet
Author: User
Tags field table numeric postgresql first row

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;

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 $ 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;

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 $ + $;
# Call function through SELECT.
postgres=# SELECT Add_em (1,2) as answer;
(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 = $;

Three, compound type:

See the following example:

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

 code as follows:

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;

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:

SELECT ROW (' None ', 1000.0, N):: EMP;

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 $ + $

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 $ + $, $ * $

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:

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 = $;

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 = $;
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, $];

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 $ > $;

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]

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);

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.