Greenplum的PL/pgSQL过程语言

本节包含Greenplum数据库的PL/pgSQL语言的概述。

父主题: Greenplum数据库参考指南

关于Greenplum数据库的PL/pgSQL

Greenplum数据库PL/pgSQL是一种可加载的过程语言，默认情况下安装并注册到Greenplum数据库。 用户1可以使用SQL语句，函数和操作符创建用户定义的函数。

使用PL/pgSQL，用户可以在数据库服务器中对一组计算和一系列SQL查询进行分组，从而具有过程语言的强大功能和易于使用的SQL。 此外，使用PL/pgSQL，用户可以使用Greenplum数据库SQL的所有数据类型，操作符和函数。

PL/pgSQL的语言是甲骨文的PL / SQL的一个子集。Greenplum的数据库PL/pgSQL的基于Postgres的PL/pgSQL里，Postgres的PL/pgSQL的 文档在 https://www.postgresql.org/docs/8.3/static/plpgsql.html

当使用PL/pgSQL函数时，函数属性会影响Greenplum Database如何创建查询计划。 用户可以将属性IMMUTABLE, STABLE, 或 VOLATILE 指定为 LANGUAGE 子句的一部分，以对功能属性进行分类, 有关创建函数和函数属性的信息，参阅CREATE FUNCTION命令在 Greenplum数据库参考指南中。

用户可以运行PL/pgSQL代码块作为匿名代码块。请参阅Greenplum数据库参考指南中的DO命令。

Greenplum数据库的SQL限制

