在EXECUTE中用于整数变量的格式说明符是什么？

Question

在EXECUTE中用于整数变量的格式说明符是什么？

postgresqlplpgsqldynamic-sqlstring-concatenation

8

CREATE OR REPLACE FUNCTION getParentLtree(parent_id bigint, tbl_name varchar) 
  RETURNS ltree AS
$BODY$
DECLARE
   parent_ltree ltree;
BEGIN
-- This works fine:
-- select into parent_ltree l_tree from tbl1 where id = parent_id;

EXECUTE format('select into parent_ltree l_tree from %I
                where id = %I', tbl_name,parent_id);

RETURN parent_ltree;
END;
$BODY$ LANGUAGE plpgsql;

以上函数存在2个问题：

parent_id 是 integer 类型，但被引号替代了？int 变量的正确格式说明符是什么？
select into 不能与 EXECUTE 一起使用？如何使上述被注释的查询使用传递的表名？

- RAFIQ

3个回答

5

使用%s来表示字符串，%I则用来表示标识符。

select format('select into parent_ltree l_tree from %I  where id = %s', 'tbl1', 1);
                         format                          
---------------------------------------------------------
 select into parent_ltree l_tree from tbl1  where id = 1

http://www.postgresql.org/docs/current/static/functions-string.html#FUNCTIONS-STRING-FORMAT

PL/pgSQL的select into与PostgreSQL的select into不同。请改用create table as：

create table parent_ltree as 
select l_tree 
from tbl1
where id = 1

http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW

提示：请注意，使用 SELECT 和 INTO 的这种解释与 PostgreSQL 常规的 SELECT INTO 命令非常不同，其中 INTO 目标是一个新创建的表。如果您想在 PL/pgSQL 函数中从 SELECT 结果创建表，请使用语法 CREATE TABLE...AS SELECT。

要从 execute 语句中的变量进行 select into 操作：

EXECUTE format('select l_tree from %I where id = %s', tbl_name,parent_id) 
into parent_ltree;

http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

- Clodoaldo Neto

感谢回复，%s 是正确的类型说明符，对于问题1有效，但对于问题2，结果只有一个值而不是一组值，因为在这种情况下id是唯一的，我们如何将其读入变量而不是表格？ - RAFIQ

那也可以，非常感谢！总结一下你的解决方案：1. 对于标识符使用 %s；2. into 应该在 format 函数之外使用。 - RAFIQ

2

以下的Postgres使用for循环和变量创建了10个以前缀'sbtest'开头的表，分别为sbtest1、sbtest2...sbtest10。

create_table.sql

do $$
    DECLARE myvar integer;
begin
    for myvar in 1..10 loop
        EXECUTE format('CREATE TABLE sbtest%s (
        id SERIAL NOT NULL,
        k INTEGER NOT NULL,
        c CHAR(120) NOT NULL,
        pad CHAR(60) NOT NULL,
        PRIMARY KEY (id))', myvar);
    end loop;
end; $$

使用psql -U用户名 -d数据库名 -f create_table.sql运行它

Example Table  sbtest1 is as
id | k | c | pad
----+---+---+-----
(0 rows)

- Deepakshi Mittal

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Erwin Brandstetter · Accepted Answer

这将更短、更快、更安全：

CREATE OR REPLACE FUNCTION get_parent_ltree(parent_id bigint, tbl_name regclass
                                          , OUT parent_ltree ltree)
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE format('SELECT l_tree FROM %s WHERE id = $1', tbl_name)
   INTO  parent_ltree
   USING parent_id;
END
$func$;

为什么？

最重要的是，使用EXECUTE的USING子句来进行参数传递。不要将它们转换为text类型再进行拼接和解释。这样会更慢且容易出错。

通常情况下，对于诸如表名之类的标识符，您应该使用format()函数中的%I说明符。对于现有的表，regclass对象标识符类型可能更好。请参见：

将表名作为PostgreSQL函数参数

OUT参数使得操作更加简单，性能不会受到影响。

在Postgres中，请勿使用未加引号的驼峰式标识符，例如getParentLtree。请参阅手册获取详细信息。