Greenplum PL/pgSQL过程语言

Greenplum PL/pgSQL过程语言

本节概述了Greenplum数据库PL/pgSQL语言。

关于Greenplum数据库PL/pgSQL
PL/pgSQL计划缓存
PL/pgSQL示例
参考

关于Greenplum数据库PL/pgSQL

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

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

PL/pgSQL语言是Oracle PL/SQL的子集。 Greenplum数据库PL/pgSQL基于Postgres PL/pgSQL。 Postgres PL/pgSQL文档位于 https://www.postgresql.org/docs/9.4/plpgsql.html

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

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

Greenplum数据库SQL限制

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

不支持触发器
游标只向前移动（不可滚动）
不支持可更新游标（UPDATE…WHERE CURRENT OF和DELETE…WHERE CURRENT OF）。

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

PL/pgSQL语言

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

[ label ]
[ DECLARE
   declarations ]
BEGIN
   statements
END [ label ];

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

仅当您要识别要在EXIT语句中使用的块或限定块中声明的变量的名称时，才需要label。如果在END之后提供label，则它必须与块开头的label匹配。

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

关键字不区分大小写。除非双引号，否则标识符将隐式转换为小写，就像在普通SQL命令中一样。

PL/pgSQL代码中的注释与普通SQL中的注释相同：

双短划线(—)开始一条延伸到该行末尾的注释。
/* 启动一个块注释，扩展到匹配的 */。

阻止评论嵌套。

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

在子块中声明的变量在子块的持续时间内屏蔽外部块的任何类似命名的变量。如果使用块的标签限定其名称，则可以访问外部变量。例如，此函数多次声明一个名为quantity的变量：

CREATE FUNCTION testfunc() RETURNS integer AS $$
<< outerblock >>
DECLARE
   quantity integer := 30;
BEGIN
   RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 30
   quantity := 50;
   --
   -- Create a subblock
   --
   DECLARE
      quantity integer := 80;
   BEGIN
      RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 80
      RAISE NOTICE 'Outer quantity here is %', outerblock.quantity;  -- Prints 50
   END;
   RAISE NOTICE 'Quantity here is %', quantity;  -- Prints 50
   RETURN quantity;
END;
$$ LANGUAGE plpgsql;

执行SQL命令

您可以使用PL/pgSQL语句执行SQL命令，例如EXECUTE，PERFORM和SELECT … INTO。有关PL/pgSQL语句的信息，请参阅 https://www.postgresql.org/docs/9.4/plpgsql-statements.html.

Note: EXECUTE语句不支持PL/pgSQL语句SELECT INTO。

PL/pgSQL计划缓存

PL/pgSQL函数的波动率分类对Greenplum数据库如何缓存引用该函数的计划有影响。有关Greenplum数据库函数波动率类别的计划缓存注意事项的信息，请参阅Greenplum数据库管理员指南中的函数波动率和计划缓存。

当PL/pgSQL函数在数据库会话中第一次执行时，PL/pgSQL解释器会解析函数的SQL表达式和命令。解释器创建一个准备好的执行计划，因为每个表达式和SQL命令首先在函数中执行。 PL/pgSQL解释器在数据库连接的生命周期中重用特定表达式和SQL命令的执行计划。虽然这种重用大大减少了解析和生成计划所需的总时间，但是直到执行该部分函数的运行时才能检测到特定表达式或命令中的错误。

如果对查询中使用的任何关系进行任何架构更改，或者重新定义查询中使用的任何用户定义函数，Greenplum数据库将自动重新规划已保存的查询计划。这使得在大多数情况下重复使用准备好的计划是透明的。

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

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

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

PL/pgSQL示例

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

示例：函数参数的别名

传递给函数的参数使用诸如$1，$2之类的标识符命名。（可选）可以为$n参数名称声明别名，以提高可读性。然后可以使用别名或数字标识符来引用参数值。

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

CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$
BEGIN
   RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;

您还可以使用声明语法显式声明别名：

name ALIAS FOR $n;

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

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

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

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

name table.column_name%TYPE;

您可以使用%TYPE构造来声明将保存数据库值的变量。例如，假设您的users表中有一个名为user_id的列。声明名为my_userid的变量，其数据类型与users.user_id列相同：

my_userid users.user_id%TYPE;

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

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

复合类型的变量称为行变量。以下语法基于表行声明复合变量：