当使用Greenplum数据库的PL/pgSQL时，限制包括

  • 不支持触发器
  • 光标仅向前移动（不可滚动）
  • 不支持可更新游标( (UPDATE…WHERE CURRENT OF and DELETE…WHERE CURRENT OF)。

有关Greenplum数据库SQL一致性的信息，请参阅Greenplum数据库参考指南中的Greenplum特性摘要。

PL/pgSQL语言

PL/pgSQL是块结构语言。函数定义的完整文本必须是块。块被定义为:

  1. [ label ]
  2. [ DECLARE
  3.    declarations ]
  4. BEGIN
  5.    statements
  6. END [ label ];

块中的每个声明和每个语句都以分号（;）终止。出现在另一个块中的块必须在END后具有分号， 如上一个块所示。结束函数体的END 不需要分号。

重要提示：不要将PL/pgSQL中的分组语句与用于事务控制的数据库命令的BEGIN 和 END 关键字混淆。 PL/pgSQL的BEGIN和 END 关键字仅用于分组; 它们不开始或结束一个事务。 函数总是在由外部查询建立的事务中执行 - 它们无法启动或提交该事务，因为它们不会执行上下文。 但是，包含 EXCEPTION 子句的PL/pgSQL块有效地形成可以回滚而不影响外部事务的子事务。有关EXCEPTION子句的更多信息，请参阅Postgres文档中有关错误捕获的信息 https://www.postgresql.org/docs/8.3/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING.

所有关键词和标识符可以写成大小写混合。标识符隐式转换为小写，除非用双引号（“）括起来。

用户可以通过以下方式在PL/pgSQL中添加注释：

  • 双击 (—)对行尾的注释进行延伸。

  • / * 标志着块注开始，直到* /出现结束。

    块注释不能被嵌套，但是双击注释可以被封装成块注释并且双击可以隐藏块注释分隔符/ *和* /。

块的语句部分中的任何语句都可以是子块。 子块可用于逻辑分组或将变量本地化为一小组语句。

在块之前的声明部分中声明的变量在每次输入块时都被初始化为其默认值，而不仅仅是每次调用一次。 例如，数次声明变量 quantity :

  1. CREATE FUNCTION testfunc() RETURNS integer AS $$
  2. DECLARE
  3.    quantity integer := 30;
  4. BEGIN
  5.    RAISE NOTICE 'Quantity here is %', quantity;  
  6.    -- Quantity here is 30
  7.    quantity := 50;
  8.    --
  9.    -- Create a subblock
  10.    --
  11.    DECLARE
  12.       quantity integer := 80;
  13.    BEGIN
  14.       RAISE NOTICE 'Quantity here is %', quantity;  
  15.       -- Quantity here is 80
  16.    END;
  17.    RAISE NOTICE 'Quantity here is %', quantity; 
  18.    -- Quantity here is 50
  19.    RETURN quantity;
  20. END;
  21. $$ LANGUAGE plpgsql;

执行SQL命令

用户可以使用PL/pgSQL语句像 EXECUTE, PERFORM, 和 SELECT … INTO。 有关PL/pgSQL语句的信息，另见https://www.postgresql.org/docs/8.3/static/plpgsql-statements.html.

注意： PL/pgSQL语句 SELECT INTO 不被支持在 EXECUTE 语句中。

PL/pgSQL计划缓存

一个PL/pgSQL函数的易变性分类对Greenplum数据库如何缓存引用该函数的计划有影响。关于Greenplum数据库函数易变性分类的计划缓存考虑，请参考Greenplum数据库管理员指南中的函数易变性和计划缓冲。

当PL/pgSQL函数在数据库会话中首次执行时，PL/pgSQL解释器解析函数的SQL表达式和命令。解释器会为首次执行的函数中的每个表达式和SQL命令创建一个事先准备的执行计划。PL/pgSQL解释器在数据库连接的使用期间重新使用特定表达式和SQL命令的执行计划。虽然此重用显著减少了解析和生成计划所需的总时间，但是在函数此部分运行之前，不能检测到特定表达式或命令中的错误。

如果对查询中使用的任何关系有任何模式更改，或者在查询中使用的任何用户自定义的函数被重新定义，Greenplum数据库将自动重新计划保存的查询计划。这使得在大多数情况下，重新使用准备好的计划变成了透明的。

用户在PL/pgSQL函数中使用的SQL命令必须在每次执行时引用相同的表和列。用户不能将参数用作SQL命令中的表或列的名称。

PL/pgSQL为用户调用多态函数的实际参数类型的每个组合缓存单独的查询计划，以确保数据类型差异不会导致意外故障。

参考PostgreSQL的计划缓存文档获取有关PL/pgSQL语言中计划缓存注意事项的详细讨论。

PL/pgSQL 示例

以下是PL/pgSQL用户自定义函数的示例。

示例：函数参数别名

传递给函数的参数以 $1, $2等标识符命名。相应的，可以为 $n 参数名称声明别名以提高可读性，然后可以使用别名或数字标识符来引用参数值。

创建别名有两种方法。 例如，首选方法是在 CREATE FUNCTION命令中为参数命名：

  1. CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$
  2. BEGIN
  3.    RETURN subtotal * 0.06;
  4. END;
  5. $$ LANGUAGE plpgsql;

用户还可以使用声明语法显式声明别名:

  1. name ALIAS FOR $n;

此示例使用 DECLARE 语法创建相同的函数。

  1. CREATE FUNCTION sales_tax(real) RETURNS real AS $$
  2. DECLARE
  3.     subtotal ALIAS FOR $1;
  4. BEGIN
  5.     RETURN subtotal * 0.06;
  6. END;
  7. $$ LANGUAGE plpgsql;

示例：使用表列的数据类型

当声明变量时，可以使用 %TYPE 来指定变量或表列的数据类型。 这是声明具有表列数据类型的变量的语法：

  1. name table.column_name%TYPE;

用户可以使用它来声明将保存数据库值的变量。 例如，如果用户有一个名为 user_id 的列在用户的 users 表中。 要声明与users.user_id列具有相同数据类型 变量my_userid :

  1. my_userid users.user_id%TYPE;

%TYPE 在多态函数中特别有价值，因为内部变量所需的数据类型可能会从一个调用变为下一个调用。 可以通过将%TYPE 应用于函数的参数或结果占位符来创建适当的变量。

示例：基于表行的复合类型

以下语法根据表行声明一个复合变量:

  1. name table_name%ROWTYPE;

这样一个行变量可以容纳整行SELECT或 FOR 查询结果，只要该查询列集合与声明的变量类型相匹配即可。 行值的各个字段使用通用的点符号进行访问，例如 rowvar.column.

函数的参数可以是复合类型（完整的表行）。 在这种情况下，对应的标识符 $n 将是行变量，可以从中选择字段， 例如 $1.user_id.

只有表行的用户定义的列可以在行类型变量中访问，而不是OID或其他系统列。 行类型的字段会继承数据类型（如char(n))的表的字段大小和精度。

