24.2. 描述

24.2.3.1. 带缺省值的常量和变量

声明有下面的语法∶

name [ CONSTANT ] type [ NOT NULL ] [ { DEFAULT | := } value ];

定义为 CONSTANT 的变量数值不能修改．如果声明了 NOT NULL，那么赋予 NULL 数值将导致一个运行时错误．因为所有变量的缺省值是 SQL NULL，所以所有声明为 NOT NULL 的变量还必须 声明一个缺省值．

缺省值是在每次调用函数的时候计算的．因此给一个类型为 timestamp 的变量赋值会令变量拥有函数实际调用 的时间，而不是函数预编译成其字节码的时间．

例子∶

quantity INTEGER := 32;
url varchar := ''http://mysite.com'';
user_id CONSTANT INTEGER := 10;

24.2.3.2. 传递给函数的变量

传递给函数的变量都是用 $1，$2， 等等这样的标识符(最大是 16)命名的．一些例子∶

CREATE FUNCTION sales_tax(REAL) RETURNS REAL AS '
DECLARE
    subtotal ALIAS FOR $1;
BEGIN
    return subtotal * 0.06;
END;
' LANGUAGE 'plpgsql';


CREATE FUNCTION instr(VARCHAR,INTEGER) RETURNS INTEGER AS '
DECLARE
    v_string ALIAS FOR $1;
    index ALIAS FOR $2;
BEGIN
    -- Some computations here
END;
' LANGUAGE 'plpgsql';

24.2.3.3. 属性

使用 %TYPE 和 %ROWTYPE 属性， 你可以声明与另外一个数据库项相同的数据类型或结构(比如∶ 一个表的域)．

%TYPE

%TYPE 给我们提供了一个变量或者数据库列的数据类型． 你可以用这个关键字声明那些需要保存数据库数值的变量． 举例来说，让我们假设你有一个叫 user_id 的列 在你的 users 表里．要声明一个和它相同的数据类型， 你可以∶

user_id users.user_id%TYPE;

通过使用 %TYPE，你就不需要知道你引用的 结构的数据类型，最重要的是，如果被引用项的数据类型在将来 变化了(比如你把表定义列的 user_id 改成 REAL)，那么你就 不用修改你的函数定义．

name table%ROWTYPE;

声明一个与给定表结构相同的行．table 必须 是该数据库列一个现存的表或者视图．该行的域是用点表示法进行访问的． 给函数的参数可以是组合类型(完整的表的行)．在这种情况下，对应的 标识符 $n 就是一个行类型，但是你必须用上面描述的 ALIAS 命令给它取 一个别名．

只有表里面的用户属性是可以访问的，而 OID 或者其它系统属性(因为 该行可以来自一个视图)则不能访问．行类型的字段为char() 或类似的类型继承表的字段大小或精度．

DECLARE
    users_rec users%ROWTYPE;
  user_id users%TYPE;
BEGIN
    user_id := users_rec.user_id;
    ...

create function cs_refresh_one_mv(integer) returns integer as '
   DECLARE
        key ALIAS FOR $1;
        table_data cs_materialized_views%ROWTYPE;
   BEGIN
        SELECT INTO table_data * FROM cs_materialized_views
               WHERE sort_key=key;

        IF NOT FOUND THEN
           RAISE EXCEPTION ''View '' || key || '' not found'';
           RETURN 0;
        END IF;

        -- The mv_name column of cs_materialized_views stores view
        -- names.
 
        TRUNCATE TABLE table_data.mv_name;
        INSERT INTO table_data.mv_name || '' '' || table_data.mv_query;

        return 1;
end;
' LANGUAGE 'plpgsql';

24.2.3.4. RENAME

你可以用 RENAME 修改一个变量，记录或者行的名字． 如果 NEW 或者 OLD 在个触发器过程里被另外一个名字引用， 那么这个东西就很有用．

语法和例子∶

RENAME oldname TO newname;

