sql - 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 代码中)，它被强制为从 派生的类型。上下文 .如果无法确定类型，则可能需要显式转换。喜欢:'my_tbl'::text .

2. 'my_tbl'::text
相同的字符串文字转换为 类型 text .它可以包含表格的名称，但实际上只是文本。

3. 'my_tbl'::regclass
一个 object identifier (OID)注册类(class) .它被显示并可以作为代表有效对象名称的字符串输入 ('my_tbl')。如果输出不明确或非法，则输出会自动进行模式限定( 'my_schema.my_tbl' )和/或双引号( '"mY_TbL"' )。它可以是常规表、序列、 View 、物化 View 、复合类型等。此相关答案中的详细信息:

How to check if a table exists in a given schema

4. my_tbl_var my_tbl ( my_tbl_var my_tbl%ROWTYPE 的缩写)

在 DECLARE plpgsql 代码块的一部分，它是带有 的变量声明众所周知row type (又名复合类型)。类型必须在系统表 pg_class中注册(与 regclass 变量相同)。它不是被引用对象的 OID，而是它的实际行类型。 my_tbl_var和 my_tbl这里都是标识符，不能参数化。您也可以直接转换任何行或记录: (123, 'foo')::my_tbl
5. my_tbl_var record
在 DECLARE plpgsql 代码块的一部分，它是 的声明匿名 record .基本上，一个未知行类型的占位符/具有尚未定义的结构。它可以在大多数可以使用行类型的地方使用。但是在分配记录变量之前，您不能从中访问字段。

你弄糊涂了 1. , 3. 和 4. 并通过使用 解决了它5. 反而。
但是有 更多问题 这里:

您正在选择整个表，但行(记录)变量一次只能保存一行。所以只有第一个被分配和返回。虽然没有ORDER BY子句，结果是任意的，可以随时更改。 邪恶的陷阱。

由于您现在使用的是 record类型，您需要确保它已被分配，然后才能对其字段运行测试，否则您将获得空表的异常。
在您的情况下，支票 record_var IS NULL几乎做同样的工作。但是对于所有字段中都为 NULL 的行存在一个极端情况:然后 record_var IS NULL评估为真。测试更棘手IS NOT NULL .详情在这里:

IS NOT NULL test for a record does not return TRUE when variable is set

我在 SQL fiddle 中添加了一个演示以下。

该函数返回单个标量 (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反而。

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

Refactor a PL/pgSQL function to return the output of various SELECT queries