name table_name%ROWTYPE;

这样的行变量可以保存SELECT或FOR查询结果的整行，只要该查询的列集与变量的声明类型匹配即可。使用通常的点表示法访问行值的各个字段，例如rowvar.column。

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

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

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

CREATE TABLE table1 (
  f1 text,
  f2 numeric,
  f3 integer
) distributed by (f1);

此INSERT命令将数据添加到表中。

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

此函数使用基于table1的列%TYPE变量和%ROWTYPE复合变量。

CREATE OR REPLACE FUNCTION t1_calc( name text) RETURNS integer 
AS $$ 
DECLARE
    t1_row   table1%ROWTYPE;
    calc_int table1.f3%TYPE;
BEGIN
    SELECT * INTO t1_row FROM table1 WHERE table1.f1 = $1 ;
    calc_int = (t1_row.f2 * t1_row.f3)::integer ;
    RETURN calc_int ;
END;
$$ LANGUAGE plpgsql VOLATILE;

Note: 前一个函数被归类为VOLATILE函数，因为函数值可能在单个表扫描中发生变化。

以下SELECT命令使用该函数。

select t1_calc( 'test1' );

Note: 示例PL/pgSQL函数将SELECT与INTO子句一起使用。它与SQL命令SELECT INTO不同。如果要从PL/pgSQL函数内的SELECT结果创建表，请使用SQL命令CREATE TABLE AS。

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

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

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

CREATE FUNCTION mleast (VARIADIC numeric[]) 
    RETURNS numeric AS $$
  DECLARE minval numeric;
  BEGIN
    SELECT min($1[i]) FROM generate_subscripts( $1, 1) g(i) INTO minval;
    RETURN minval;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION
SELECT mleast(10, -1, 5, 4.4);
 mleast
--------
     -1
(1 row)

实际上，VARIADIC位置或其以外的所有实际参数都被收集到一维数组中。

您可以将已构造的数组传递给可变参数函数。当您想要在可变参数函数之间传递数组时，这尤其有用。在函数调用中指定VARIADIC，如下所示：

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

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

示例：使用默认参数值

您可以使用某些或所有输入参数的默认值声明PL/pgSQL函数。只要调用的函数少于声明的参数个数，就会插入默认值。因为参数只能从实际参数列表的末尾省略，所以必须在使用默认值定义的参数之后为所有参数提供默认值。

示例：

CREATE FUNCTION use_default_args(a int, b int DEFAULT 2, c int DEFAULT 3)
    RETURNS int AS $$
DECLARE
    sum int;
BEGIN
    sum := $1 + $2 + $3;
    RETURN sum;
END;
$$ LANGUAGE plpgsql;
SELECT use_default_args(10, 20, 30);
 use_default_args
------------------
               60
(1 row)
SELECT use_default_args(10, 20);
 use_default_args
------------------
               33
(1 row)
SELECT use_default_args(10);
 use_default_args
------------------
               15
(1 row)

您也可以使用\=符号代替关键字DEFAULT。

示例：使用多态数据类型

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

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

在此示例中，您将创建一个多态函数，该函数返回两个值的总和：

CREATE FUNCTION add_two_values(v1 anyelement,v2 anyelement)
    RETURNS anyelement AS $$ 
DECLARE 
    sum ALIAS FOR $0;
BEGIN
    sum := v1 + v2;
    RETURN sum;
END;
$$ LANGUAGE plpgsql;

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

SELECT add_two_values(1, 2);
 add_two_values
----------------
              3
(1 row)

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

SELECT add_two_values (1.1, 2.2);
 add_two_values
----------------
            3.3
(1 row)

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

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

示例：匿名块

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

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

参考

关于PL/pgSQL的PostgreSQL文档是在https://www.postgresql.org/docs/9.4/plpgsql.html

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

有关内置Greenplum数据库函数的摘要，请参阅Greenplum数据库参考指南中的内置函数摘要。有关使用Greenplum数据库函数的信息，请参阅Greenplum数据库管理员指南中的“查询数据”

有关移植Oracle函数的信息，请参阅https://www.postgresql.org/docs/9.4/plpgsql-porting.html。有关在Greenplum数据库中安装和使用Oracle兼容性函数的信息，请参阅Greenplum数据库实用程序指南中的“Oracle兼容性函数”。