PostgreSQL 服务器编程简介

#PostgreSQL 服务器编程简介

1 背景知识

如果你认为 PostgreSQL 服务器仅仅是一个存储数据的系统，和它交流的唯一办法就是通过 SQL 语句，那么你就严重低估了它的功能。这仅仅是这个数据库的特性之一。

PostgreSQL 是一个强大的结构，可以用来完成各种各样的数据处理，甚至包括一些非数据服务器的工作。它是一个服务器平台，你可以在这个平台上对各种流行的编程语言开发的函数或库进行简单的组合与匹配。

我们来看一个复杂的多编程语言的工作顺序：

调用 Perl 编写的一个字符串解析函数。
将字符串转换为 XSLT，并使用 JavaScript 处理转换结果。
从外部事件标记服务，比如 www.guardtim.com ，请求一个安全的时间标记，并使用它们提供的 C&C++ 语言版本的 SDK。
编写 Python 函数，用数字的形式表现结果。

以上流程可以借助多种服务器设计语言，需要设计多个简单函数并调用。对于 PostgreSQL 用户来说，只需要调用一个PostgreSQL 函数即可。例如：

SELECT convert_to_xslt_and_sign(raw_data_string);

2 为什么在服务端中进行程序设计

开发者使用不同的编程语言进行程序开发时，期望所编写的代码能够在任何环境下运行。所以编写应用程序的时候尽可能将处理逻辑推送到客户端。那么为什么还需要数据库服务器中进行程序设计呢？

让我们从一个简单的例子开始。许多应用程序会涉及一张客户清单表，这些客户的账户有账户余额。

CREATE table accounts (owner text,balance numeric);
INSERT INTO accounts values('Bob',100);
INSERT INTO accounts VALUES('Mary',200);

如果你想从 Bob 的账户转移14 美元到 Mary 的账户，可以通过简单的SQL 语句实现：

UPDATE accounts SET balance = balance - 14.00 WHERE owner ='Bob';
UPDATE accounts SET balance = balance + 14.00 WHERE owner ='Mary';

2.1 请思考余额的问题

为了完成以上功能，你要确保以下情况。

确保 Bob 的账户有足够余额（或存款）。
确保出现任何事件节点的时候都会导致失败。
考虑上面两点，则数据库的代码需要修改成一下内容。

BEGIN;
SELECT amount FROM accounts WHERE owner ='Bob' FOR UPDATE AND amount > 14;
UPDATE accounts SET amount = amount - 14.00 WHERE owner ='Bob';
UPDATE accounts SET amount = amount + 14.00 WEHRE owner ='Mary';
COMMIT;

2.2 请思考账户是否存在

Mary 一定有账户吗？即便她没有账户，最后的UPDATE 语句仍会成功，只不过更新了0行记录。
如果出现了失败的状况，那么你应该做ROLLBACK 而不是OCMMIT.
如果要求你把最小的可转移的金额限制为5 美元时，又该怎么做。

所以你可以做些什么，让这些工作更加容易管理、更加安全和更加强健？

这就是服务器程序设计可以做到的事情。

它可以自动在数据库服务器上执行代码。
你可以把计算、核对和数据操作集成到位于服务器上的用户定义函数里面（UDF）。
这样做可以保证你只需要管理一份代码，溶蚀使得工作更加便捷，因为你此时不需要再服务器和客户端之间来回调试。
如果需要的话，可以确保哪些必要的信息是被传送到数据库之外的。例如大多数情况下，这些客户端应用程序仅仅需要知道是否有足够的余额可用于转账。

3 使用PL/pgSQL 进行完整性检查

PostgreSQL 有它自己的开发语言，叫做 PL/pgSQL。 PL/pgSQL 的主要目的是轻松地将SQL 语句集成在一起。PL 是 programming language 的简称，意思是程序设计语言。pgSQL 则是 Postgresql 的缩写。

与基础的SQL 不同，PL/pgSQL 包括程序化的元素，比如在 PL/pgSQL 可以使用 if/then/else 语句和循环功能。可以让你轻松地执行SQL 语句设置对SQL语句的结果进行循环操作。
应用程序中所需要进行的完整性检查可以通过PL/pgSQL 函数来完成。这个函数包括3 哥参数：付费人的名字、收款人的名字和付费金额。

CREATE OR REPLACE FUNCTION transfer(
    i_player text,
    i_recipient text,
    i_amount numeric(15, 2))
    RETURNS text
AS
$
DECLARE
    payer_bal numeric ;
BEGIN
    SELECT balance
    INTO payer_bal
    FROM accounts
    WHERE owner = i_player FOR UPDATE;
    IF NOT found THEN
        RETURN 'Payer account not found';
    END IF;
    IF payer_bal < i_amount THEN
        RETURN 'Not enough funds';
    END IF;


    UPDATE accounts
    SET balance = balance - i_amount
    WHERE owner = i_player;
    RETURN 'OK';
END;
$ LANGUAGE plpgsql;