如何编写 PL/pgSQL 函数（适用于 PostgreSQL 8.3）

引言

这是对编写基本 PL/pgSQL 函数的介绍。在 PostgreSQL 世界中，“函数”一词与 MS SQL Server 中的“存储过程”一词同义。本文对任何刚接触 PostgreSQL 和编写基本 PL/pgSQL 函数的读者都有帮助。

本文大量参考了在 Open Publication license 下发布的图书《Practical PostgreSQL》。

背景

什么是 PL/pgSQL？

有关良好定义，请参阅 http://en.wikipedia.org/wiki/PL/pgSQL。您会注意到，在 PostgreSQL 中，还有其他语言可用于编写函数。这些超出了本文的范围。

关键概念

代码块

函数的主代码块以声明部分（DECLARE 关键字）开始。

主体代码块以 BEGIN 关键字开始，以 END 关键字结束。主体块应返回其指定返回类型（RETURNS 关键字）的值。

PL/pgSQL 代码块的结构

CREATE FUNCTION identifier (arguments) RETURNS type AS '
  DECLARE
    declaration;
    [...]
  BEGIN
    statement;
    [...]
    RETURN { variable_name | value }
  END;' LANGUAGE 'plpgsql';

注意：返回值“variable_name | value”必须与 type 相同。

简单变量声明的语法

variable_name data_type [ := value ];

更复杂的变量声明的语法

variable_name [ CONSTANT ] data_type [ NOT NULL ] [ { DEFAULT | := } value ];

示例

id INTEGER;
title VARCHAR(10);
price FLOAT;
six CONSTANT INTEGER := 6;
ten INTEGER NOT NULL := 10;

注释

单行注释

-- This will be interpreted as a single-line comment

块注释

 /*
 *  This is a
 *  block
 *  comment.
 */ 

语句

语句要么将值赋给变量，要么执行查询。每个语句都应以分号结尾。

表达式

表达式是计算或操作，它们将其结果作为 PostgreSQL 的基本数据类型之一返回。例如，表达式 x := a + b 将变量 a 和 b 相加，然后将结果赋给变量 x。

变量

必须在 DECLARE 关键字下声明。如果变量在声明时未初始化为默认值，则其值将默认为 SQL NULL 类型。

数据类型

变量可以是 SQL 数据类型，也可以是 RECORD 数据类型。

PL/pgSQL 中常用数据类型的简要列表

• 布尔值
• 文本
• char
• 整数
• double precision
• date
• 时间

赋值

赋值运算符 (:=)

-- value of the right side is assigned to the left variable. 
left_variable := right_variable; 
left_variable := expression;

SELECT INTO：主要用于将查询结果赋给声明为 %ROWTYPE 或 RECORD 类型的变量。

-- SYNTAX
SELECT INTO target_variable [, ...] target_column [, ...] select_clauses;

- Using the SELECT INTO statement

  DECLARE
    customer_fname varchar(100); 
    customer_lname varchar(100); 
  BEGIN
    SELECT INTO customer_fname, customer_lname
                first_name, last_name 
           FROM customers;
    IF NOT FOUND THEN
      return -1;
    END IF;
    return 1;
  END;

FOUND、IS NULL 和 ISNULL 应在条件（IF/THEN）语句中使用。

参数变量

PSQL 中的示例函数调用

