postgresql - 使用 plpgsql 变量设置 n_distinct 时出错

Question

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

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 语句绕过这个问题，但我想知道为什么我们不能在这个特定查询中使用变量。有什么我忽略的特定规则吗？

Answer 1

这是设计使然。手册在章节 Variable Substitution 中进行了解释:

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

您不能使用EXECUTE 参数化动态语句中的值，因为，quoting the chapter Executing Dynamic Commands :

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

plpgsql 函数中的唯一选项 是将值连接到命令字符串中。您可以使用 format() , 但对于简单的示例，普通连接是安全且简单的::

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

模式限定 pg_temp. 使它成为一个(未记录!)“临时”函数，反射(reflect)了这个问题。
关于n_distinct的说明书.