执行函数时，复合数据类型的INTEGER：(2，2)&QOOT；类型的输入语法无效 [英] Invalid input syntax for type integer: "(2,2)" with composite data type while executing function

问题描述

begin;
create type public.ltree as (a int, b int);
create  table public.parent_tree(parent_id int,l_tree ltree);
insert into public.parent_tree values(1,(2,2)),(2,(1,2)),(3, (1,28));
commit;

正在尝试复制此答案中的解决方案：

• Format specifier for integer variables in format() for EXECUTE?

对于复合类型的函数：

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

执行的有效查询：

select l_tree from parent_tree where parent_id = 1;

执行函数：

select get_parent_ltree(1,'parent_tree');
select get_parent_ltree(1,'public.parent_tree');

我收到此错误：

ERROR:  invalid input syntax for type integer: "(2,2)"  
CONTEXT:  PL/pgSQL function get_parent_ltree(integer,regclass) line 3 at EXECUTE

第3行的上下文：

推荐答案

输出参数_l_tree是行变量。(复合类型被视为行变量。)SELECT INTO逐个为行变量的字段赋值。The manual:

可选target是记录变量、行变量或简单变量和记录/行字段的逗号分隔列表，[...]

因此，目前(第14页)，行或记录变量必须独立为target。或者如相应的Postgres错误消息所说：

ERROR:  record variable cannot be part of multiple-item INTO list

这行得通：

CREATE OR REPLACE FUNCTION public.get_parent_ltree (IN  _parent_id int
                                                  , IN  _tbl_name  regclass
                                                  , OUT _l_tree    ltree)
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE format('SELECT (l_tree).* FROM %s WHERE parent_id = $1', _tbl_name)
   INTO  _l_tree
   USING _parent_id;
END
$func$;

或此：

CREATE OR REPLACE FUNCTION public.get_parent_ltree2 (IN  _parent_id int
                                                   , IN  _tbl_name  regclass
                                                   , OUT _l_tree    ltree)
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE format('SELECT (l_tree).a, (l_tree).b FROM %s WHERE parent_id = $1', _tbl_name)
   INTO  _l_tree.a, _l_tree.b
   USING _parent_id;
END
$func$;

小提琴here

我同意这是相当棘手的。人们可能会认为复合字段被视为单个字段(就像简单类型一样)。但目前在PL/pgSQL赋值中并非如此。

the manual about composite types中的相关引用：

复合类型表示行或记录的结构；它是 本质上只是字段名及其数据类型的列表。 PostgreSQL允许以与使用简单类型相同的许多方式使用复合类型。

强调我的。
许多。不是全部。

相关：

• Use of custom return types in a FOR loop in plpgsql
• PostgreSQL: ERROR: 42601: a column definition list is required for functions returning "record"

---

旁白：考虑additional module ltree而不是种植您自己的&。如果继续使用您自己的复合类型，请考虑使用不同的名称，以避免与该模块混淆/冲突。

这篇关于执行函数时，复合数据类型的INTEGER：(2，2)&QOOT；类型的输入语法无效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！