postgresql - 什么是 PostgreSQL 函数，我什么时候必须使用它们？

Question

我想知道什么是 PostgreSQL 函数。
我什么时候必须写它们？
我该怎么写？
我该如何称呼他们？

Answer 1

定义，from wikipedia :

A stored procedure is a subroutine available to applications that access a relational database system.

一般存储过程的优点，from wikipedia :

Overhead: Because stored procedure statements are stored directly in the database, they may remove all or part of the compilation overhead that is typically required in situations where software applications send inline (dynamic) SQL queries to a database. (...)

Avoidance of network traffic: A major advantage with stored procedures is that they can run directly within the database engine. In a production system, this typically means that the procedures run entirely on a specialized database server, which has direct access to the data being accessed. The benefit here is that network communication costs can be avoided completely. This becomes particularly important for complex series of SQL statements.

Encapsulation of business logic: Stored procedures allow programmers to embed business logic as an API in the database, which can simplify data management and reduce the need to encode the logic elsewhere in client programs. (...)

Delegation of access-rights: In many systems, stored procedures can be granted access rights to the database that users who execute those procedures do not directly have.

Some protection from SQL injection attacks: Stored procedures can be used to protect against injection attacks. Stored procedure parameters will be treated as data even if an attacker inserts SQL commands. (...)

在 PostgresSQL 中，存储过程称为用户定义函数。定义示例:

CREATE FUNCTION somefunc(quantity integer) RETURNS integer AS $$
DECLARE
    myvariable integer := 2;
BEGIN   
    RETURN quantity * myvariable;
END;
$$ LANGUAGE plpgsql;

(您可以使用其他语言在PostgreSQL中定义存储函数)

调用示例:

SELECT somefunc(100);

更多信息:http://www.postgresql.org/docs/9.1/static/server-programming.html