这是本节的多页打印视图。点击此处打印.

存储过程

1: PL/vSQL

1.1: 支持的类型
1.2: 范围和结构
1.3: 嵌入式 SQL
1.4: 控制流
1.5: 错误和诊断
1.6: 游标
1.7: PL/pgSQL 到 PL/vSQL 迁移指南

2: 形参模式
3: 执行存储过程
4: 更改存储过程
5: 存储过程：用例和示例

您可以将复杂的数据库任务和例程压缩为存储过程。与外部过程不同，存储过程在数据库内部存在且可以从数据库内部执行；这使它们可以直接与您的数据库进行通信和交互，以执行维护、执行查询和更新表。

最佳实践

许多其他数据库针对专重于频繁事务的在线事务处理 (OLTP) 进行了优化。相比之下，Vertica 针对在线分析处理 (OLAP) 进行了优化，该处理专注于存储和分析大量数据，并对针对该数据的最复杂查询提供最快的响应。

这种架构差异意味着 Vertica 中存储过程的推荐用例和最佳实践与其他数据库中的存储过程略有不同。

虽然面向 OLTP 的数据库中的存储过程通常用于执行小型事务，但应使用 Vertica 等面向 OLAP 的数据库中的存储过程来增强分析工作负载。Vertica 可以处理孤立的事务，但频繁的小事务可能会影响性能。

一些推荐的 Vertica 存储过程用例包括信息生命周期管理 (ILM) 活动（例如提取、转换和加载 (ETL)）以及用于机器学习等任务的数据准备。例如：

根据生命期交换分区
在生命期结束时导出数据并删除分区
保存机器学习模型的输入、输出和元数据 — 运行模型的人员、模型的版本、模型运行次数以及接收结果的人员

Vertica 中的存储过程还可以对需要比调用者更高权限的对象进行操作。可选参数允许过程使用定义者的权限运行，允许调用者以受控方式执行敏感操作。

已知问题和解决方法

您不能使用 PERFORM CREATE FUNCTION 创建 SQL 宏。

解决方法
使用 EXECUTE 在存储过程中创建 SQL 宏：

CREATE PROCEDURE procedure_name()
LANGUAGE PLvSQL AS $$
BEGIN
    EXECUTE 'macro';
end;
$$;

其中 macro 是 SQL 宏的创建语句。例如，此过程创建 argmax 宏：

=> CREATE PROCEDURE make_argmax() LANGUAGE PLvSQL AS $$
BEGIN
    EXECUTE
        'CREATE FUNCTION
        argmax(x int) RETURN int AS
        BEGIN
            RETURN (CASE WHEN (x IS NOT NULL) THEN x ELSE 0 END);
        END';
END;
$$;

嵌入式 SQL 语句中的非错误异常不会被报告。
DECIMAL、NUMERIC、NUMBER、MONEY 和 UUID 数据类型尚不能用于实参。
游标应该在声明时捕获变量上下文，但它们目前在打开时捕获变量上下文。

对具有键约束的表的 DML 查询还不能返回值。

解决方法
不要使用以下语句：

DO $$
DECLARE
    y int;
BEGIN
    y := UPDATE tbl WHERE col1 = 3 SET col2 = 4;
END;
$$

而应使用 SELECT 检查 DML 查询结果：

DO $$
DECLARE
    y int;
BEGIN
    y := SELECT COUNT(*) FROM tbl WHERE col1 = 3;
    PERFORM UPDATE tbl SET col2 = 4 WHERE col1 = 3;
END;
$$;

1 - PL/vSQL

PL/vSQL 是一种功能强大且富有表现力的过程语言，用于创建可重用过程、操作数据和简化其他复杂的数据库例程。

Vertica PL/vSQL 在很大程度上与 PostgreSQL PL/pgSQL 兼容，语义差异很小。有关将 PostgreSQL PL/pgSQL 存储过程迁移到 Vertica 的详细信息，请参阅 PL/pgSQL 到 PL/vSQL 迁移指南。

有关 PL/vSQL 用法的实际示例，请参阅存储过程：用例和示例。

1.1 - 支持的类型

Vertica PL/vSQL 支持非复杂数据类型。以下类型仅作为变量受支持，而不能作为实参：

DECIMAL
NUMERIC
NUMBER
MONEY
UUID

1.2 - 范围和结构

PL/vSQL 使用块范围，其中块具有以下结构：

[ <<label>> ]
[ DECLARE
    declarations ]
BEGIN
    statements
    ...
END [ label ];

声明

DECLARE 块中的变量 declarations 结构如下：

variable_name [ CONSTANT ] data_type [ NOT NULL ] [:= { expression | statement } ];

别名

别名是同一变量的备选名称。变量的别名不是副本，对任一引用的任何更改都会影响同一基础变量。

new_name ALIAS FOR variable;

在下方示例中，标识符 y 现在是变量 x 的别名，对 y 的更改反映在 x 中。

DO $$
DECLARE
    x int := 3;
    y ALIAS FOR x;
BEGIN
    y := 5; -- since y refers to x, x = 5
    RAISE INFO 'x = %, y = %', x, y;
END;
$$;

INFO 2005:  x = 5, y = 5

BEGIN 和嵌套块

BEGIN 包含 statements。statement 定义为 PL/vSQL 的行或块。

内部块中声明的变量遮蔽外部块中声明的变量。要明确指定特定块中的变量，您可以使用 label（不区分大小写）命名块，然后使用以下语句引用该块中声明的变量：

label.variable_name

例如，由于存在遮蔽，在内部块中指定变量 x 会隐式引用 inner_block.x 而不引用 outer_block.x：


<<outer_block>>
DECLARE
    x int;
BEGIN
    <<inner_block>>
    DECLARE
        x int;
    BEGIN
        x := 1000; -- implicitly specifies x in inner_block because of shadowing
        OUTER_BLOCK.x := 0; -- specifies x in outer_block; labels are case-insensitive
    END inner_block;
END outer_block;

NULL 语句

NULL 语句不起任何作用。它可以用作占位符语句，或者是一种显示代码块有意为空的方式。例如：

DO $$
BEGIN
    NULL;
END;
$$

备注

注释语法如下。您不能嵌套注释。

-- single-line comment

/* multi-line
comment
*/

1.3 - 嵌入式 SQL

您可以在存储过程中嵌入和执行 SQL 语句和表达式。

赋值

要保存表达式的值或返回的值，您可以将其赋值给变量：

variable_name := expression;
variable_name := statement;

例如，此过程将 3 赋值给 i，将 'message' 赋值给 v。

=> CREATE PROCEDURE performless_assignment() LANGUAGE PLvSQL AS $$
DECLARE
    i int;
    v varchar;
BEGIN
    i := SELECT 3;
    v := 'message';
END;
$$;

如果查询未返回任何行或者返回多个行，则此类赋值失败。要返回多个行，请使用 LIMIT 或截断赋值：

=> SELECT * FROM t1;
 b
---
 t
 f
 f
(3 rows)


=> CREATE PROCEDURE more_than_one_row() LANGUAGE PLvSQL as $$
DECLARE
    x boolean;
BEGIN
    x := SELECT * FROM t1;
END;
$$;
CREATE PROCEDURE

=> CALL more_than_one_row();
ERROR 10332:  Query returned multiple rows where 1 was expected

截断赋值

截断赋值将查询返回的第一行存储在变量中。行排序是不确定的，除非您指定了 ORDER BY 子句：

variable_name <- expression;
variable_name <- statement;

以下过程获取指定的查询所返回结果的第一行，并将其赋值给 x：

=> CREATE PROCEDURE truncating_assignment() LANGUAGE PLvSQL AS $$
DECLARE
    x boolean;
BEGIN
    x <- SELECT * FROM t1 ORDER BY b DESC; -- x is now assigned the first row returned by the SELECT query
END;
$$;

PERFORM

PERFORM 关键字运行 SQL 语句或表达式，放弃返回的结果。

PERFORM statement;
PERFORM expression;

例如，此过程将一个值插入表。INSERT 返回插入的行数，因此您必须将其与 PERFORM 配对。

=> DO $$
BEGIN
    PERFORM INSERT INTO coordinates VALUES(1,2,3);
END;
$$;

注意

如果 SQL 语句未返回任何行，或者您未将返回值赋值给变量，则必须使用 PERFORM。

EXECUTE

EXECUTE 允许您在执行期间动态构造 SQL 查询：

EXECUTE command_expression [ USING expression [, ... ] ];

