PL/pgSQL 表达式

0.表达式的实现方式：

你写的：

IF expression THEN ...

将被被服务器的主SQL执行器处理成一个查询：

SELECT expression

sql实际上执行的是PREPARE命令：

PREPARE statement_name(integer, integer) AS SELECT $1 < $2;

每一次IF语句的执行，这个预备语句都会被EXECUTE，执行时使用变量的当前值作为参数值。

1.赋值

variable { := | = } expression;

如果没有对应类型的赋值造型可用， PL/pgSQL解释器将尝试以文本的方式转换结果值，也就 是在应用结果类型的输出函数之后再应用变量类型的输入函数。

2.执行无返回的命令

在调用一个有副作用但是没有有用的结果值的函数可以使用PERFORM语句：

PERFORM query;

对于WITH查询，使用PERFORM并且接着把该查询放在圆括号中（此时，该查询只能返回一行）。PL/pgSQL变量将被替换到该查询中，正像对不返回结果的命令所作的那样，并且计划被以相同的方式被缓存。

3.执行单一返回的命令

一个产生单一行（可能有多个列）的 SQL 命令的结果可以被赋值给一个记录变量、行类型变量或标量变量列表。这通过书写基础 SQL 命令并增加一个INTO子句来达成。例如：

SELECT select_expressions INTO [STRICT] target FROM ...;
INSERT ... RETURNING expressions INTO [STRICT] target;
UPDATE ... RETURNING expressions INTO [STRICT] target;
DELETE ... RETURNING expressions INTO [STRICT] target;

PostgreSQL常规的INTO目标是一个新创建的表。如果你想要在一个PL/pgSQL函数中从一个SELECT的结果创建一个表，请使用语法CREATE TABLE … AS SELECT。

INTO子句几乎可以出现在 SQL 命令中的任何位置。通常它被写成刚好在SELECT命令中的select_expressions列表之前或之后，或者在其他命令类型的命令最后。

如果STRICT没有在INTO子句中被指定，那么target将被设置为该查询返回的第一个行，或者在该查询不返回行时设置为空（注意除非使用了ORDER BY，否则“第一行”的界定并不清楚）。第一行之后的任何结果行都会被抛弃。你可以检查特殊的FOUND变量来确定是否返回了一行：

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

如果指定了STRICT选项，该查询必须刚好返回一行或者将会报告一个运行时错误，该错误可能是NO_DATA_FOUND（没有行）或TOO_MANY_ROWS（多于一行）。成功执行一个带STRICT的命令总是会将FOUND置为真。

对于带有RETURNING的INSERT/UPDATE/DELETE，即使没有指定STRICT，PL/pgSQL也会针对多于一个返回行的情况报告一个错误。这是因为没有类似于ORDER BY的选项可以用来决定应该返回哪个被影响的行。

如果为该函数启用了If print_strict_params，那么当因为 STRICT的要求没有被满足而抛出一个错误时，该错误消息 的DETAIL将包括传递给该查询的参数信息。可以通过设置 plpgsql.print_strict_params为所有函数更改 print_strict_params设置，但是只有修改后被编译的函数 才会生效。也可以使用一个编译器选项来为一个函数启用它。

4.动态命令

很多时候你将想要在PL/pgSQL函数中产生动态命令，也就是每次执行中会涉及到不同表或不同数据类型的命令。PL/pgSQL通常对于命令所做的缓存计划尝试（如第 42.11.2 节中讨论）在这种情境下无法工作。

EXECUTE command-string [ INTO [STRICT] ... target ] [ USING expression [, ... ] ];

--例1
EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2'
   INTO c
   USING checked_user, checked_date;

--例2
EXECUTE 'SELECT count(*) FROM '
    || quote_ident(tabname)
    || ' WHERE inserted_by = $1 AND inserted <= $2'
   INTO c
   USING checked_user, checked_date;

--例3
EXECUTE format('SELECT count(*) FROM %I '
   'WHERE inserted_by = $1 AND inserted <= $2', tabname)
   INTO c
   USING checked_user, checked_date;

在上面第一个例子中，带有一个简单的常量命令字符串和一些USING参数的EXECUTE命令在功能上等效于直接用PL/pgSQL写的命令，并且允许自动发生PL/pgSQL变量替换。重要的不同之处在于，EXECUTE会在每一次执行时根据当前的参数值重新规划该命令，而PL/pgSQL则是创建一个通用计划并且将其缓存以便重用。在最佳计划强依赖于参数值的情况中，使用EXECUTE来明确地保证不会选择一个通用计划是很有帮助的。