RENAME id TO user_id;
RENAME this_var TO that_var;

24.2.5.1. 赋值

给一个变量或行/记录赋值用下面方法∶

identifier := expression;

user_id := 20;
tax := subtotal * 0.06;

24.2.5.2. 调用其他函数

所有在 Prostgres 数据库里的函数返回一个值． 因此，通常的调用函数的方法是执行一条 SELECT 查询或者做一个赋值 （导致一个 PL/pgSQL 的内部 SELECT ）．

但是有时候我们对函数结果并不感兴趣． 这种时候，使用 PERFORM 语句．

PERFORM query

PERFORM create_mv(''cs_session_page_requests_mv'',''
     select   session_id, page_id, count(*) as n_hits,
              sum(dwell_time) as dwell_time, count(dwell_time) as dwell_count
     from     cs_fact_table
     group by session_id, page_id '');

24.2.5.3. 执行动态查询

你经常会希望在你的 PL/pgSQL 函数里生成动态查询． 或者你有一些会生成其它函数的函数．PL/pgSQL 为这样的 场合提供 EXECUTE 语句．

EXECUTE query-string

在使用动态查询的时候，你就必须面对 PL/pgSQL 的单引号 逃逸的问题．请参考"从 Oracle PL/SQL 移植"一章里的表 获取详细的解释，这些说明可以帮你省不少功夫．

和所有其它在 PL/pgSQL 里的查询不同，一个由 EXECUTE 语句 运行的 query 在服务器生命期内 并不只准备和保存一次．相反，在该语句每次运行的时候， query 都准备一次． query-string 可以在过程里动态地 生成以便于对各种不同的表和字段进行操作．

来自 SELECT 查询的结果被 EXECUTE 抛弃，并且目前 EXECUTE 里面还不支持 SELECT INTO．因此，从一个动态创建的 SELECT 里抽取结果的唯一方法是使用我们稍后描述的 FOR ... EXECUTE 语句．

一个例子∶

EXECUTE ''UPDATE tbl SET ''
        || quote_ident(fieldname)
        || '' = ''
        || quote_literal(newvalue)
        || '' WHERE ...'';

这个例子显示了函数 quote_ident(TEXT) 和 quote_literal(TEXT) 的使用． 包含字段和表标识符的变量应该传递给函数 quote_ident()． 包含动态查询字串的文本元素的变量应该传递给 quote_literal()．它们俩都会采取合适的 步骤把输入文本包围在单或双引号里以及用任何嵌入的特殊字符返回．

下面是一个大得多的动态查询和 EXECUTE 的例子∶

CREATE FUNCTION cs_update_referrer_type_proc() RETURNS INTEGER AS '
DECLARE
    referrer_keys RECORD;  -- 声明一个用于 FOR 里的一个通用的记录
    a_output varchar(4000);
BEGIN 
    a_output := ''CREATE FUNCTION cs_find_referrer_type(varchar,varchar,varchar) 
                  RETURNS varchar AS '''' 
                     DECLARE 
                         v_host ALIAS FOR $1; 
                         v_domain ALIAS FOR $2; 
                         v_url ALIAS FOR $3; ''; 

    -- 
    -- 请注意这里是如何在一个 FOR 循环中扫描所有的结果的
    -- 这里用的是 FOR <record> 构造
    --

    FOR referrer_keys IN select * from cs_referrer_keys order by try_order LOOP
        a_output := a_output || '' if v_'' || referrer_keys.kind || '' like '''''''''' 
                 || referrer_keys.key_string || '''''''''' then return '''''' 
                 || referrer_keys.referrer_type || ''''''; end if;''; 
    END LOOP; 
  
    a_output := a_output || '' return null; end; '''' language ''''plpgsql'''';''; 
 
    -- 我们可以象下面这样写是因为我们没有替换任何变量
    -- 否则它会出错．看看 PERFORM 获取另外一种运行函数的方法
    
    EXECUTE a_output; 