command_expression 是一个 SQL 表达式，它可以将 PL/vSQL 变量和求值引向字符串字面量。该字符串字面量作为 SQL 语句执行，$1、$2、... 替换为对应的表达式。

使用 PL/vSQL 变量构造查询可能有危险，会将您的系统暴露给 SQL 注入，因此请使用 QUOTE_IDENT、QUOTE_LITERAL 和 QUOTE_NULLABLE 对其进行包装。

以下过程使用 WHERE 子句来构造查询：

DO $$
BEGIN
    EXECUTE 'SELECT * FROM t1 WHERE x = $1' USING 10; -- becomes WHERE x = 10
END;
$$;

以下过程使用 username 和 password 实参中的密码创建用户。由于构造的 CREATE USER 语句使用变量，请使用函数 QUOTE_IDENT 和 QUOTE_LITERAL，用 || 连接它们。

=> CREATE PROCEDURE create_user(username varchar, password varchar) LANGUAGE PLvSQL AS $$
BEGIN
    EXECUTE 'CREATE USER ' || QUOTE_IDENT(username) || ' IDENTIFIED BY ' || QUOTE_LITERAL(password);
END;
$$;

EXECUTE 是 SQL 语句，因此您可以将其赋值给变量或与 PERFORM 配对：

variable_name:= EXECUTE command_expression;
PERFORM EXECUTE command_expression;

FOUND（特殊变量）

特殊布尔变量 FOUND 初始化为 false，然后根据是否发生以下操作确定是赋值为 true 还是 false：

语句（不是表达式）返回具有非零行数的结果，或者
FOR 循环至少迭代一次

您可以使用 FOUND 区分返回 NULL 和返回 0 行。

在过程实参的作用域与其定义的最外层块之间存在特殊变量。这意味着：

特殊变量影射过程实参
在存储过程主体中声明的变量将影射特殊变量

下面的过程演示 FOUND 如何更改。在 SELECT 语句之前，FOUND 为 false；在 SELECT 语句之后，FOUND 为 true。

=> DO $$
BEGIN
    RAISE NOTICE 'Before SELECT, FOUND = %', FOUND;
    PERFORM SELECT 1; -- SELECT returns 1
    RAISE NOTICE 'After SELECT, FOUND = %', FOUND;
END;
$$;

NOTICE 2005:  Before SELECT, FOUND = f
NOTICE 2005:  After SELECT, FOUND = t

同样，UPDATE、DELETE 和 INSERT 返回受影响的行数。在下一示例中，UPDATE 不更改任何行，但是返回 0 值以表示没有任何行受影响，因此 FOUND 设置为 true：

=> SELECT * t1;
  a  |  b
-----+-----
 100 | abc
(1 row)

DO $$
BEGIN
    PERFORM UPDATE t1 SET a=200 WHERE b='efg'; -- no rows affected since b doesn't contain 'efg'
    RAISE INFO 'FOUND = %', FOUND;
END;
$$;

INFO 2005:  FOUND = t

FOUND 开始时设置为 false，如果循环至少迭代一次，则设为 true：

=> DO $$
BEGIN
    RAISE NOTICE 'FOUND = %', FOUND;
    FOR i IN RANGE 1..1 LOOP -- RANGE is inclusive, so iterates once
        RAISE NOTICE 'i = %', i;
    END LOOP;
    RAISE NOTICE 'FOUND = %', FOUND;
END;
$$;

NOTICE 2005:  FOUND = f
NOTICE 2005:  FOUND = t

DO $$
BEGIN
    RAISE NOTICE 'FOUND = %', FOUND;
    FOR i IN RANGE 1..0 LOOP
        RAISE NOTICE 'i = %', i;
    END LOOP;
    RAISE NOTICE 'FOUND = %', FOUND;
END;
$$;

NOTICE 2005:  FOUND = f
NOTICE 2005:  FOUND = f

1.4 - 控制流

控制流构造使您可以控制语句块应运行的次数和条件。

条件

IF/ELSIF/ELSE

IF/ELSIF/ELSE 语句允许您根据指定的条件执行不同的操作。

IF condition_1 THEN
  statement_1;
[ ELSIF condition_2 THEN
  statement_2 ]
...
[ ELSE
  statement_n; ]
END IF;

Vertica 将每个条件作为布尔值依次求值，直到找到一个为 true 的条件，然后执行语句块并退出 IF 语句。如果任何条件均不为 true，则执行 ELSE 块（如果存在）。


IF i = 3 THEN...
ELSIF 0 THEN...
ELSIF true THEN...
ELSIF x <= 4 OR x >= 10 THEN...
ELSIF y = 'this' AND z = 'THAT' THEN...

例如，此过程演示简单的 IF...ELSE 分支。由于 b 声明为 true，Vertica 执行第一个分支。

=> DO LANGUAGE PLvSQL $$
DECLARE
    b bool := true;
BEGIN
    IF b THEN
        RAISE NOTICE 'true branch';
    ELSE
        RAISE NOTICE 'false branch';
    END IF;
END;
$$;

NOTICE 2005:  true branch

CASE

CASE 表达式通常比 IF…ELSE 链更可读。在执行 CASE 表达式的分支之后，控制将跳转到封闭 END CASE 后的语句。

PL/vSQL CASE 表达式比 SQL Case 表达式更灵活、更强大，但后者更高效；在均可使用的情况下，您应该更偏好 SQL Case 表达式。

CASE [ search_expression ]
   WHEN expression_1 [, expression_2, ...] THEN
      when_statements
  [ ... ]
  [ ELSE
      else_statements ]
END CASE;

search_expression 求值一次，然后从上到下与每个分支中的 expression_n 进行比较。如果 search_expression 与给定 expression_n 相等，则 Vertica 针对 expression_n 执行 WHEN 块，并退出 CASE 块。如果找不到匹配的表达式，则执行 ELSE 分支（如果存在）。

Case 表达式必须具有匹配的 Case 或 ELSE 分支，否则 Vertica 将引发 Case_NOT_FOUND 错误。

如果忽略 search_expression，则其值默认为 true。

例如，此过程进行游戏 FizzBuzz。如果实参可被 3 整除，则输出 Fizz；如果实参可被 5 整除，则输出 Buzz；如果实参可被 3 和 5 整除，则输出 FizzBuzz。

=> CREATE PROCEDURE fizzbuzz(IN x int) LANGUAGE PLvSQL AS $$
DECLARE
    fizz int := x % 3;
    buzz int := x % 5;
BEGIN
    CASE fizz
        WHEN 0 THEN -- if fizz = 0, execute WHEN block
            CASE buzz
                WHEN 0 THEN -- if buzz = 0, execute WHEN block
                    RAISE INFO 'FizzBuzz';
                ELSE -- if buzz != 0, execute WHEN block
                    RAISE INFO 'Fizz';
            END CASE;
        ELSE -- if fizz != 0, execute ELSE block
            CASE buzz
                WHEN 0 THEN
                    RAISE INFO 'Buzz';
                ELSE
                    RAISE INFO '';
            END CASE;
    END CASE;
END;
$$;

=> CALL fizzbuzz(3);
INFO 2005:  Fizz

=> CALL fizzbuzz(5);
INFO 2005:  Buzz

=> CALL fizzbuzz(15);
INFO 2005:  FizzBuzz

循环

循环重复执行代码块，直到满足给定条件。

WHILE

WHILE 循环检查给定条件，如果条件为 true，则执行循环体，然后再次检查条件：如果为 true，循环体再次执行；如果为 false，则控制跳到循环体的末尾。

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

例如，此过程计算实参的阶乘：

=> CREATE PROCEDURE factorialSP(input int) LANGUAGE PLvSQL AS $$
DECLARE
    i int := 1;
    output int := 1;
BEGIN
    WHILE i <= input loop
        output := output * i;
        i := i + 1;
    END LOOP;
    RAISE INFO '%! = %', input, output;
END;
$$;

=> CALL factorialSP(5);
INFO 2005:  5! = 120

LOOP

此循环类型等同于 WHILE true，仅在遇到 RETURN 或 EXIT 语句或引发异常时终止。

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

例如，此过程输出从 counter 到 upper_bound（含）的整数：

DO $$
DECLARE
    counter int := 1;
    upper_bound int := 3;
BEGIN
    LOOP
        RAISE INFO '%', counter;
        IF counter >= upper_bound THEN
            RETURN;
        END IF;
        counter := counter + 1;
    END LOOP;
END;
$$;

INFO 2005:  1
INFO 2005:  2
INFO 2005:  3

FOR