下一个示例函数使用行变量复合类型。 创建该函数之前，请创建该函数使用该命令的表。 command.

  1. CREATE TABLE table1 (
  2.   f1 text,
  3.   f2 numeric,
  4.   f3 integer
  5. ) distributed by (f1);

该INSERT 数据添加到表中。

  1. INSERT INTO table1 values 
  2.  ('test1', 14.1, 3),
  3.  ('test2', 52.5, 2),
  4.  ('test3', 32.22, 6),
  5.  ('test4', 12.1, 4) ;

这个函数使用一个变量ROWTYPE 基于复合变量 table1.

  1. CREATE OR REPLACE FUNCTION t1_calc( name text) RETURNS integer 
  2. AS $$ 
  3. DECLARE
  4.     t1_row   table1%ROWTYPE;
  5.     calc_int table1.f3%TYPE;
  6. BEGIN
  7.     SELECT * INTO t1_row FROM table1 WHERE table1.f1 = $1 ;
  8.     calc_int = (t1_row.f2 * t1_row.f3)::integer ;
  9.     RETURN calc_int ;
  10. END;
  11. $$ LANGUAGE plpgsql VOLATILE;

注解： 上一个函数被归类为 VOLATILE函数是 因为在单个表的扫描中函数值可能会改变。

下列SELECT 命令使用该功能。

  1. select t1_calc( 'test1' );

注解：示例PL/pgSQL函数使用SELECT 与 INTO 子句。 它与SQL命令 SELECT INTO不同。如果要从PL/pgSQL函数中的SELECT 结果中创建表， 请使用SQL命令CREATE TABLE AS。

示例：使用可变数量的参数

只要所有可选参数的数据类型相同，就可以声明一个PL/pgSQL函数来接受可变数量的参数。 用户必须将函数的最后一个参数标记为 VARIADIC 并使用数组类型声明参数。 用户可以将包含 VARIADIC 参数的函数引用为可变函数。

例如，这个可变函数返回数值变量数组的最小值:

  1. CREATE FUNCTION mleast (VARIADIC numeric[]) 
  2.     RETURNS numeric AS $$
  3.   DECLARE minval numeric;
  4.   BEGIN
  5.     SELECT min($1[i]) FROM generate_subscripts( $1, 1) g(i) INTO minval;
  6.     RETURN minval;
  7. END;
  8. $$ LANGUAGE plpgsql;
  9. CREATE FUNCTION
  11. SELECT mleast(10, -1, 5, 4.4);
  12.  mleast
  13. --------
  14.      -1
  15. (1 row)

实际上，VARIADIC位置之外的所有实际参数都被收集成一维数组。

用户可以将已构建的数组传递给可变函数。当用户想要在可变函数之间传递数组时，这是特别有用的。在函数调用中指定 VARIADIC如下所示:

  1. SELECT mleast(VARIADIC ARRAY[10, -1, 5, 4.4]);

这样可以防止PL/pgSQL将函数的可变参数扩展到其元素类型中。

示例：使用默认参数值

用户可以使用默认值声明一些或所有输入参数的PL/pgSQL的函数。无论调用的函数是否少于声明的参数的数量，每当调用的函数少于声明的参数数时，就会插入默认值。因为参数只能从实际参数列表的末尾删除，所以必须在使用默认值定义的参数之后为所有参数提供默认值。