还有，对于通过EXECUTE执行的命令不会有计划被缓存。该命令反而在每次运行时都会被做计划。因此，该命令字符串可以在执行不同表和列上动作的函数中被动态创建。

INTO子句指定一个返回行的 SQL 命令的结果应该被赋值到哪里。如果提供了一个行或变量列表，它必须完全匹配查询结果的结构（当使用一个记录变量时，它会自动把它自己配置为匹配结果结构）。如果返回多个行，只有第一个行会被赋值给INTO变量。如果没有返回行，NULL 会被赋值给INTO变量。如果没有指定INTO变量，该查询结果会被抛弃。

PL/pgSQL中的EXECUTE语句与EXECUTE PostgreSQL服务器支持的 SQL 语句无关。服务器的EXECUTE语句不能直接在PL/pgSQL函数中使用（并且也没有必要）。

4.1 动态命令中对于引号需要转义的处理

pgsql 官方文档 quote转义函数

我们推荐在函数体中使用美元符号引用来引用固定的文本（如果你有没有使用美元符界定的老代码，这样在把上述代码转换成更合理的模式时会省力些。 （美元符号引用只对引用固定文本有用）

--例1
	-%I等效于quote_ident并且 %L等效于quote_nullable。 format函数可以和 USING子句一起使用： -
EXECUTE format('UPDATE tbl SET %I = $1 '
   'WHERE key = $2', colname) USING newvalue, keyvalue;

--例2
EXECUTE 'UPDATE tbl SET '
        || quote_ident(colname)
        || ' = '
        || quote_literal(newvalue)
        || ' WHERE key = '
        || quote_literal(keyvalue);

包含列或表标识符的表达式应该通过quote_ident被传递。
表达式包含在被构造出的命令中应该是字符串的值时，它应该通过quote_literal被传递。

quote_literal被标记为STRICT，当用一个空参数调用时，它总是会返回空。在上面的例子中，如果newvalue或keyvalue为空，整个动态查询字符串会变成空，导致从EXECUTE得到一个错误。可以通过使用quote_nullable函数来避免这种问题，它工作起来和quote_literal相同，除了用空参数调用时会返回一个字符串NULL。

=操作符中使用空操作数得到的结果总是为空。如果想让空和一个普通键值一样工作，可以使用IS NOT DISTINCT FROM替代=，但是它的处理效率不如=，因此只有在非常必要时才这样做。

例1的形式更好，因为变量被以它们天然的数据类型格式处理，而不是无 条件地把它们转换成文本并且通过%L引用它们。这也效率 更高。

5.获取结果集的状态

5.1 方式一：GET DIAGNOSTICS命令

这条命令允许检索系统状态指示符。

GET [ CURRENT ] DIAGNOSTICS variable { = | := } item [ , ... ];

--例：
GET DIAGNOSTICS integer_var = ROW_COUNT;

CURRENT是一个噪声词。
每个item是一个关键字， 它标识一个要被赋予给指定变量的状态值（变量应具有正确的数据类型来接收状态值）。
pl/pgSQL提供了一些可用的状态项（即例子中的“ROW_COUNT”）。
冒号等号（:=）可以被用来取代 SQL 标准的=符号。

5.2 方式二： 检查FOUND变量

Found变量受影响的所有条件：

• SELECT INTO语句赋值了一行将把FOUND设置为真，反之为假。
• PERFORM语句生成（并且抛弃）一行或多行将把FOUND设置为真，反之。。。
• UPDATE、INSERT以及DELETE语句影响了至少一行，会把FOUND设置为真
• FETCH语句返回了一行，它将把FOUND设置为真
• MOVE语句成功地重定位了游标，它将会把FOUND。。。
• FOR或FOREACH语句迭代了一次或多次（在循环执行中，不会被循环语句修改）
• 查询返回至少一行，RETURN QUERY和RETURN QUERY EXECUTE语句会把FOUND设为

需要注意：

• EXECUTE会修改GET DIAGNOSTICS的输出，但不会修改FOUND的输出。
• FOUND是每个PL/pgSQL函数的局部变量；任何对它的修改只影响当前的函数。

6.啥都不做

这里直接放两个例子：

--例1
BEGIN
    y := x / 0;
EXCEPTION
    WHEN division_by_zero THEN
        NULL;  -- 忽略错误
END;

--例2
BEGIN
    y := x / 0;
EXCEPTION
    WHEN division_by_zero THEN  -- 忽略错误
END;