FOR 循环在集合上迭代，集合可以是整体范围、查询或游标。

如果 FOR 循环至少迭代一次，则在循环结束后，特殊的 FOUND 变量设置为 true。否则，FOUND 设置为 false。

FOUND 变量可用于区分返回 NULL 和返回 0 行，或者在 LOOP 未运行时创建 IF 分支。

FOR (RANGE)

FOR (RANGE) 循环在表达式 left 和 right 指定的整数范围内迭代。

[ <<label>> ]
FOR loop_counter IN RANGE [ REVERSE ] left..right [ BY step ] LOOP
    statements
END LOOP [ label ];

loop_counter：

不必声明，它使用 left 值进行初始化
仅在 FOR 循环范围内可用

loop_counter 从 left 迭代到 right（包含），在每次迭代结尾以 step 递增。

相反，REVERSE 选项从 right 迭代到 left（包含），以 step 递减。

例如，下面是一个标准的递增 FOR 循环，其 step = 1：

=> DO $$
BEGIN
    FOR i IN RANGE 1..4 LOOP -- loop_counter i does not have to be declared
        RAISE NOTICE 'i = %', i;
    END LOOP;
    RAISE NOTICE 'after loop: i = %', i; -- fails
END;
$$;

NOTICE 2005:  i = 1
NOTICE 2005:  i = 2
NOTICE 2005:  i = 3
NOTICE 2005:  i = 4
ERROR 2624:  Column "i" does not exist -- loop_counter i is only available inside the FOR loop

在下方示例中，loop_counteri 从 4 开始，在每次迭代结尾以 2 递减：

=> DO $$
BEGIN
    FOR i IN RANGE REVERSE 4..0 BY 2 LOOP
        RAISE NOTICE 'i = %', i;
    END LOOP;
END;
$$;

NOTICE 2005:  i = 4
NOTICE 2005:  i = 2
NOTICE 2005:  i = 0

FOR (query)

FOR (QUERY) 循环在查询结果上进行迭代。

[ <<label>> ]
FOR target IN QUERY statement LOOP
    statements
END LOOP [ label ];

您可以在查询中包含 ORDER BY 子句，以使排序具有确定性。

与 FOR (RANGE) 循环不同，您必须声明 target 变量。这些变量的值在循环结束后保持不变。

例如，假设给定表 tuple：

=> SELECT * FROM tuples ORDER BY x ASC;
 x | y | z
---+---+---
 1 | 2 | 3
 4 | 5 | 6
 7 | 8 | 9
(3 rows)

此过程检索每行中的元组，并将它们存储在变量 a、b 和 c 中，在每次迭代后输出它们：

=>
=> DO $$
DECLARE
    a int; -- target variables must be declared
    b int;
    c int;
    i int := 1;
BEGIN
    FOR a,b,c IN QUERY SELECT * FROM tuples ORDER BY x ASC LOOP
        RAISE NOTICE 'iteration %: a = %, b = %, c = %', i,a,b,c;
        i := i + 1;
    END LOOP;
    RAISE NOTICE 'after loop: a = %, b = %, c = %', a,b,c;
END;
$$;

NOTICE 2005:  iteration 1: a = 1, b = 2, c = 3
NOTICE 2005:  iteration 2: a = 4, b = 5, c = 6
NOTICE 2005:  iteration 3: a = 7, b = 8, c = 9
NOTICE 2005:  after loop: a = 7, b = 8, c = 9

您还可以使用通过 EXECUTE 动态构建的查询：

[ <<label>> ]
FOR target IN EXECUTE 'statement' [ USING expression [, ... ] ] LOOP
    statements
END LOOP [ label ];

下面的过程使用 EXECUTE 构建 FOR (QUERY) 循环，并将 SELECT 语句的结果存储在变量 x 和 y 中。此类语句的结果集只有一行，因此它只迭代一次。

=> SELECT 'first string', 'second string';
   ?column?   |   ?column?
--------------+---------------
 first string | second string
(1 row)

=> DO $$
DECLARE
    x varchar; -- target variables must be declared
    y varchar;
BEGIN
    -- substitute the placeholders $1 and $2 with the strings
    FOR x, y IN EXECUTE 'SELECT $1, $2' USING 'first string', 'second string' LOOP
        RAISE NOTICE '%', x;
        RAISE NOTICE '%', y;
    END LOOP;
END;
$$;

NOTICE 2005:  first string
NOTICE 2005:  second string

FOR (cursor)

FOR (CURSOR) 循环在绑定的、未打开的游标上进行迭代，为每次迭代执行一组 statements。

[ <<label>> ]
FOR loop_variable [, ...] IN CURSOR bound_unopened_cursor [ ( [ arg_name := ] arg_value [, ...] ) ] LOOP
    statements
END LOOP [ label ];

这种类型的 FOR 循环在循环开始时打开游标，在循环结束时关闭游标。

例如，此过程创建游标 c。该过程将 6 作为实参传递给游标，因此游标仅检索 y 坐标为 6 的行，将坐标存储在变量 x_、y_ 和 z_ 中，并在每次迭代结束时输出它们：

=> SELECT * FROM coordinates;
 x  | y | z
----+---+----
 14 | 6 | 19
  1 | 6 |  2
 10 | 6 | 39
 10 | 2 | 1
  7 | 1 | 10
 67 | 1 | 77
(6 rows)

DO $$
DECLARE
    c CURSOR (key int) FOR SELECT * FROM coordinates WHERE y=key;
    x_ int;
    y_ int;
    z_ int;
BEGIN
    FOR x_,y_,z_ IN CURSOR c(6) LOOP
       RAISE NOTICE 'cursor returned %,%,% FOUND=%', x_,y_,z_,FOUND;
    END LOOP;
    RAISE NOTICE 'after loop: %,%,% FOUND=%', x_,y_,z_,FOUND;
END;
$$;

NOTICE 2005:  cursor returned 14,6,19 FOUND=f -- FOUND is only set after the loop ends
NOTICE 2005:  cursor returned 1,6,2 FOUND=f
NOTICE 2005:  after loop: 10,6,39 FOUND=t -- x_, y_, and z_ retain their values, FOUND is now true because the FOR loop iterated at least once

操作循环

RETURN

您可以使用 RETURN 退出整个过程（因此退出循环）。RETURN 是可选语句，可以添加它来向读取器发出过程结束的信号。

RETURN;

EXIT

与其他编程语言中的 break 或带标签 break 类似，EXIT 语句允许您提早退出循环，可以选择指定：

loop_label：退出的循环的名称
condition：如果 condition 为 true，则执行 EXIT 语句

EXIT [ loop_label ] [ WHEN condition ];

CONTINUE

CONTINUE 跳到循环的下一次迭代，而不执行 CONTINUE 本身之后的语句。您可以指定具有 loop_label 的特定循环：

CONTINUE [loop_label] [ WHEN condition ];

例如，此过程在其前两次迭代期间不输出，因为 CONTINUE 语句将在控制到达 RAISE NOTICE 语句之前执行并移动到循环的下一次迭代：

=> DO $$
BEGIN
    FOR i IN RANGE 1..5 LOOP
        IF i < 3 THEN
            CONTINUE;
        END IF;
        RAISE NOTICE 'i = %', i;
    END LOOP;
END;
$$;

NOTICE 2005:  i = 3
NOTICE 2005:  i = 4
NOTICE 2005:  i = 5

1.5 - 错误和诊断

ASSERT

ASSERT 是一个调试功能，它可以检查某个条件是否为 true。如果条件为 false，则 ASSERT 引发 ASSERT_FAILURE 异常，并显示可选错误消息。

要转义 '（单引号）字符，请使用 ''。同样，要转义 "（双引号）字符，请使用 ""。

ASSERT condition [ , message ];

例如，此过程检查 products 表中的行数，并使用 ASSERT 检查是否填充了该表。如果该表为空，则 Vertica 引发错误：


=> CREATE TABLE products(id UUID, name VARCHARE, price MONEY);
CREATE TABLE

=> SELECT * FROM products;
 id | name | price
----+------+-------
(0 rows)

DO $$
DECLARE
    prod_count INT;
BEGIN
    prod_count := SELECT count(*) FROM products;
    ASSERT prod_count > 0, 'products table is empty';
END;
$$;

ERROR 2005:  products table is empty

要让 Vertica 停止检查 ASSERT 语句，您可以设置布尔会话级别参数PLpgSQLCheckAsserts。

RAISE

RAISE 可以引发错误或输出用户指定的下列错误消息之一：

