用于EXECUTE的format()中整数变量的格式说明符? [英] Format specifier for integer variables in format() for EXECUTE?

问题描述

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个问题:

1. parent_id是integer，但是用引号代替了吗? int变量的正确格式说明符是什么?
2. select into不能与EXECUTE一起使用?如何使上面的注释查询使用通过的表名?

1. parent_id is integer but it is replaced with quotes? What is the correct format specifier for int variables?
2. select into does not work with EXECUTE? How can I make above commented query to use table name passed?

推荐答案

这将更短，更快，更安全:

This would be shorter, faster and safer:

CREATE OR REPLACE FUNCTION get_parent_ltree(parent_id bigint, tbl_name regclass
                                          , OUT parent_ltree ltree) AS
$func$
BEGIN

EXECUTE format('SELECT l_tree FROM %s WHERE id = $1', tbl_name)
INTO  parent_ltree
USING parent_id;

END
$func$ LANGUAGE plpgsql;

为什么?

• 最重要的是，使用EXECUTE 的> USING 子句以获取参数值.不要将它们转换为text，不要串联并解释它们.这样会更慢且容易出错.

  Why?

  • Most importantly, use the USING clause of EXECUTE for parameter values. Don't convert them to text, concatenate and interpret them back. That would be slower and error-prone.

    通常，您将使用 %I说明符，用format() 表示表名之类的标识符.不过，还有一种更好的方法:使用 regclass对象标识符类型.详细信息在这里:
    表名作为PostgreSQL函数参数

    Normally you would use the %I specifier with format() for identifiers like the table name. There is an even better way, though: use a regclass object-identifier type. Details here:
    Table name as a PostgreSQL function parameter

    使用 OUT参数进行简化您的代码.性能是一样的.

    Use an OUT parameter to simplify your code. Performance is the same.

    请勿在Postgres中使用未加引号的CaMeL案例标识符，例如getParentLtree. 手册中的详细信息.

    Don't use unquoted CaMeL case identifiers like getParentLtree in Postgres. Details in the manual.

    这篇关于用于EXECUTE的format()中整数变量的格式说明符?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！