Postgresql-基本语句-服务器专区

Postgresql-基本语句

作者：网络　佚名编辑： IT168 2005-09-21 00:00

【IT168 服务器学院】本节以及随后的一节里，我们描述所有 PL/pgSQL 明确可以理解的语句类型。任何无法识别为这样类型的语句将被做为 SQL 命令看待，并且被发送到主数据库引擎执行（在将语句中用到的任何 PL/pgSQL 变量进行替换之后）。因此，举例来说，SQL INSERT，UPDATE，和 DELETE 命令可能被认为是 PL/pgSQL 语句，但是它们并未在此明确列出。

赋值
给一个变量或行/记录赋值用下面方法：

identIFier := expression;
如上所述，这样的语句中的表达式是用一个发送到主数据库引擎的 SQL SELECT 命令计算的。该表达式必须生成单一的数值。

如果表达式的结果数据类型和变量数据类型不一致，或者变量具有已知的尺寸/精度（象 char(20)），结果值将隐含地被PL/pgSQL解释器用结果类型的输出函数和变量类型的输入函数转换。要注意的是，如果结果数值的字串形式不是输入函数可以接受的形式，那么这样做可能导致类型输入函数产生的运行时错误。

例子∶

user_id := 20;
tax := subtotal * 0.06;

    SELECT INTO

    生成多个列（但只有一行）的SELECT命令的结果可以赋予一个记录变量，行类型变量，或者一个标量变量的列表。这是用下面方法实现的：

SELECT INTO target select_expressions FROM ...;
这里的 target 可以是一个记录变量，行变量，或者一个用逗号分隔的简单变量和记录/行字段的列表。 select_expressions 和命令的剩余部分和普通 SQL 一样。

请注意这个构造和 PostgreSQL 普通的SELECT INTO构造的解释是不一样的，后者的INTO目标是一个新创建的表。（如果你想在 PL/pgSQL 函数里从一个SELECT 的结果中创建一个表，那么使用 CREATE TABLE ... AS SELECT 语法。）

如果将一行或者一个变量列表用做目标，那么选出的数值必需精确匹配目标的结构，否则就会产生运行时错误。如果目标是一个记录变量，那么它自动将自己配置成命令结果列的行类型。

除了INTO子句，剩下的SELECT语句和普通的 SQL SELECT命令完全一样，并且你可以使用SELECT的全部能力。

INTO 子句几乎可以出现在 SELECT 语句的任何地方。习惯上它是跟在 SELECT 后面，就像上面写的那样，或者就在 FROM 之前 — 也就是说，在 select_expressions 列表之前或者之后。

如果命令返回零行，则给目标赋与空值。如果命令返回多行，那么将第一行赋与目标并抛弃其它的行。（请注意：除非你用了ORDER BY，否则"第一行"是不明确的。）

INTO子句可以出现在SELECT命令里的几乎任何地方。

在一个 SELECT INTO 语句之后，你可以检查特殊变量 FOUND 来判断一个赋值是否成功，也就是说，查询至少返回一行。例如：

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

要测试一个记录/行结果是否为空，你可以使用 IS NULL 条件。不过，这个时候没有任何办法来判断是否有额外的行被抛弃。下面是一个例子，处理没有返回行的情况：

DECLARE
users_rec RECORD;
BEGIN
SELECT INTO users_rec * FROM users WHERE user_id=3;

    IF users_rec.homepage IS NULL THEN
        -- 用户没有输入主页，返回"http://"
        RETURN ''http://'';
    END IF;
END;

执行一个没有结果的表达式或者命令

有时候我们希望计算一个表达式或者一个命令，但是却丢弃其结果（通常因为我们经常调用一些存在有用的副作用但是不存在有用结果值的函数）。要在 PL/pgSQL 里干这件事，你可以使用PERFORM语句：

PERFORM query;
这条语句执行一个 query并且丢弃结果。 query 的写法和你平常写 SQL SELECT 命令是一样的，只是把开头的关键字 SELECT 替换成 PERFORM。 PL/pgSQL 的变量和平常一样代换到命令中。同样，如果命令生成至少一行，那么特殊的变量 FOUND 设置为真，如果没有生成行，则为假。

注意: 我们可能希望没有INTO子句的SELECT也能满足这样的需要，但是目前可以接受的唯一的方法是PERFORM。

一个例子：

PERFORM create_mv(''cs_session_page_requests_mv'', my_query);

执行动态命令

你经常会希望在你的PL/pgSQL函数里生成动态命令。也就是那些每次执行的时候都会涉及不同表或不同数据类型的命令。在这样的情况下，PL/pgSQL 试图为命令缓冲执行计划的一般企图将不再合适。为了处理这样的问题，我们提供了 EXECUTE 语句：