RAISE [ level ] 'format' [, arg_expression [, ... ]] [ USING option = expression [, ... ] ];
RAISE [ level ] condition_name [ USING option = expression [, ... ] ];
RAISE [ level ] SQLSTATE 'sql-state' [ USING option = expression [, ... ] ];
RAISE [ level ] USING option = expression [, ... ];

此过程演示各种 RAISE 级别：

=> DO $$
DECLARE
    logfile varchar := 'vertica.log';
BEGIN
    RAISE LOG 'this message was sent to %', logfile;
    RAISE INFO 'info';
    RAISE NOTICE 'notice';
    RAISE WARNING 'warning';
    RAISE EXCEPTION 'exception';

    RAISE NOTICE 'exception changes control flow; this is not printed';
END;
$$;

INFO 2005:  info
NOTICE 2005:  notice
WARNING 2005:  warning
ERROR 2005:  exception

$ grep 'this message was sent to vertica.log' v_vmart_node0001_catalog/vertica.log
<LOG> @v_vmart_node0001: V0002/2005: this message is sent to vertica.log

异常

EXCEPTION 块使您可以捕获和处理从 statements 引发的异常：

[ <<label>> ]
[ DECLARE
    declarations ]
BEGIN
    statements
EXCEPTION
    WHEN exception_condition [ OR exception_condition ... ] THEN
        handler_statements
    [ WHEN exception_condition [ OR exception_condition ... ] THEN
        handler_statements
      ... ]
END [ label ];

exception_condition 具有下列格式之一：

WHEN errcode_division_by_zero THEN ...
WHEN division_by_zero THEN ...
WHEN SQLSTATE '22012' THEN ...
WHEN OTHERS THEN ...

OTHERS 是特殊条件，它捕获除 QUERY_CANCELLED、ASSERT_FAILURE 和 FEATURE_NOT_SUPPORTED 之外的所有异常。

当引发异常时，Vertica 检查异常列表，从上到下查找匹配的 exception_condition。如果找到匹配，它将执行 handler_statements，然后离开异常块范围。

如果 Vertica 找不到匹配，则会将异常传播到下一个封闭块。您可以使用 RAISE 在异常处理程序中手动执行此操作：

RAISE;

例如，以下过程在 inner_block 中用 3 除以 0，这是一个非法操作，将引发 division_by_zero 异常（SQL 状态 22012）。Vertica 检查内部 EXCEPTION 块以查找匹配的条件：

第一个条件检查 SQL 状态 42501，因此 Vertica 移动到下一个条件。
WHEN OTHERS THEN 捕获所有异常，因此它执行该块。
随后，基本 RAISE 将异常传播到 outer_block。
外部 EXCEPTION 块成功捕获异常并输出消息。

=> DO $$
<<outer_block>>
BEGIN
    <<inner_block>>
    DECLARE
        x int;
    BEGIN
        x := 3 / 0; -- throws exception division_by_zero, SQLSTATE 22012
    EXCEPTION -- this block is checked first for matching exceptions
        WHEN SQLSTATE '42501' THEN
            RAISE NOTICE 'caught insufficient_privilege exception';
        WHEN OTHERS THEN -- catches all exceptions
            RAISE; -- manually propagate the exception to the next enclosing block
    END inner_block;
EXCEPTION -- exception is propagated to this block
    WHEN division_by_zero THEN
        RAISE NOTICE 'caught division_by_zero exception';
END outer_block;
$$;

NOTICE 2005:  caught division_by_zero exception

SQLSTATE 和 SQLERRM 变量

当处理异常时，您可以使用以下变量检索错误信息：

SQLSTATE 包含 SQL 状态
SQLERRM 包含错误消息

有关详细信息，请参阅SQL 状态列表。

此过程通过尝试将 NULL 赋值给 NOT NULL 变量捕获引发的异常，并输出 SQL 状态和错误消息：

DO $$
DECLARE
    i int NOT NULL := 1;
BEGIN
    i := NULL; -- illegal, i was declared with NOT NULL
EXCEPTION
    WHEN OTHERS THEN
        RAISE WARNING 'SQL State: %', SQLSTATE;
        RAISE WARNING 'Error message: %', SQLERRM;
END;
$$;

WARNING 2005:  SQLSTATE: 42809
WARNING 2005:  SQLERRM: Cannot assign null into NOT NULL variable

检索异常信息

您可以使用 GET STACKED DIAGNOSTICS 在异常处理程序中检索有关异常的信息：

GET STACKED DIAGNOSTICS variable_name { = | := } item [, ... ];

其中 item 可以是以下任意一项：

例如，此过程有一个 EXCEPTION 块，它捕捉 division_by_zero 错误并输出 SQL 状态、错误消息和异常上下文：

=> DO $$
DECLARE
    message_1 varchar;
    message_2 varchar;
    message_3 varchar;
    x int;
BEGIN
    x := 5 / 0;
EXCEPTION
    WHEN OTHERS THEN -- OTHERS catches all exceptions
    GET STACKED DIAGNOSTICS message_1 = RETURNED_SQLSTATE,
                            message_2 = MESSAGE_TEXT,
                            message_3 = EXCEPTION_CONTEXT;

    RAISE INFO 'SQLSTATE: %', message_1;
    RAISE INFO 'MESSAGE: %', message_2;
    RAISE INFO 'EXCEPTION_CONTEXT: %', message_3;
END;
$$;

INFO 2005:  SQLSTATE: 22012
INFO 2005:  MESSAGE: Division by zero
INFO 2005:  EXCEPTION_CONTEXT: PL/vSQL procedure inline_code_block line 8 at static SQL

1.6 - 游标

游标是对查询结果集的引用，允许您一次查看一行结果。游标记住结果集中的位置，可以是以下位置之一：

结果行
第一行之前
最后一行之后

您还可以使用 FOR 循环在未打开的绑定游标上进行迭代。有关详细信息，请参阅控制流。

声明游标

绑定游标

要将游标绑定到声明中的语句，请使用 FOR 关键字：

cursor_name CURSOR [ ( arg_name arg_type [, ...] ) ] FOR statement;

游标的实参使您能够更好地控制要处理的行。例如，假设您有下表：

=> SELECT * FROM coordinates_xy;
 x | y
---+----
 1 |  2
 9 |  5
 7 | 13
...
(100000 rows)

如果仅对 y 为 6 的行感兴趣，则可以声明以下游标，然后在打开游标时提供实参 6：

c CURSOR (key int) FOR SELECT * FROM coordinates_xy WHERE y=key;

未绑定的游标

要声明未绑定到特定查询的游标，请使用 refcursor 类型：

cursor_name refcursor;

您可以随时使用打开对未绑定的游标进行绑定。

例如，要声明游标 my_unbound_cursor：

my_unbound_cursor refcursor;

打开和关闭游标

打开

打开游标将使用给定实参执行查询，并将游标放在结果集的第一行之前。查询结果的排序（因此结果集的开头）是不确定的，除非您指定了 ORDER BY 子句。

打开绑定的游标

打开声明期间绑定的游标：

OPEN bound_cursor [ ( [ arg_name := ] arg_value [, ...] ) ];

例如，给定以下声明：

c CURSOR (key int) FOR SELECT * FROM t1 WHERE y=key;

可以使用以下某项操作打开游标：

OPEN c(5);
OPEN c(key := 5);

关闭

当游标离开范围时，打开的游标将自动关闭，但您可以使用“关闭”命令提前关闭游标。关闭的游标可以稍后重新打开，这将重新执行查询并准备新的结果集。

CLOSE cursor;

打开未绑定的游标

对未绑定的游标进行绑定，然后打开游标：

OPEN unbound_cursor FOR statement;

您还可以使用 EXECUTE，因为它是语句：

OPEN unbound_cursor FOR EXECUTE statement_string [ USING expression [, ... ] ];

例如，将游标 c 绑定到表 product_data 的查询：

OPEN c for SELECT * FROM product_data;

提取行

FETCH 语句：

检索指定游标当前指向的行，并将其存储在某个变量中。
使游标前进到下一个位置。

variable [, ...] := FETCH opened_cursor;

检索的值存储在变量中。行通常有多个值，因此您可以每行使用一个变量。

如果 FETCH 成功检索值，则特殊变量 FOUND 设置为 true。否则，如果您在游标经过结果集的最后一行时调用 FETCH，它将返回 NULL，且特殊变量 FOUND 设置为 false。

