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

查看:23
本文介绍了使用 plpgsql 变量设置 n_distinct 时出错的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我尝试使用函数来设置表的 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 语句可以绕过这个问题,但我想知道为什么我们不能在这个特定查询中使用变量.有没有我忽略的特定规则?

解决方案

这是设计使然.该手册在一章中进行了说明变量替换:

<块引用>

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

不能使用EXECUTE参数化动态语句中的值,因为引用执行动态命令一章:

<块引用>

对参数符号的另一个限制是它们只能在SELECTINSERTUPDATEDELETE 命令.在其他语句类型中(通常称为实用程序语句),您必须插入值即使它们只是数据值.

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, 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:

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.

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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