postgresql函数

偏执的太偏执、 2022-08-22 14:26 124阅读 0赞

来自：http://www.yiibai.com/html/postgresql/2013/080784.html

PostgreSQL的函数也被称为存储过程，可执行操作，通常会作为一些查询和往返在一个单一的数据库内的函数。函数允许数据库重新使用其他应用程序可以直接与您的存储过程而不是一个中间层或复制代码。

可以创建在所选择的语言，如SQL，PL/pgSQL，C，Python等功能 yiibai.com

## 语法 ##

创建一个函数的基本语法如下：

CREATE [OR REPLACE] FUNCTION function_name (arguments) 
    RETURNS return_datatype AS $variable_name$
      DECLARE
        declaration;
        [...]
      BEGIN
        < function_body >
        [...]
        RETURN {
        variable_name | value }
      END; LANGUAGE plpgsql;

Where,

*  **function-name** specifies the name of the function. www.yiibai.com
 *  \[OR REPLACE\] option allows modifying an existing function.
 *  The function must contain a **return** statement.
 *  **RETURN** clause specifies that data type you are going to return from the function. The**return\_datatype** can be a base, composite, or domain type, or can reference the type of a table column.
 *  **function-body** contains the executable part.
 *  The AS keyword is used for creating a standalone function.
 *  **plpgsql** is the name of the language that the function is implemented in. Here we use this option for PostgreSQL, it Can be SQL, C, internal, or the name of a user-defined procedural language. For backward compatibility, the name can be enclosed by single quotes.

## 语法 ##

The following example illustrates creating and calling a standalone function. This function returns the total number of records in the COMPANY table. We will use the [COMPANY][] table, which has following records:

testdb# select * from COMPANY;
     id | name  | age | address   | salary
    ----+-------+-----+-----------+--------
      1 | Paul  |  32 | California|  20000
      2 | Allen |  25 | Texas     |  15000
      3 | Teddy |  23 | Norway    |  20000
      4 | Mark  |  25 | Rich-Mond |  65000
      5 | David |  27 | Texas     |  85000
      6 | Kim   |  22 | South-Hall|  45000
      7 | James |  24 | Houston   |  10000
    (7 rows)

Function totalRecords() is as follows:

CREATE OR REPLACE FUNCTION totalRecords ()
    RETURNS integer AS $total$
    declare
    	total integer;
    BEGIN
       SELECT count(*) into total FROM COMPANY;
       RETURN total;
    END;
    $total$ LANGUAGE plpgsql; www.yiibai.com

When the above query is executed the result would be:

testdb# CREATE FUNCTION

Now let's execute a call to this function and check the records in the COMPANY table

testdb=# select totalRecords();

When the above query is executed the result would be:  www.yiibai.com

totalrecords
    --------------
                7
    (1 row) www

[COMPANY]: http://www.tutorialspoint.com/postgresql/company.sql