下面的过程创建游标 c，将其绑定到 coordinates 表中的 SELECT 查询。该过程将实参 1 传递给游标，因此游标仅检索 y 坐标为 1 的行，将坐标存储在变量 x_、y_ 和 z_ 中。

只有两行的 y 坐标为 1，因此使用 FETCH 两次后，第三个 FETCH 开始返回 NULL 值，且 FOUND 设置为 false：

=> SELECT * FROM coordinates;
 x  | y | z
----+---+----
 14 | 6 | 19
  1 | 6 |  2
 10 | 6 | 39
 10 | 2 |  1
  7 | 1 | 10
 67 | 1 | 77
(6 rows)

DO $$
DECLARE
    c CURSOR (key int) FOR SELECT * FROM coordinates WHERE y=key;
    x_ int;
    y_ int;
    z_ int;
BEGIN
    OPEN c(1); -- only retrieve rows where y=1
    x_,y_,z_ := FETCH c;
    RAISE NOTICE 'cursor returned %, %, %, FOUND=%',x_, y_, z_, FOUND;
    x_,y_,z_ := FETCH c; -- fetches the last set of results and moves to the end of the result set
    RAISE NOTICE 'cursor returned %, %, %, FOUND=%',x_, y_, z_, FOUND;
    x_,y_,z_ := FETCH c; -- cursor has advanced past the final row
    RAISE NOTICE 'cursor returned %, %, %, FOUND=%',x_, y_, z_, FOUND;
END;
$$;

NOTICE 2005:  cursor returned 7, 1, 10, FOUND=t
NOTICE 2005:  cursor returned 67, 1, 77, FOUND=t
NOTICE 2005:  cursor returned <NULL>, <NULL>, <NULL>, FOUND=f

移动游标

MOVE 使打开的游标前进到下一个位置，而不检索该行。如果游标位置（在 MOVE 之前）未经过最后一行，则特殊 FOUND 变量设置为 true — 即，如果调用 FETCH 而不调用 MOVE，则会检索该行。

MOVE bound_cursor;

例如，此游标仅检索 y 坐标为 2 的行。结果集只有一行，因此使用 MOVE 两次会导致前进超过第一行（和最后一行），且将 FOUND 设置为 false：

 => SELECT * FROM coordinates WHERE y=2;
 x  | y | z
----+---+---
 10 | 2 | 1
(1 row)

DO $$
DECLARE
    c CURSOR (key int) FOR SELECT * FROM coordinates WHERE y=key;
BEGIN
    OPEN c(2); -- only retrieve rows where y=2, cursor starts before the first row
    MOVE c; -- cursor advances to the first (and last) row
    RAISE NOTICE 'FOUND=%', FOUND; -- FOUND is true because the cursor points to a row in the result set
    MOVE c; -- cursor advances past the final row
    RAISE NOTICE 'FOUND=%', FOUND; -- FOUND is false because the cursor is past the final row
END;
$$;

NOTICE 2005:  FOUND=t
NOTICE 2005:  FOUND=f

1.7 - PL/pgSQL 到 PL/vSQL 迁移指南

虽然 Vertica PL/vSQL 在很大程度上与 PostgreSQL PL/pgSQL 兼容，但从 PostgreSQL PL/pgSQL 迁移时，存在一些易于解决的语义和 SQL 级别差异。

语言级别差异

下面列出了 Vertica PL/vSQL 与 PostgreSQL PL/pgSQL 之间的显著差异。在 Vertica PL/vSQL 中：

必须将 PERFORM 语句用于未返回任何值的 SQL 语句。
UPDATE/DELETE WHERE CURRENT OF 不受支持。
FOR 循环具有附加关键字：
- FOR (RANGE) 循环RANGE 关键字
- FOR (QUERY) 循环：QUERY 关键字
- FOR (CURSOR) 循环：CURSOR 关键字
默认情况下，NULL 不能强制转换为 FALSE。

解决方法：将 NULL 强制转换为 FALSE

与 PostgreSQL PL/pgSQL 不同，在 Vertica PL/vSQL 中，NULL 不可强制转换为 false。当赋值 NULL 时，预期获得布尔值的表达式会引发异常：

=> DO $$
BEGIN
    IF NULL THEN -- boolean value expected for IF
    END IF;
END;
$$;

ERROR 10268:  Query returned null where a value was expected

要使 NULL 强制转换为 false，请启用配置参数 PLvSQLCoerceNull：

=> ALTER DATABASE DEFAULT SET PLvSQLCoerceNull = 1;

计划的功能

在将来版本中，计划支持以下功能：

完整事务和会话语义。目前，存储过程在执行之前提交事务，每个嵌入式 SQL 语句在其自己的自动提交事务中执行。它具有以下含义： * 您不能回退。 * 会话级别的更改（如创建定向查询或设置会话级别参数）将成功，但是没有作用。
OUT/INOUT 参数模式。
FOREACH (ARRAY) 循环。
使用以下类型作为实参： * DECIMAL * NUMERIC * NUMBER * MONEY * UUID
非正向移动游标。
用于诊断的 CONTEXT/EXCEPTION_CONTEXT。

特殊变量 ROW_COUNT。

注意

一种解决方法是，您可以依赖 INSERT、UPDATE 和 DELETE 返回受影响的行数：

=> CREATE TABLE t1(i int);
CREATE TABLE
=> DO $$
DECLARE
x int;
BEGIN
x := INSERT INTO t1 VALUES (200);
RAISE INFO 'rows inserted: %', x;
END;
$$;
INFO 2005:  rows inserted: 1

SQL 级别差异

下面说明了 Vertica 和 PostgreSQL 之间在架构和 SQL 级别上的显著差异。在 Vertica 中：

一些数据类型大小不同 — 例如，Vertica 中的标准 INTEGER 为 8 字节，但 PostgreSQL 中为 4 字节。
INSERT、UPDATE 和 DELETE 返回受影响的行数。
某些 SQLSTATE 代码不同，这会影响异常处理。

2 - 形参模式

存储过程支持 IN 形参。OUT 和 INOUT 形参当前不受支持。

如果未指定，则形参的模式默认为 IN。

IN

IN 形参指定实参的名称和类型。这些形参确定过程的签名。当调用过载过程时，Vertica 运行其签名与调用中传递的实参类型匹配的过程。

例如，此过程的调用者必须传入 INT 和 VARCHAR 值。x 和 y 都是 IN 形参：

=> CREATE PROCEDURE raiseXY(IN x INT, y VARCHAR) LANGUAGE PLvSQL AS $$
BEGIN
    RAISE NOTICE 'x = %', x;
    RAISE NOTICE 'y = %', y;
    -- some processing statements
END;
$$;

CALL raiseXY(3, 'some string');
NOTICE 2005:  x = 3
NOTICE 2005:  y = some string

有关 RAISE NOTICE 的详细信息，请参阅错误和诊断。

3 - 执行存储过程

如果您对存储过程有 EXECUTE 权限，则可以使用指定存储过程及其 IN 实参的 CALL 语句来执行存储过程。

语法

CALL stored_procedure_name();

例如，存储过程 raiseXY() 定义为：

=> CREATE PROCEDURE raiseXY(IN x INT, y VARCHAR) LANGUAGE PLvSQL AS $$
BEGIN
    RAISE NOTICE 'x = %', x;
    RAISE NOTICE 'y = %', y;
    -- some processing statements
END;
$$;

CALL raiseXY(3, 'some string');
NOTICE 2005:  x = 3
NOTICE 2005:  y = some string

有关 RAISE NOTICE 的详细信息，请参阅错误和诊断。

您可以使用 DO 执行匿名（未命名）过程。这不需要权限：

=> DO $$
BEGIN
    RAISE NOTICE '% ran an anonymous procedure', current_user();
END;
$$;

NOTICE 2005:  Bob ran an anonymous procedure

限制运行时

可以使用会话参数 RUNTIMECAP 来设置过程的最大运行时。

此示例将会话期间所有存储过程的运行时设置为一秒，并使用无限循环运行匿名过程。Vertica 在过程运行超过一秒后终止过程：

=> SET SESSION RUNTIMECAP '1 SECOND';

=> DO $$
BEGIN
    LOOP
    END LOOP;
END;
$$;

ERROR 0:  Query exceeded maximum runtime
HINT:  Change the maximum runtime using SET SESSION RUNTIMECAP

执行安全性和权限

默认情况下，存储过程以调用者的权限执行，因此，调用者必须对存储过程访问的编录对象具有必要的权限。您可以通过指定 SECURITY 选项的 DEFINER，来允许调用者使用定义者的权限、默认角色、用户参数、和用户属性 (RESOURCE_POOL、MEMORY_CAP_KB、TEMP_SPACE_CAP_KB、RUNTIMECAP)。

