Postgresql 简明教程

PostgreSQL - Functions

PostgreSQL functions ，也称为存储过程，允许您在数据库中的单个函数中执行通常需要多个查询和往返的操作。由于其他应用程序可以直接与您的存储过程（而不是中间层或复制代码）进行交互，因此函数允许重复使用数据库。

PostgreSQL functions, also known as Stored Procedures, allow you to carry out operations that would normally take several queries and round trips in a single function within the database. Functions allow database reuse as other applications can interact directly with your stored procedures instead of a middle-tier or duplicating code.

可使用您选择的语言（如 SQL、PL/pgSQL、C、Python 等）创建函数。

Functions can be created in a language of your choice like SQL, PL/pgSQL, C, Python, etc.

Syntax

创建函数的基本语法如下 −

The basic syntax to create a function is as follows −

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,

  1. function-name specifies the name of the function.

  2. [OR REPLACE] option allows modifying an existing function.

  3. The function must contain a return statement.

  4. 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.

  5. function-body contains the executable part.

  6. The AS keyword is used for creating a standalone function.

  7. 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.

Example

以下示例说明了如何创建和调用独立函数。此函数返回 COMPANY 表中的记录总数。我们将使用包含以下记录的 COMPANY 表 −

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 the 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() 如下所示 −

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;

执行上述查询后，结果为 −

When the above query is executed, the result would be −

testdb# CREATE FUNCTION

现在，让我们执行对该函数的调用并检查 COMPANY 表中的记录

Now, let us 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 −

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