第12 章 Greenplum PL/pgSQL 程序语言

Chapter 12 GreenplumPL/pgSQL 程序语言

This section contains an overview ofthe Greenplum Database PL/pgSQL language.

•    About Greenplum Database PL/pgSQL

•    PL/pgSQL 示例

•    References

 

About Greenplum Database PL/pgSQL

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

使用PL / pgSQL，您可以将数据库服务器内部的一组计算和一系列SQL查询分组，从而具有程序语言的强大功能和SQL的易用性。另外，使用PL / pgSQL，您可以使用GreenplumDatabase SQL的所有数据类型，操作符和函数。

PL / pgSQL语言是Oracle PL /SQL的一个子集。 Greenplum数据库PL / pgSQL基于Postgres PL / pgSQL。 Postgres PL / pgSQL文档在http://www.postgresql.org/docs/8.2/static/plpgsql.html

当使用PL / pgSQL函数时，函数属性影响Greenplum数据库如何创建查询计划。

您可以将属性IMMUTABLE，STABLE或VOLATILE指定为LANGUAGE子句的一部分，以对函数类型进行分类。有关创建函数和函数属性的信息，请参阅“Greenplum数据库参考指南”中的CREATEFUNCTION命令。

Greenplum Database SQL Limitations

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

•触发器不受支持

•游标只向前移动（不可滚动）

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

The PL/pgSQL Language

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

[ label ]

[ DECLARE

declarations]

BEGIN

statements

END [ label ];

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

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

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

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

•双破折号（ - ）开始注释，延伸到行的末尾。

•A / *启动一个扩展到下一个* /的块注释。

块注释不能嵌套，但是可以将双破折号注释包含在块注释中，双破折号可以隐藏块注释分隔符/ *和* /。

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

在块前面的声明部分声明的变量在每次输入块时被初始化为默认值，而不是每个函数调用一次。例如多次声明变量的数量quantity：

CREATE FUNCTION testfunc() RETURNS integerAS $$

DECLARE

quantity integer := 30;

BEGIN

RAISE NOTICE 'Quantity here is %',quantity;

-- Quantity here is 30 quantity := 50;

--Create a subblock

DECLARE

quantity integer := 80;

BEGIN

RAISE NOTICE 'Quantity here is %',quantity;

-- Quantity here is 80 END;

RAISE NOTICE 'Quantity here is %',quantity;

-- Quantity here is 50 RETURNquantity;

END;

$$ LANGUAGE plpgsql;

Executing SQL Commands

您可以使用PL / gpSQL语句执行SQL命令，例如EXECUTE，PERFORM和SELECT ...

INTO。 有关PL / gpSQL语句的信息，请参阅

http://www.postgresql.org/docs/8.2/static/plpgsql-statements.html.

注意：EXECUTE语句不支持PL / gpSQL语句SELECT INTO。

 

PL/pgSQL 示例

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

Example: Aliases for Function parameter

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

有两种方法来创建一个别名。 首选的方法是给CREATE FUNCTION命令中的参数命名，例如：

CREATEFUNCTION sales_tax(subtotal real) RETURNS real AS $$

BEGIN

RETURN subtotal * 0.06;

END;

$$ LANGUAGEplpgsql;

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

name ALIAS FOR$n;

This example,creates the same function with the declare syntax.

CREATEFUNCTION sales_tax(real) RETURNS real AS $$

DECLARE

subtotal ALIAS FOR $1;

BEGIN

RETURN subtotal * 0.06;

END;

$$ LANGUAGEplpgsql;

Example: Using the Data Type of a Table Column

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

名称table.column_name%TYPE;

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

my_useridusers.user_id%TYPE;

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

Example: Composite Type Based on a Table Row

以下语法基于表格行声明复合变量：

名字表名%ROWTYPE;

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

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

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

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

CREATETABLE table1 ( f1 text, f2 numeric, f3 integer

)distributedby (f1);

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

INSERT INTOtable1 values

('test1',14.1, 3),

('test2',52.5, 2),

('test3',32.22, 6),

('test4', 12.1, 4) ;

该函数使用基于table1的变量和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;

$$ LANGUAGEplpgsql VOLATILE;

 

注意：上一个函数被分类为VOLATILE函数，因为函数值可能会在单个表扫描中更改。

以下SELECT命令使用该函数。

选择t1_calc（'test1'）;

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

References

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

另请参阅“Greenplum数据库参考指南”中的CREATEFUNCTION命令。

有关内置的Greenplum数据库函数的摘要，请参阅“内置函数摘要”

Greenplum数据库参考指南。 有关使用Greenplum数据库功能的信息，请参阅

“Greenplum数据库管理员指南”中的“查询数据”

有关移植Oracle函数的信息，请参见http://www.postgresql.org/docs/8.2/static/plpgsqlporting。

HTML。 有关安装和使用Greenplum的Oracle兼容性功能的信息

数据库，请参阅“Greenplum数据库实用程序指南”中的“Oracle兼容性功能”。