例如，以下过程将一个值插入表 s1.t1。如果定义者具有所需的权限（对于架构，为 USAGE；对于表，为 INSERT），则此要求不适用于调用者。

=> CREATE PROCEDURE insert_into_s1_t1(IN x int, IN y int)
LANGUAGE PLvSQL
SECURITY DEFINER AS $$
BEGIN
    PERFORM INSERT INTO s1.t1 VALUES(x,y);
END;
$$;

使用 SECURITY DEFINER 的过程以该用户身份有效地执行过程，因此，对数据库的更改似乎是由过程的定义者（而不是其调用者）执行的。

当心

对 SECURITY DEFINER 使用不当会导致混淆代理问题，并在您的系统中引入 SQL 注入等漏洞。

示例

在此示例中，此表：

records(i INT, updated_date TIMESTAMP DEFAULT sysdate, updated_by VARCHAR(128) DEFAULT current_user())

包含以下内容：

=> SELECT * FROM records;
 i |        updated_date        | updated_by
---+----------------------------+------------
 1 | 2021-08-27 15:54:05.709044 | Bob
 2 | 2021-08-27 15:54:07.051154 | Bob
 3 | 2021-08-27 15:54:08.301704 | Bob
(3 rows)

Bob 创建一个过程来更新表，然后使用 SECURITY DEFINER 选项并将过程上的 EXECUTE 授予 Alice。Alice 现在可以使用该过程来更新表，而无需任何额外权限：

=> GRANT EXECUTE ON PROCEDURE update_records(int,int) to Alice;
GRANT PRIVILEGE

=> \c - Alice
You are now connected as user "Alice".

=> CALL update_records(99,1);
 update_records
---------------
             0
(1 row)

由于对 update_records() 的调用以 Bob 身份有效地运行过程，所以 Bob（而不是 Alice）被列为表的更新者：

=> SELECT * FROM records;
 i  |        updated_date        | updated_by
----+----------------------------+------------
 99 | 2021-08-27 15:55:42.936404 | Bob
  2 | 2021-08-27 15:54:07.051154 | Bob
  3 | 2021-08-27 15:54:08.301704 | Bob
(3 rows)

4 - 更改存储过程

您可以使用 ALTER PROCEDURE 更改存储过程并保留其授权。

示例

下面的示例使用以下过程：

=> CREATE PROCEDURE echo_integer(IN x int) LANGUAGE PLvSQL AS $$
BEGIN
    RAISE INFO 'x is %', x;
END;
$$;

使用以下对象的权限执行过程...

定义者（所有者）：

=> ALTER PROCEDURE echo_integer(int) SECURITY DEFINER;

调用者：

=> ALTER PROCEDURE echo_integer(int) SECURITY INVOKER;

更改过程的源代码：

=> ALTER PROCEDURE echo_integer(int) SOURCE TO $$
    BEGIN
        RAISE INFO 'the integer is: %', x;
    END;
$$;

更改过程的所有者（定义者）：

=> ALTER PROCEDURE echo_integer(int) OWNER TO u1;

更改过程的架构：

=> ALTER PROCEDURE echo_integer(int) SET SCHEMA s1;

重命名过程：

=> ALTER PROCEDURE echo_integer(int) RENAME TO echo_int;

5 - 存储过程：用例和示例

Vertica 中的存储过程最适合复杂的分析工作流，而不适合小型、事务繁重的工作流。一些推荐的用例包括信息生命周期管理 (ILM) 活动（例如提取、转换和加载 (ETL)）以及更复杂的分析任务（如机器学习）的数据准备。例如：

根据生命期交换分区
在生命期结束时导出数据并删除分区
保存机器学习模型的输入、输出和元数据（例如运行模型的人员、模型的版本、模型运行次数以及接收结果的人员等）以进行审核

搜索值

find_my_value() 过程在给定架构的任何表列中搜索用户指定的值，并将该值实例的位置存储在用户指定的表中：

=> CREATE PROCEDURE find_my_value(p_table_schema VARCHAR(128), p_search_value VARCHAR(1000), p_results_schema VARCHAR(128), p_results_table VARCHAR(128)) AS $$
DECLARE
    sql_cmd VARCHAR(65000);
    sql_cmd_result VARCHAR(65000);
    results VARCHAR(65000);