EXECUTE command-string;
这里的 command-string 是一个生成字串（类型为 text）的表达式，该字串包含要执行的命令。该字串的文本将被传递给 SQL 引擎。

请特别注意在该命令字串里将不会发生任何 PL/pgSQL 变量代换。变量的数值必需在构造命令字串的时候插入该字串。

和所有其它在PL/pgSQL里的命令不同，一个由EXECUTE语句运行的命令在服务器生命期内并不只准备和保存一次。相反，在该语句每次运行的时候，命令都准备一次。命令字串可以在过程里动态地生成以便于对各种不同的表和字段进行操作。

来自SELECT命令的结果被EXECUTE抛弃，并且目前EXECUTE 里面还不支持SELECT INTO。所以我们没有办法从一个动态创建的 SELECT 中，使用简单的 EXECUTE 命令抽取结果。但是有其它两种方法可以实现里抽取结果：一种是是使用在 Section 35.7.4 里描述的FOR-IN-EXECUTE方式，另外一种是和 OPEN-FOR-EXECUTE 一起用游标，就像 Section 35.8.2 里描述的那样。

在使用动态命令的时候，你经常需要逃逸单银号。我们建议包围你的函数体内固定文本的方法是美元符包围。（如果你有老的，没有使用美元符包围的代码，请参考 Section 35.2.1，这样在把老代码转换成更合理的结构时，会节省你的一些精力。）

要插入到构造出来的查询中的动态数值也需要特殊的处理，因为他们自己可能包含引号字符。一个例子（除了特别说明之外，这里我们都假设你使用了美元符包围）：

EXECUTE ''UPDATE tbl SET ''
        || quote_ident(columnname)
        || '' = ''
        || quote_literal(newvalue)
        || '' WHERE ...'';

这个例子显示了函数 quote_ident(text) 和 quote_literal(text) 的使用。为了安全，包含字段和表标识符的变量应该传递给函数 quote_ident。那些包含数值的变量，如果其值在构造出来态命令字串里应外是文本字串，那么应该传递给 quote_literal。它们俩都会采取合适的步骤把输入文本包围在单或双引号里并且对任何嵌入其中的特殊字符进行合适的逃逸处理。

请注意美元符包围只对包围固定文本有用。如果想象下面这样做上面的例子，那就太糟糕了

EXECUTE ''UPDATE tbl SET ''
        || quote_ident(colname)
        || '' = $$''
        || newvalue
        || ''$$ WHERE ...'';

因为如果 newvalue 的内容碰巧有$$，那么这段代码就有毛病了。同样的问题可能出现在你选用的任何美元符包围分隔符上。因此，要想安全地包围事先不知道地文本，你必须使用 quote_literal。

动态命令和 EXECUTE 的一个更大的例子在 Example 35-5 里，这个例子制作病执行了一个定义新的函数的 CREATE FUNCTION 命令。

获取结果状态

有好几种方法可以判断一条命令的效果。第一个方法是使用 GET DIAGNOSTICS，它的形式如下：

GET DIAGNOSTICS variable = item [ , ... ] ;
这条命令允许我们检索系统状态标识符。每个 item 是一个关键字，表示一个将要赋予该特定变量的状态值（该变量应该和要接收的数值类型相同）。当前可用的状态项有 ROW_COUNT，最后一个发送给 SQL 引擎的 SQL 命令处理的行的数量，和 RESULT_OID，最后一条 SQL 命令插入的最后一行的 OID。请注意 RESULT_OID 只有在一个INSERT命令之后才有用。

一个例子：

GET DIAGNOSTICS var_integer = ROW_COUNT;

另外一个判断命令效果的方法是一个类型为 boolean 的特殊变量 FOUND。 FOUND在每个 PL/pgSQL 函数里开始都为假。它被下列语句设置：

一个SELECT INTO语句如果返回一行则设置 FOUND 为真，如果没有返回行则设置为假。

一个PERFORM语句如果生成（或抛弃）一行，则设置 FOUND 为真，如果没有生成行则为假。

如果至少影响了一行，那么UPDATE，INSERT，和DELETE语句设置 FOUND 为真，如果没有行受影响则为假。

FETCH语句如果返回行则设置 FOUND 为真，如果不返回行则为假。

一个FOR语句如果迭代了一次或多次，则设置 FOUND 为真，否则为假。这个规律适用于所有FOR语句的三种变体（整数FOR循环，记录集的FOR循环，以及动态记录集FOR循环）。只有在FOR循环退出的时候才设置 FOUND；在循环执行的内部，FOUND 不被FOR语句修改，但是在循环体里它可能被其他语句的执行而修改。

FOUND 是每个 PL/pgSQL 里的局部变量；它的任何修改只影响当前的函数。

关注我们