使用plpgsql变量设置n_distinct时出错 [英] Error when setting n_distinct using a plpgsql variable

问题描述

我试图使用一个函数来设置表的n_distinct值.代码如下:

I tried to use a function to set the n_distinct value for a table. The code is as follows:

create temporary table _temp
(
  id integer
);

create function pg_temp.setdistinct(_cnt real)
returns void as $$
begin
  alter table _temp
    alter column id set (n_distinct=_cnt);
end
$$ language plpgsql;

select pg_temp.setdistinct(1000);

却收到以下错误:

ERROR:  invalid value for floating point option "n_distinct": _cnt
CONTEXT:  SQL statement "alter table _temp
         alter column id set (n_distinct=_cnt)"
PL/pgSQL function pg_temp_3.setdistinct(real) line 3 at SQL statement

可以使用EXECUTE语句绕过此问题，但是我想知道为什么我们不能在此特定查询中使用变量.我有什么特别的规则可以忽略吗?

The issue can be bypassed using an EXECUTE statement, but I wonder why we can't use a variable in this particular query. Is there any particular rule I overlooked?

推荐答案

这是设计使然.可以在 一章中找到说明. 变量替换 :

This is by design. The explanation can be found in the chapter Variable Substitution:

变量替换当前仅在SELECT，INSERT，UPDATE， 和DELETE命令，因为主SQL引擎允许查询 仅在这些命令中使用参数.使用非常量名称或值 在其他语句类型(通常称为实用程序语句)中， 必须将实用程序语句构造为字符串并EXECUTE.

Variable substitution currently works only in SELECT, INSERT, UPDATE, and DELETE commands, because the main SQL engine allows query parameters only in these commands. To use a non-constant name or value in other statement types (generically called utility statements), you must construct the utility statement as a string and EXECUTE it.

由于

You cannot parameterize the value in a dynamic statement with EXECUTE either because, quoting the chapter Executing Dynamic Commands:

对参数符号的另一个限制是它们只能在 SELECT，INSERT，UPDATE和DELETE命令.在其他语句类型中 (通常称为实用程序语句)，则必须插入值 即使它们只是数据值也是如此.

Another restriction on parameter symbols is that they only work in SELECT, INSERT, UPDATE, and DELETE commands. In other statement types (generically called utility statements), you must insert values textually even if they are just data values.

plpgsql函数中的 only选项 是将值连接到命令字符串中.您可以使用 format() ，但对于简单的示例，简单的串联是安全且容易的:

The only option in a plpgsql function is to concatenate the value into the command string. You might use format(), but for the simple example, plain concatenation is safe and easy::

CREATE OR REPLACE FUNCTION pg_temp.setdistinct(_cnt real)
  RETURNS void AS
$$
BEGIN
   EXECUTE 'ALTER TABLE _temp ALTER COLUMN id SET (n_distinct=' || _cnt || ')';
END
$$ LANGUAGE plpgsql;

为演示使用未记录的临时"功能，如问题中所示.
顺便说一句，关于 n_distinct .

Using an undocumented "temporary" function for the demo like in the question.
BTW, about n_distinct.

这篇关于使用plpgsql变量设置n_distinct时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！