使用 plpgsql 变量设置 n_distinct 时出错 [英] Error when setting n_distinct using a plpgsql variable
问题描述
我尝试使用函数来设置表的 n_distinct 值.代码如下:
创建临时表_temp(id 整数);创建函数 pg_temp.setdistinct(_cnt real)以 $$ 形式返回无效开始更改表_temp更改列 ID 集(n_distinct=_cnt);结尾$$ 语言 plpgsql;选择 pg_temp.setdistinct(1000);
但收到以下错误:
<块引用>错误:浮点选项n_distinct"的无效值:_cnt上下文:SQL 语句alter table _temp更改列 ID 集 (n_distinct=_cnt)"PL/pgSQL 函数 pg_temp_3.setdistinct(real) 第 3 行在 SQL 语句
使用 EXECUTE
语句可以绕过这个问题,但我想知道为什么我们不能在这个特定查询中使用变量.有没有我忽略的特定规则?
这是设计使然.该手册在一章中进行了说明变量替换:
<块引用>变量替换目前仅适用于SELECT
、INSERT
、UPDATE
,和 DELETE
命令,因为主 SQL 引擎允许查询参数仅在这些命令中.使用非常量名称或值在其他语句类型(通常称为实用程序语句)中,您必须将实用程序语句构造为字符串并EXECUTE
它.
您不能使用EXECUTE
参数化动态语句中的值,因为引用执行动态命令一章:
对参数符号的另一个限制是它们只能在SELECT
、INSERT
、UPDATE
和 DELETE
命令.在其他语句类型中(通常称为实用程序语句),您必须插入值即使它们只是数据值.
plpgsql 函数中的唯一选项 是将值连接到命令字符串中.您可以使用 format()
,但对于简单的例子,简单的连接是安全和简单的::
CREATE OR REPLACE FUNCTION pg_temp.setdistinct(_cnt real)退货无效语言 plpgsql AS$$开始EXECUTE 'ALTER TABLE _temp ALTER COLUMN id SET (n_distinct=' || _cnt || ')';结尾$$;
模式限定 pg_temp.
使它成为(未记录的!)临时"功能,反映问题.
关于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);
Yet receive the following errors:
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
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 manual explains in the chapter Variable Substitution:
Variable substitution currently works only in
SELECT
,INSERT
,UPDATE
, andDELETE
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 andEXECUTE
it.
You cannot parameterize the value in a dynamic statement with EXECUTE
either because, quoting the chapter Executing Dynamic Commands:
Another restriction on parameter symbols is that they only work in
SELECT
,INSERT
,UPDATE
, andDELETE
commands. In other statement types (generically called utility statements), you must insert values textually even if they are just data values.
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
LANGUAGE plpgsql AS
$$
BEGIN
EXECUTE 'ALTER TABLE _temp ALTER COLUMN id SET (n_distinct=' || _cnt || ')';
END
$$;
The schema-qualification pg_temp.
makes it an (undocumented!) "temporary" function, mirroring the question.
The manual about n_distinct
.
这篇关于使用 plpgsql 变量设置 n_distinct 时出错的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!