plpgsql-在声明语句中使用动态表名

Question

我正在尝试将plpgsql编写为以下形式的函数（请注意，这是简化版本）：

CREATE FUNCTION check_valid(tablename regclass) RETURNS boolean AS $$

DECLARE valid_row tablename%ROWTYPE;

BEGIN

EXECUTE format('SELECT * FROM %s', tablename) into valid_row;

IF valid_row IS NULL THEN
      RETURN QUERY SELECT false;

ELSIF valid_row.is_valid = false;
      RETURN QUERY SELECT false;

ELSIF valid_row.hit_count > valid_row.hit_limit;
      RETURN QUERY SELECT false;

ELSE
      RETURN QUERY SELECT true;

END IF;

END

$$ LANGUAGE plpgsql;

失败的部分是DECLARE生产线。如何基于变量表名称声明类型？还是我需要以某种方式进行投射？

像这样的东西DECLARE mytable%ROWTYPE;可以正常工作，但是如果我使用像这样的变量名tablename%ROWTYPE：

ERROR: relation "tablename" does not exist

Answer 1

重要的是要了解这五种 不同类型的数据/符号 的主要性质：

1。 'my_tbl'

unknown 类型为的字符串文字。在SQL中使用时（是否嵌入在plpgsql代码中），它被强制转换为从 context
派生的类型。如果无法确定类型，则可能需要显式强制转换。像：'my_tbl'::text。

2。 'my_tbl'::text

将相同的字符串文字转换为 typetext。它可以保存表的名称，但实际上只是文本。

3。 'my_tbl'::regclass

注册 类 的
对象标识符（OID） __。
它显示并可以作为表示有效对象名称（'my_tbl'）的字符串输入。如果输出是不明确的或非法的，则输出将自动进行模式限定（'my_schema.my_tbl'）和/或双引号（'"mY_TbL"'）。它可以是规则
表 ， 序列 ， 视图 ， 实例化视图 ， 复合类型 等。此相关答案中的详细信息：

• 如何检查给定架构中是否存在表

4. my_tbl_var my_tbl（的缩写my_tbl_var my_tbl%ROWTYPE）

在DECLAREplpgsql代码块的部分中，它是具有 众所周知的
行类型（又称复合类型）的变量声明。该类型必须在系统表中注册pg_class（与regclass变量相同）。它不是所引用对象的OID，而是其实际的行类型。my_tbl_var和my_tbl都是
标识符 ，不能参数化。您还可以强制转换任何行或直接记录：(123, 'foo')::my_tbl

5， my_tbl_var record

在DECLAREplpgsql代码块的部分中，它是 匿名
记录的声明。基本上，占位符用于未知的行类型/结构尚未定义。它可以在 大多数
可以使用行类型的地方使用。但是，在分配记录变量之前，您无法从中访问字段。

您将 1. ， 3. 和 4. 弄混了，而是使用 5. 来解决。
但是，这里还有 更多错误 ：

• 您正在选择整个表，但是一行（记录）变量一次只能容纳一行。因此，只有第一个被分配并返回。虽然没有ORDER BY子句，但结果是任意的，可以随时更改。 邪恶的陷阱。

• 由于现在使用的是record类型，因此您需要先确定其类型，然后才能在其字段上运行测试，否则您将获得空表的异常。在您的情况下，支票record_var IS NULL几乎可以完成相同的工作。但是在所有字段中都为NULL的行有一个极端的情况：然后record_var IS NULL求值为true。测试更加棘手IS NOT NULL。详细信息在这里：

  • 设置变量时，对记录的IS NOT NULL测试不会返回TRUE

我在下面的SQL小提琴中添加了一个演示。

• 该函数返回单个标量（boolean）值。使用：

  RETURN false;
  

代替：

    ~~RETURN QUERY SELECT false;~~

功能

CREATE FUNCTION check_valid(_tbl regclass)
  RETURNS bool AS
$func$
DECLARE
   r record;
   _row_ct int;
BEGIN
   EXECUTE '
   SELECT is_valid, hit_count, hit_limit
   FROM  ' || _tbl || '
   ORDER  <whatever>
   LIMIT  1'            -- replace <whatever> with your sort criteria
   INTO r;              -- only needed columns

   GET DIAGNOSTICS _row_ct = ROW_COUNT;

   IF _row_ct = 0 THEN  -- necessary, because r may not be assigned
      RETURN false;
   ELSIF NOT r.is_valid OR r.hit_count > r.hit_limit THEN
      RETURN false;
   END IF;

   RETURN true;
END
$func$  LANGUAGE plpgsql;

SQL Fiddle （具有函数的两个变体和用于行IS NULL的演示）。

要点

• 使用GET DIAGNOSTICS来查找是否在动态语句中找到任何行EXECUTE。

• 该IF表达式可以被简化。

• 参数的类型为regclass，而不仅仅是表名。我不会为此参数使用误导性的名称“ tablename”。那只会增加您最初的困惑。调用它_tbl。

如果您还想 返回 一组可变的行类型：

• 重构PL / pgSQL函数以返回各种SELECT查询的输出