plpgsql-在声明语句中使用动态表名 [英] plpgsql - using dynamic table name in declare statement

问题描述

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

I'm trying to write plpgsql a function of the following form (note this is a simplified version):

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行.如何基于变量表名称声明类型?还是我需要以某种方式进行投射?

The part that's failing is the DECLARE line. How can I declare a type based on a variable table name? Or perhaps I need to cast it somehow?

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

Something like DECLARE mytable%ROWTYPE; works fine, yet if I use a variable name like tablename%ROWTYPE:

ERROR: relation "tablename" does not exist

推荐答案

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

It's important to understand the principal nature of these five different kinds of data / symbol:

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

A string literal of unknown type. When used in SQL (embedded in plpgsql code or not), it is coerced to a type derived from the context. If the type cannot be determined an explicit cast may be required. Like: 'my_tbl'::text.

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

The same string literal cast to type text. It can hold the name of a table, but it's really just text.

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

An object identifier (OID) for a registered class. It is displayed and can be input as string representing a valid object name ('my_tbl'). The output is automatically schema-qualified ('my_schema.my_tbl') and / or double-quoted ('"mY_TbL"') if it would be ambiguous or illegal otherwise. It can be a regular table, sequence, view, materialized view, composite type etc. Details in this related answer:

• 如何检查如果表存在于给定的模式中

在plpgsql代码块的DECLARE部分中，它是带有众所周知的

In the DECLARE section of a plpgsql code block that's a variable declaration with a well known row type (a.k.a. composite type). The type has to be registered in the system table pg_class (same as with a regclass variable). It's not the OID of the referenced object, but its actual row type. my_tbl_var and my_tbl are both identifiers here and cannot be parameterized. You can also cast any row or record directly: (123, 'foo')::my_tbl

在plpgsql代码块的DECLARE部分中，它是匿名

In the DECLARE section of a plpgsql code block that's the declaration of an anonymous record. Basically, a placeholder for a yet unknown row type / with yet undefined structure. It can be used in most of the places a row type can be used. But you cannot access fields from it before the record variable is assigned.

您混淆了 1.， 3.和 4.，并使用 5.解决了该问题. .
但是这里有更多出错的地方:

You were confusing 1., 3. and 4. and solved it by using 5. instead.
But there is more going wrong here:

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

• You are selecting a whole table, but a row (record) variable can only hold one row at a time. So only the first is assigned and returned. While there is no ORDER BY clause, the result is arbitrary and can change any time. Evil trap.

由于您现在使用的是record类型，因此需要确保已分配该类型，然后才能在其字段上运行测试，否则您将获得空表的异常. 在您的情况下，支票record_var IS NULL几乎可以完成相同的工作.但是在所有字段中都为NULL的行中有一个特殊情况:record_var IS NULL的计算结果为true.对于测试IS NOT NULL甚至更棘手.详细信息在这里:

Since you are now using a record type, you need to make sure it has been assigned before you can run tests on its fields, or you'll get exceptions for empty tables. In your case the check record_var IS NULL almost does the same job. But there is a corner case for rows with NULL in all fields: then record_var IS NULL evaluates to true. Even trickier for the test IS NOT NULL. Details here:

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

  I added a demo to the SQL fiddle below.

  该函数返回单个标量(boolean)值.使用:

  The function returns a single scalar (boolean) value. Use:

  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小提琴 (具有两种函数和行IS NULL的演示.

SQL Fiddle (with two variants of the function and a demo for row IS NULL).

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

• Use GET DIAGNOSTICS to find out whether any rows were found in a dynamic statement with EXECUTE.

IF表达式可以简化.

参数的类型为regclass，而不仅仅是表名.我不会为此参数使用误导性的名称"tablename".那只会增加您最初的困惑.改为_tbl.

The parameter is of type regclass, not just a tablename. I wouldn't use the misleading name "tablename" for this parameter. That only adds to your initial confusion. Calling it _tbl instead.

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

If you'd want to also return a set of variable row type:

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

这篇关于plpgsql-在声明语句中使用动态表名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！