BEGIN
    IF p_table_schema IS NULL OR p_table_schema = '' OR
        p_search_value IS NULL OR p_search_value = '' OR
        p_results_schema IS NULL OR p_results_schema = '' OR
        p_results_table IS NULL OR p_results_table = '' THEN
        RAISE EXCEPTION 'Please provide a schema to search, a search value, a results table schema, and a results table name.';
        RETURN;
    END IF;

    sql_cmd := 'CREATE TABLE IF NOT EXISTS ' || QUOTE_IDENT(p_results_schema) || '.' || QUOTE_IDENT(p_results_table) ||
        '(found_timestamp TIMESTAMP, found_value VARCHAR(1000), table_name VARCHAR(128), column_name VARCHAR(128));';

    sql_cmd_result := EXECUTE 'SELECT LISTAGG(c USING PARAMETERS max_length=1000000, separator='' '')
        FROM (SELECT ''
        (SELECT '''''' || NOW() || ''''''::TIMESTAMP , ''''' || QUOTE_IDENT(p_search_value) || ''''','''''' || table_name || '''''', '''''' || column_name || ''''''
            FROM '' || table_schema || ''.'' || table_name || ''
            WHERE '' || column_name || ''::'' ||
            CASE
                WHEN data_type_id IN (17, 115, 116, 117) THEN data_type
                ELSE ''VARCHAR('' || LENGTH(''' || QUOTE_IDENT(p_search_value)|| ''') || '')'' END || '' = ''''' || QUOTE_IDENT(p_search_value) || ''''''' || DECODE(LEAD(column_name) OVER(ORDER BY table_schema, table_name, ordinal_position), NULL, '' LIMIT 1);'', '' LIMIT 1)

        UNION ALL '') c
            FROM (SELECT table_schema, table_name, column_name, ordinal_position, data_type_id, data_type
            FROM columns WHERE NOT is_system_table AND table_schema ILIKE ''' || QUOTE_IDENT(p_table_schema) || ''' AND data_type_id < 1000
            ORDER BY table_schema, table_name, ordinal_position) foo) foo;';

    results := EXECUTE 'INSERT INTO ' || QUOTE_IDENT(p_results_schema) || '.' || QUOTE_IDENT(p_results_table) || ' ' || sql_cmd_result;

    RAISE INFO 'Matches Found: %', results;
END;
$$;

例如，在 public 架构中搜索字符串 'dog' 的实例，然后将结果存储在 public.table_list 中：

=> CALL find_my_value('public', 'dog', 'public', 'table_list');
 find_my_value
---------------
             0
(1 row)

=> SELECT * FROM public.table_list;
      found_timestamp       | found_value |  table_name   | column_name
----------------------------+-------------+---------------+-------------
 2021-08-25 22:13:20.147889 | dog         | another_table | b
 2021-08-25 22:13:20.147889 | dog         | some_table    | c
(2 rows)

优化表

您可以使用 create_optimized_table() 过程自动从 Parquet 文件加载数据并优化查询。此过程：

创建一个外部表，其结构是使用 Vertica INFER_TABLE_DDL 函数从 Parquet 文件构建的。
创建一个原生 Vertica 表，就像外部表一样，将所有 VARCHAR 列的大小调整为要加载的数据的 MAX 长度。
使用可选分段/按作为参数传入的列排序创建超投影。
向作为参数传入的原生表添加可选主键。
将外部表中的示例数据集（100 万行）加载到原生表中。
删除外部表。
在原生表上运行 ANALYZE_STATISTICS 函数。
运行 DESIGNER_DESIGN_PROJECTION_ENCODINGS 函数以获取原生表的正确编码的超投影。
截断现在优化的原生表（我们将在单独的脚本/存储过程中加载整个数据集）。


=> CREATE OR REPLACE PROCEDURE create_optimized_table(p_file_path VARCHAR(1000), p_table_schema VARCHAR(128), p_table_name VARCHAR(128), p_seg_columns VARCHAR(1000), p_pk_columns VARCHAR(1000)) LANGUAGE PLvSQL AS $$
DECLARE
    command_sql VARCHAR(1000);
    seg_columns VARCHAR(1000);
    BEGIN

-- First 3 parms are required.
-- Segmented and PK columns names, if present, must be Unquoted Identifiers
    IF p_file_path IS NULL OR p_file_path = '' THEN
        RAISE EXCEPTION 'Please provide a file path.';
    ELSEIF p_table_schema IS NULL OR p_table_schema = '' THEN
        RAISE EXCEPTION 'Please provide a table schema.';
    ELSEIF p_table_name IS NULL OR p_table_name = '' THEN
        RAISE EXCEPTION 'Please provide a table name.';
    END IF;

-- Pass optional segmented columns parameter as null or empty string if not used
    IF p_seg_columns IS NULL OR p_seg_columns = '' THEN
        seg_columns := '';
    ELSE
        seg_columns := 'ORDER BY ' || p_seg_columns || ' SEGMENTED BY HASH(' || p_seg_columns || ') ALL NODES';
    END IF;

-- Add '_external' to end of p_table_name for the external table and drop it if it already exists
    EXECUTE 'DROP TABLE IF EXISTS ' || QUOTE_IDENT(p_table_schema) || '.' || QUOTE_IDENT(p_table_name) || '_external CASCADE;';

-- Execute INFER_TABLE_DDL to generate CREATE EXTERNAL TABLE from the Parquet files
    command_sql := EXECUTE 'SELECT infer_table_ddl(' || QUOTE_LITERAL(p_file_path) || ' USING PARAMETERS format = ''parquet'', table_schema = ''' || QUOTE_IDENT(p_table_schema) || ''', table_name = ''' || QUOTE_IDENT(p_table_name) || '_external'', table_type = ''external'');';

-- Run the CREATE EXTERNAL TABLE DDL
    EXECUTE command_sql;

-- Generate the Internal/ROS Table DDL and generate column lengths based on maximum column lengths found in external table
    command_sql := EXECUTE 'SELECT LISTAGG(y USING PARAMETERS separator='' '')
        FROM ((SELECT 0 x, ''SELECT ''''CREATE TABLE ' || QUOTE_IDENT(p_table_schema) || '.' || QUOTE_IDENT(p_table_name) || '('' y
            UNION ALL SELECT ordinal_position, column_name || '' '' ||
            CASE WHEN data_type LIKE ''varchar%''
                THEN ''varchar('''' || (SELECT MAX(LENGTH('' || column_name || ''))
                    FROM '' || table_schema || ''.'' || table_name || '') || '''')'' ELSE data_type END || NVL2(LEAD('' || column_name || '', 1) OVER (ORDER BY ordinal_position), '','', '')'')
                    FROM columns WHERE table_schema = ''' || QUOTE_IDENT(p_table_schema) || ''' AND table_name = ''' || QUOTE_IDENT(p_table_name) || '_external''
                    UNION ALL SELECT 10000, ''' || seg_columns || ''' UNION ALL SELECT 10001, '';'''''') ORDER BY x) foo WHERE y <> '''';';
    command_sql := EXECUTE command_sql;
    EXECUTE command_sql;

-- Alter the Internal/ROS Table if primary key columns were passed as a parameter
    IF p_pk_columns IS NOT NULL AND p_pk_columns <> '' THEN
        EXECUTE 'ALTER TABLE ' || QUOTE_IDENT(p_table_schema) || '.' || QUOTE_IDENT(p_table_name) || ' ADD CONSTRAINT ' || QUOTE_IDENT(p_table_name) || '_pk PRIMARY KEY (' || p_pk_columns || ') ENABLED;';
    END IF;

-- Insert 1M rows into the Internal/ROS Table, analyze stats, and generate encodings
    EXECUTE 'INSERT INTO ' || QUOTE_IDENT(p_table_schema) || '.' || QUOTE_IDENT(p_table_name) || ' SELECT * FROM ' || QUOTE_IDENT(p_table_schema) || '.' || QUOTE_IDENT(p_table_name) || '_external LIMIT 1000000;';

    EXECUTE 'SELECT analyze_statistics(''' || QUOTE_IDENT(p_table_schema) || '.' || QUOTE_IDENT(p_table_name) || ''');';

    EXECUTE 'SELECT designer_design_projection_encodings(''' || QUOTE_IDENT(p_table_schema) || '.' || QUOTE_IDENT(p_table_name) || ''', ''/tmp/toss.sql'', TRUE, TRUE);';


-- Truncate the Internal/ROS Table and you are now ready to load all rows
-- Drop the external table

    EXECUTE 'TRUNCATE TABLE ' || QUOTE_IDENT(p_table_schema) || '.' || QUOTE_IDENT(p_table_name) || ';';

    EXECUTE 'DROP TABLE IF EXISTS ' || QUOTE_IDENT(p_table_schema) || '.' || QUOTE_IDENT(p_table_name) || '_external CASCADE;';

  END;

  $$;

=> call create_optimized_table('/home/dbadmin/parquet_example/*','public','parquet_table','c1,c2','c1');

create_optimized_table
------------------------
                      0
(1 row)

=> select export_objects('', 'public.parquet_table');
       export_objects
------------------------------------------
CREATE TABLE public.parquet_table
(
    c1 int NOT NULL,
    c2 varchar(36),
    c3 date,
    CONSTRAINT parquet_table_pk PRIMARY KEY (c1) ENABLED
);


CREATE PROJECTION public.parquet_table_super /*+createtype(D)*/
(
c1 ENCODING COMMONDELTA_COMP,
c2 ENCODING ZSTD_FAST_COMP,
c3 ENCODING COMMONDELTA_COMP
)
AS
SELECT parquet_table.c1,
        parquet_table.c2,
        parquet_table.c3
FROM public.parquet_table
ORDER BY parquet_table.c1,
          parquet_table.c2
SEGMENTED BY hash(parquet_table.c1, parquet_table.c2) ALL NODES OFFSET 0;

SELECT MARK_DESIGN_KSAFE(0);

(1 row)

动态透视表

存储过程 unpivot() 将源表和目标表作为输入。它取消透视源表并将其输出到目标表中。

此示例使用下表：

=> SELECT * FROM make_the_columns_into_rows;
c1  | c2  |                  c3                  |             c4             |    c5    | c6
-----+-----+--------------------------------------+----------------------------+----------+----
123 | ABC | cf470c5b-50e3-492a-8483-b9e4f20d195a | 2021-08-24 18:49:40.835802 |  1.72964 | t
567 | EFG | 25ea7636-d924-4b4f-81b5-1e1c884b06e3 | 2021-08-04 18:49:40.835802 | 41.46100 | f
890 | XYZ | f588935a-35a4-4275-9e7f-ebb3986390e3 | 2021-08-29 19:53:39.465778 |  8.58207 | t
(3 rows)

此表包含以下列：

=> \d make_the_columns_into_rows
                                               List of Fields by Tables
Schema |           Table            | Column |     Type      | Size | Default | Not Null | Primary Key | Foreign Key
--------+----------------------------+--------+---------------+------+---------+----------+-------------+-------------
public | make_the_columns_into_rows | c1     | int           |    8 |         | f        | f           |
public | make_the_columns_into_rows | c2     | varchar(80)   |   80 |         | f        | f           |
public | make_the_columns_into_rows | c3     | uuid          |   16 |         | f        | f           |
public | make_the_columns_into_rows | c4     | timestamp     |    8 |         | f        | f           |
public | make_the_columns_into_rows | c5     | numeric(10,5) |    8 |         | f        | f           |
public | make_the_columns_into_rows | c6     | boolean       |    1 |         | f        | f           |
(6 rows)

目标表具有来自源表的列，这些列作为键/值对转换为行。它还有一个 ROWID 列，用于将键/值对绑定回源表中的原始行：

=> CREATE PROCEDURE unpivot(p_source_table_schema VARCHAR(128), p_source_table_name VARCHAR(128), p_target_table_schema VARCHAR(128), p_target_table_name VARCHAR(128)) AS $$
DECLARE
    explode_command VARCHAR(10000);
BEGIN
    explode_command := EXECUTE 'SELECT ''explode(string_to_array(''''['''' || '' || LISTAGG(''NVL('' || column_name || ''::VARCHAR, '''''''')'' USING PARAMETERS separator='' || '''','''' || '') || '' || '''']'''')) OVER (PARTITION BY rn)'' explode_command FROM (SELECT table_schema, table_name, column_name, ordinal_position FROM columns ORDER BY table_schema, table_name, ordinal_position LIMIT 10000000) foo WHERE table_schema = ''' || QUOTE_IDENT(p_source_table_schema) || ''' AND table_name = ''' || QUOTE_IDENT(p_source_table_name) || ''';';

    EXECUTE 'CREATE TABLE ' || QUOTE_IDENT(p_target_table_schema) || '.' || QUOTE_IDENT(p_target_table_name) || '
        AS SELECT rn rowid, column_name key, value FROM (SELECT (ordinal_position - 1) op, column_name
            FROM columns WHERE table_schema = ''' || QUOTE_IDENT(p_source_table_schema) || '''
            AND table_name = ''' || QUOTE_IDENT(p_source_table_name) || ''') a
            JOIN (SELECT rn, ' || explode_command || '
            FROM (SELECT ROW_NUMBER() OVER() rn, *
            FROM ' || QUOTE_IDENT(p_source_table_schema) || '.' || QUOTE_IDENT(p_source_table_name) || ') foo) b ON b.position = a.op';
END;
$$;

调用过程：

=> CALL unpivot('public', 'make_the_columns_into_rows', 'public', 'columns_into_rows');
 unpivot
---------
       0
(1 row)

=> SELECT * FROM columns_into_rows ORDER BY rowid, key;
rowid | key |                value
-------+-----+--------------------------------------
     1 | c1  | 123
     1 | c2  | ABC
     1 | c3  | cf470c5b-50e3-492a-8483-b9e4f20d195a
     1 | c4  | 2021-08-24 18:49:40.835802
     1 | c5  | 1.72964
     1 | c6  | t
     2 | c1  | 890
     2 | c2  | XYZ
     2 | c3  | f588935a-35a4-4275-9e7f-ebb3986390e3
     2 | c4  | 2021-08-29 19:53:39.465778
     2 | c5  | 8.58207
     2 | c6  | t
     3 | c1  | 567
     3 | c2  | EFG
     3 | c3  | 25ea7636-d924-4b4f-81b5-1e1c884b06e3
     3 | c4  | 2021-08-04 18:49:40.835802
     3 | c5  | 41.46100
     3 | c6  | f
(18 rows)

unpivot() 过程也可以处理源表中的新列。

将新列 z 添加到源表，然后使用相同的过程取消透视表：

=> ALTER TABLE make_the_columns_into_rows ADD COLUMN z VARCHAR;
ALTER TABLE

=> UPDATE make_the_columns_into_rows SET z = 'ZZZ' WHERE c1 IN (123, 890);
OUTPUT
--------
      2
(1 row)

=> CALL unpivot('public', 'make_the_columns_into_rows', 'public', 'columns_into_rows');
unpivot
---------
       0
(1 row)

=> SELECT * FROM columns_into_rows;
rowid | key |                value
-------+-----+--------------------------------------
     1 | c1  | 567
     1 | c2  | EFG
     1 | c3  | 25ea7636-d924-4b4f-81b5-1e1c884b06e3
     1 | c4  | 2021-08-04 18:49:40.835802
     1 | c5  | 41.46100
     1 | c6  | f
     1 | z   |   -- new column
     2 | c1  | 123
     2 | c2  | ABC
     2 | c3  | cf470c5b-50e3-492a-8483-b9e4f20d195a
     2 | c4  | 2021-08-24 18:49:40.835802
     2 | c5  | 1.72964
     2 | c6  | t
     2 | z   | ZZZ   -- new column
     3 | c1  | 890
     3 | c2  | XYZ
     3 | c3  | f588935a-35a4-4275-9e7f-ebb3986390e3
     3 | c4  | 2021-08-29 19:53:39.465778
     3 | c5  | 8.58207
     3 | c6  | t
     3 | z   | ZZZ   -- new column
(21 rows)

机器学习：优化 AUC 估计

ROC 函数可以近似估计 AUC（曲线下面积），其准确度取决于num_bins 参数；num_bins 值越大，提供的近似值越精确，但可能会影响性能。

您可以使用存储过程 accurate_auc() 来近似估计 AUC，它会自动确定给定 epsilon（误差项）的最佳 num_bins 值：

=> CREATE PROCEDURE accurate_auc(relation VARCHAR, observation_col VARCHAR, probability_col VARCHAR, epsilon FLOAT) AS $$
DECLARE
    auc_value FLOAT;
    previous_auc FLOAT;
    nbins INT;
BEGIN
    IF epsilon > 0.25 THEN
        RAISE EXCEPTION 'epsilon must not be bigger than 0.25';
    END IF;
    IF epsilon < 1e-12 THEN
        RAISE EXCEPTION 'epsilon must be bigger than 1e-12';
    END IF;
    auc_value := 0.5;
    previous_auc := 0; -- epsilon and auc should be always less than 1
    nbins := 100;
    WHILE abs(auc_value - previous_auc) > epsilon and nbins < 1000000 LOOP
        RAISE INFO 'auc_value: %', auc_value;
        RAISE INFO 'previous_auc: %', previous_auc;
        RAISE INFO 'nbins: %', nbins;
        previous_auc := auc_value;
        auc_value := EXECUTE 'SELECT auc FROM (select roc(' || QUOTE_IDENT(observation_col) || ',' || QUOTE_IDENT(probability_col) || ' USING parameters num_bins=$1, auc=true) over() FROM ' || QUOTE_IDENT(relation) || ') subq WHERE auc IS NOT NULL' USING nbins;
        nbins := nbins * 2;
    END LOOP;
    RAISE INFO 'Result_auc_value: %', auc_value;
END;
$$;

例如，给定 test_data.csv 中的以下数据：

1,0,0.186
1,1,0.993
1,1,0.9
1,1,0.839
1,0,0.367
1,0,0.362
0,1,0.6
1,1,0.726
...

（完整数据集见 test_data.csv）

您可以将数据加载到表 categorical_test_data 中，如下所示：

=> \set datafile '\'/data/test_data.csv\''
=> CREATE TABLE categorical_test_data(obs INT, pred INT, prob FLOAT);
CREATE TABLE

=> COPY categorical_test_data FROM :datafile DELIMITER ',';

调用 accurate_auc()。对于此示例，近似估计的 AUC 将在 epsilon 0.01 内：

=> CALL accurate_auc('categorical_test_data', 'obs', 'prob', 0.01);
INFO 2005:  auc_value: 0.5
INFO 2005:  previous_auc: 0
INFO 2005:  nbins: 100
INFO 2005:  auc_value: 0.749597423510467
INFO 2005:  previous_auc: 0.5
INFO 2005:  nbins: 200
INFO 2005:  Result_auc_value: 0.750402576489533

test_data.csv

1,0,0.186
1,1,0.993
1,1,0.9
1,1,0.839
1,0,0.367
1,0,0.362
0,1,0.6
1,1,0.726
0,0,0.087
0,0,0.004
0,1,0.562
1,0,0.477
0,0,0.258
1,0,0.143
0,0,0.403
1,1,0.978
1,1,0.58
1,1,0.51
0,0,0.424
0,1,0.546
0,1,0.639
0,1,0.676
0,1,0.639
1,1,0.757
1,1,0.883
1,0,0.301
1,1,0.846
1,0,0.129
1,1,0.76
1,0,0.351
1,1,0.803
1,1,0.527
1,1,0.836
1,0,0.417
1,1,0.656
1,1,0.977
1,1,0.815
1,1,0.869
0,0,0.474
0,0,0.346
1,0,0.188
0,1,0.805
1,1,0.872
1,0,0.466
1,1,0.72
0,0,0.163
0,0,0.085
0,0,0.124
1,1,0.876
0,0,0.451
0,0,0.185
1,1,0.937
1,1,0.615
0,0,0.312
1,1,0.924
1,1,0.638
1,1,0.891
0,1,0.621
1,0,0.421
0,0,0.254
0,0,0.225
1,1,0.577
0,1,0.579
0,1,0.628
0,1,0.855
1,1,0.955
0,0,0.331
1,0,0.298
0,0,0.047
0,0,0.173
1,1,0.96
0,0,0.481
0,0,0.39
0,0,0.088
1,0,0.417
0,0,0.12
1,1,0.871
0,1,0.522
0,0,0.312
1,1,0.695
0,0,0.155
0,0,0.352
1,1,0.561
0,0,0.076
0,1,0.923
1,0,0.169
0,0,0.032
1,1,0.63
0,0,0.126
0,0,0.15
1,0,0.348
0,0,0.188
0,1,0.755
1,1,0.813
0,0,0.418
1,0,0.161
1,0,0.316
0,1,0.558
1,1,0.641
1,0,0.305