--this call:
SELECT get_author('John');
--works for this function:
create function get_author(fname varchar(100) returns integer AS

--this call:
SELECT get_author(1111);
--works for this function:
create function get_author(author_id integer) returns integer AS

Return 语句

Return 语句的值必须与函数声明的“RETURNS”子句中指定的“type”匹配。

属性

使用 %TYPE 属性声明一个变量以匹配数据库对象的类型。

variable_name table_name.column_name%TYPE

使用 %ROWTYPE 属性来匹配行的行结构。

DECLARE
  found_author authors%ROWTYPE;
BEGIN
  SELECT INTO found_author * FROM authors WHERE id = author_id;
END;

条件语句

IF/THEN 语句

IF condition THEN 
  statement;
  [...]
END IF;

IF/THEN/ELSE 语句

IF condition THEN 
  statement;
  [...]
ELSE
  statement;
  [...]
END IF;
END;

IF/THEN/ELSE/IF 语句

IF condition THEN 
  statement;
  [...]
ELSE IF condition
  statement;
  [...]
END IF;

循环

无条件循环

[ <<label>> ]
  LOOP
    statement;
    [...]
    EXIT [ label ] [ WHEN condition ];
  END LOOP;

无条件循环语句将一直循环，直到遇到 EXIT 语句。

标签标识循环块，以便在嵌套多个循环时指定要退出的循环。

WHILE 循环

[ <<label>> ]
WHILE condition LOOP
  statement;
  [...]
END LOOP;

FOR 循环

[ <<label>> ] 
FOR identifier IN [ REVERSE ] expression1 .. expression2  LOOP
  statement;
  [...]
END LOOP;

FOR 循环将为标识符的每个增量值执行一次迭代，该标识符的值范围介于 expression1 和 expression2 之间（包含两者）。标识符的值将初始化为 expression1 的值，而不考虑任何先前的设置，并且每次迭代增加一。如果指定了 REVERSE，则标识符将递减而不是递增。

For 循环示例

DECLARE  
    row_data books%ROWTYPE;
BEGIN
  FOR i IN 0..15 LOOP
      FOR row_data IN SELECT * FROM books
        WHERE subject_id = i  LOOP       
        text_output := text_output || row_data.title || ''\n''; 
      END LOOP;
  END LOOP;        
  RETURN text_output;
END;

处理错误和异常

标准的 PostgreSQL 错误日志工具 (elog) 通常将数据记录到 /var/log/messages 或 $PGDATA/serverlog，并显示到 stderr。

RAISE 语句将消息发送到 elog。

RAISE level ''message string'' [, identifier [...] ];

• DEBUG：在生产模式下运行的数据库将忽略。
• NOTICE：在任何 PostgreSQL 操作模式下发送。
• EXCEPTION：始终发送，并导致当前事务中止。

RAISE DEBUG ''The raise_test() function began.'';    
RAISE NOTICE ''Variable an_integer's value is now %.'',an_integer;
RAISE EXCEPTION ''Variable % changed. Transaction aborted.'',an_integer;

调用函数

从 PL/pgSQL 中调用另一个 PL/pgSQL 函数的正常语法是在 SQL SELECT 语句中引用该函数，或者在变量赋值期间引用。例如：

SELECT function_identifier(arguments);
variable_identifier := function_identifier(arguments);
-- Use the PERFORM keyword to call a function and ignore its return data.
PERFORM function_identifier(arguments); 

返回多行的技术

到目前为止，一个方便地被忽略的事情是如何编写返回多行（也称为 recordset、resultset、table）和多个表的函数。以下是几种方法：

使用“setof <数据库对象类型>”

CREATE OR REPLACE FUNCTION foo_func(prop_id_in integer,
 start_in date, end_in date) RETURNS SETOF foo_type 
  AS $$
DECLARE
  foo foo_type;
  occ RECORD;
BEGIN
  FOR occ IN
    SELECT x.*, y.*
      FROM 
        /*... sql query ...*/
      WHERE x.property_id = prop_id_in
        AND y.start_date BETWEEN start_in AND end_in
  LOOP
    foo.unit_id := occ.unit_id;
    /* ... fill foo  ... */
    RETURN NEXT foo;
  END LOOP;
  RETURN;  
END;
$$ LANGUAGE plpgsql STRICT;

使用 refcursor

create or replace function doit() returnqs refcursor as
$$
  declare
    r refcursor value 'result';
  begin
    /* some query that puts data in refcursor */
  end;
$$ language plpgsql;

-- from psql/app
begin;
select doit();
fetch all from result;
commit;

使用“setof text[]”

使用“returns setof record”

CREATE OR REPLACE FUNCTION sql_get_schema_full(select_table text) 
RETURNS SETOF RECORD AS $$
DECLARE
 sql_result record;
BEGIN
 FOR sql_result in EXECUTE 'SELECT statement here' LOOP
   RETURN NEXT sql_result;
 END LOOP;
END;
$$

调用函数

SELECT * FROM sql_get_schema_full('temp_visit') 
AS temp_schema(table1 name, column1 name, constname name, 
  consttext text, table2 name, column2 name);

本文是对 PostgreSQL 函数的基本介绍。有许多未在此讨论的技术需要学习，但希望本文能帮助您入门。