end; 
' LANGUAGE 'plpgsql';

24.2.5.4. 获取其它结果状态

GET DIAGNOSTICS variable = item [ , ... ]

24.2.5.5. 从函数返回

RETURN expression

表达式的结果将被自动转换成函数返回类型－－象我们在赋值里描述的那样．

24.2.6.1. 条件控制∶ IF 语句

IF 语句让你可以根据某种条件采取动作． PL/pgSQL 有三种形式的 IF∶IF-THEN，IF-THEN-ELSE，IF-THEN-ELSE IF. 注意∶所有 PL/pgSQL 的 IF 语句需要一个对应的 END IF 语句．在 ELSE-IF 语句里，你需要两个∶一个用于第一个 IF 而另外一个 用于第二个(ELSE IF)．

IF-THEN

IF-THEN 语句是 IF 的最简单形式．如果条件为真， 在 THEN 和 END IF 之间的语句将被执行． 否则，将执行跟在 END IF 后面的语句．

IF v_user_id <> 0 THEN
    UPDATE users SET email = v_email WHERE user_id = v_user_id;
END IF;

IF-THEN-ELSE

IF-THEN-ELSE 语句增加了 IF-THEN 的分支，让你可以声明 在条件计算为 FALSE 的时候执行的语句．

IF parentid IS NULL or parentid = ''''
THEN 
    return fullname;
ELSE
    return hp_true_filename(parentid) || ''/'' || fullname;
END IF;


IF v_count > 0 THEN 
    INSERT INTO users_count(count) VALUES(v_count);
    return ''t'';
ELSE 
    return ''f'';
END IF;

IF 语句可以嵌套并且在下面的例子中∶

IF demo_row.sex = ''m'' THEN
  pretty_sex := ''man'';
ELSE
  IF demo_row.sex = ''f'' THEN
    pretty_sex := ''woman'';
  END IF;
END IF;

IF-THEN-ELSE IF

如果你使用 "ELSE IF" 语句，那么你实际上就是在 ELSE 语句中嵌套了一个 IF 语句．因此你需要一个 END IF 语句 给每个嵌套的 IF，另外还要一个给父 IF-ELSE 用．

比如∶

IF demo_row.sex = ''m'' THEN
   pretty_sex := ''man'';
ELSE IF demo_row.sex = ''f'' THEN
        pretty_sex := ''woman'';
     END IF;
END IF;

24.2.6.2. 迭代控制∶ LOOP, WHILE, FOR 和 EXIT

使用 LOOP，WHILE，FOR 和 EXIT 语句，你可以控制你的 PL/pgSQL 程序迭代的流程．

LOOP

[<<label>>]
LOOP
    statements
END LOOP;

一个无条件的循环，你必须用 EXIT 语句明确退出．可选的标签 可以由 EXIT 语句使用，用在嵌套循环中声明应该结束哪一层 循环．

EXIT

EXIT [ label ] [ WHEN expression ];

如果没有给出 label， 那么退出最内层的循环，然后执行跟在 END LOOP 后面的语句． 如果给出 label， 那么它必须是当前或者更高层的嵌套循环块的标签． 然后该命名块或者循环就会终止，而控制落到对应循环/块的 END 语句后面的语句上．

例子∶

LOOP
    -- 一些计算
    IF count > 0 THEN
        EXIT;  -- exit loop
    END IF;
END LOOP;

LOOP
    -- 一些计算
    EXIT WHEN count > 0;
END LOOP;

BEGIN
    -- 一些计算
    IF stocks > 100000 THEN
        EXIT;  -- 非法，不能用 EXIT 退出到 LOOP 外面．
    END IF;
END;

WHILE

利用 WHILE 语句，只要条件表达式为真，那么你就可以 不停在一系列语句上进行循环．

[<<label>>]
WHILE expression LOOP
    statements
END LOOP;

比如∶

WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
    -- 可以在这里做些计算
END LOOP;

WHILE NOT boolean_expression LOOP
    -- 可以在这里做些计算
END LOOP;

FOR

[<<label>>]
FOR name IN [ REVERSE ] expression .. expression LOOP
    statements
END LOOP;

一个对一定范围的整数数值进行迭代的循环．变量 name 会自动作为整数类型创建并且只在 循环里存在．给出范围的上下界的两个表达式知识在进入循环的时候才计算． 迭代步进值总是为一．

一些 FOR 循环的例子(参阅Section 24.2.7 获取在 FOR 循环中迭代所有记录的例子)∶

FOR i IN 1..10 LOOP
  -- 这里可以放一些表达式

    RAISE NOTICE 'i is %',i;
END LOOP;

FOR i IN REVERSE 1..10 LOOP
    -- 这里可以放一些表达式
END LOOP;

24.2.7.1. 声明

一个类型为 RECORD 的变量可以用于不同的选择． 如果一个记录变量里还没有实际的数据行的时候， 访问一条记录或者试图给一个记录字段赋值的动作都会 导致一个运行时错误．它们可以象这样声明∶

name RECORD;

24.2.7.2. 赋值

一次完整的选择的结果赋予一个记录或者一行的做法可以是∶

SELECT  INTO target expressions FROM ...;

如果把一行或者一个变量列表用做目标，那么选出的数值必须准确地 匹配目标的结构，否则就会产生一个运行时错误．FROM 关键字后面可以 跟着人和有效的条件，分组，排序等等可以给 SELECT 语句的东西．

一旦将一个记录或者一行赋予了一个 RECORD 变量， 那么你就可以使用"."(点)表示法访问该记录中的字段∶

DECLARE
    users_rec RECORD;
    full_name varchar;
BEGIN
    SELECT INTO users_rec * FROM users WHERE user_id=3;

  full_name := users_rec.first_name || '' '' || users_rec.last_name;

有一个类型为 boolean 的特殊变量 FOUND， 你可以用它紧跟在 SELECT INTO 后面检查一次赋值是否成功．

SELECT INTO myrec * FROM EMP WHERE empname = myname;
IF NOT FOUND THEN
    RAISE EXCEPTION ''employee % not found'', myname;
END IF;

DECLARE
    users_rec RECORD;
    full_name varchar;
BEGIN
    SELECT INTO users_rec * FROM users WHERE user_id=3;

    IF users_rec.homepage IS NULL THEN
        -- user entered no homepage, return "http://"

        return ''http://'';
    END IF;
END;

24.2.7.3. 遍历所有的记录

利用 FOR 循环的一种特殊类型，你可以遍历一个查询 的记录并且相应处理那些书局．语法如下∶

[<<label>>]
FOR record | row IN select_clause LOOP
    statements
END LOOP;

create function cs_refresh_mviews () returns integer as '
DECLARE
     mviews RECORD;

     -- 如果你用下面这样的方法声明∶
     -- mviews  cs_materialized_views%ROWTYPE;
     -- 那么这个 record 就*只能*用于 cs_materialized_view 表了

BEGIN
     PERFORM cs_log(''Refreshing materialized views...'');

     FOR mviews IN SELECT * FROM cs_materialized_views ORDER BY sort_key LOOP

         -- 现在 "mviews" 里有了一条来自 cs_materialized_views 的记录

         PERFORM cs_log(''Refreshing materialized view '' || mview.mv_name || ''...'');
         TRUNCATE TABLE mview.mv_name;
         INSERT INTO mview.mv_name || '' '' || mview.mv_query;
     END LOOP;

     PERFORM cs_log(''Done refreshing materialized views.'');
     return 1;
end;
' language 'plpgsql';

FOR-IN EXECUTE 语句是遍历所有记录的另外一种方法∶

[<<label>>]
FOR record | row IN EXECUTE text_expression LOOP 
    statements
END LOOP;