例如:

  1. CREATE FUNCTION use_default_args(a int, b int DEFAULT 2, c int DEFAULT 3)
  2.     RETURNS int AS $$
  3. DECLARE
  4.     sum int;
  5. BEGIN
  6.     sum := $1 + $2 + $3;
  7.     RETURN sum;
  8. END;
  9. $$ LANGUAGE plpgsql;
  11. SELECT use_default_args(10, 20, 30);
  12.  use_default_args
  13. ------------------
  14.                60
  15. (1 row)
  17. SELECT use_default_args(10, 20);
  18.  use_default_args
  19. ------------------
  20.                33
  21. (1 row)
  23. SELECT use_default_args(10);
  24.  use_default_args
  25. ------------------
  26.                15
  27. (1 row)

用户还可以使用\= 符号代替关键字DEFAULT.

示例：使用多态数据类型

PL/pgSQL支持多态 anyelement, anyarray, anyenum, 和 anynonarray 类型。 使用这些类型，用户可以创建一个运行在多种数据类型上的PL/pgSQL函数。参阅 Greenplum数据库数据类型 有关Greenplum数据库中多态类型支持的更多信息。

当PL/pgSQL函数的返回类型被声明为多态类型时，将创建一个名为 $0 的特殊参数。 数据类型 $0 识别从实际输入类型推导的函数的返回类型。

在这个例子中，用户创建一个返回两个值的和的多态函数：

  1. CREATE FUNCTION add_two_values(v1 anyelement,v2 anyelement)
  2.     RETURNS anyelement AS $$ 
  3. DECLARE 
  4.     sum ALIAS FOR $0;
  5. BEGIN
  6.     sum := v1 + v2;
  7.     RETURN sum;
  8. END;
  9. $$ LANGUAGE plpgsql;

执行提供整数输入值的add_two_values()：

  1. SELECT add_two_values(1, 2);
  2.  add_two_values
  3. ----------------
  4.               3
  5. (1 row)

add_two_values()的返回类型是整数，也是输入参数的类型。现在执行提供浮点输入值的add_two_values()：

  1. SELECT add_two_values (1.1, 2.2);
  2.  add_two_values
  3. ----------------
  4.             3.3
  5. (1 row)

在这种情况下 add_two_values() 的返回类型为float。

用户还可以在多态函数中指定VARIADIC 参数。

示例：匿名块

此示例将使用 DO命令作为匿名块执行上一个示例中的函数。在该示例中，匿名块从临时表中检索输入值。

  1. CREATE TEMP TABLE list AS VALUES ('test1') DISTRIBUTED RANDOMLY;
  3. DO $$ 
  4. DECLARE
  5.     t1_row   table1%ROWTYPE;
  6.     calc_int table1.f3%TYPE;
  7. BEGIN
  8.     SELECT * INTO t1_row FROM table1, list WHERE table1.f1 = list.column1 ;
  9.     calc_int = (t1_row.f2 * t1_row.f3)::integer ;
  10.     RAISE NOTICE 'calculated value is %', calc_int ;
  11. END $$ LANGUAGE plpgsql ;

参考

关于PL/pgSQL的Postgres文档在 https://www.postgresql.org/docs/8.3/static/plpgsql.html

另请参阅CREATE FUNCTION 命令在Greenplum数据库参考指南中。

有关内置Greenplum数据库功能的摘要， 参阅 内建函数摘要在Greenplum数据库Reference Guide. 有关使用Greenplum数据库功能的信息参阅 “查询数据” 在Greenplum数据库管理员指南

有关移植Oracle功能的信息， 参考https://www.postgresql.org/docs/8.3/static/plpgsql-porting.html. 有关使用Greenplum数据库安装和使用Oracle兼容性功能的信息，请参阅 Greenplum数据库Utility Guide中的“Oracle兼容性功能”。