在postgresql函数中选择自定义类型时格式错误的数组文字

I have a postgresql custom type, containing arrays

我有一个postgresql自定义类型，包含数组

CREATE TYPE route_part (
  nodea bigint[],
  edgea bigint[],
  geom geometry
);

And a function, returning this type

和一个函数，返回这种类型

CREATE OR REPLACE FUNCTION net.get_route_part_dist(int8, int8, int4)
RETURNS route_part
AS
$BODY$
DECLARE routerec route_part;
BEGIN
SELECT INTO routerec
  ...
;

RETURN routerec;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

This function works as expected and returns route_part composite type. I'm trying to use it inside another "wrapper" function, that looks like this:

此函数按预期工作并返回route_part复合类型。我试图在另一个“包装”函数中使用它，看起来像这样：

CREATE OR REPLACE FUNCTION net.get_route(beg_ int8, end_ int8, mida int8[], dist int4)
RETURNS route_part
AS
$BODY$
DECLARE routerec route_part;
BEGIN
SELECT INTO routerec net.get_route_part_dist(beg_, end_, dist);
RETURN routerec;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

I get an error on the select query.

我在选择查询时遇到错误。

ERROR:  malformed array literal: "(
{303513543,2289605239,...,306687989}","
{2585314,264212,...,1088633}",
0102000020110F000029000000AE47E11A81754F41C3F5280C07F25C)"
DETAIL:  Array value must start with "{" or dimension information.

I don't cast types to strings or other types, so I can't figure out why the returned value considered to have a malformed array. Any clues?

我没有将类型转换为字符串或其他类型，所以我无法弄清楚为什么返回的值被认为有一个格式错误的数组。有什么线索吗？

1 个解决方案

#1

The solution is to assign decomposed values:

解决方案是分配分解的值：

CREATE OR REPLACE FUNCTION net.get_route(beg_ int8, end_ int8, mida int8[], dist int4)
  RETURNS route_part AS
$func$
DECLARE
   routerec route_part;
BEGIN
   SELECT INTO routerec * FROM net.get_route_part_dist(beg_, end_, dist);
   RETURN routerec;
END
$func$ LANGUAGE plpgsql;

Since routerec is a row type (composite type), The columns of the SELECT list must match the columns of the row type. The form you had would attempt to fit the value (as a whole) returned by net.get_route_part_dist() into the first column of routerec.

由于routerec是行类型（复合类型），因此SELECT列表的列必须与行类型的列匹配。您拥有的表单将尝试将net.get_route_part_dist（）返回的值（作为一个整体）放入routerec的第一列。

Quoting the manual:

引用手册：

If a row or a variable list is used as target, the query's result columns must exactly match the structure of the target as to number and data types

如果将行或变量列表用作目标，则查询的结果列必须与目标的结构完全匹配，以及数字和数据类型

Postgres tries to fit your composite type (or rather its text representation) into bigint[], the first column of the composite type routerec. The error message you quoted is the consequence.

Postgres尝试将您的复合类型（或者更确切地说是其文本表示）放入bigint []，即复合类型routerec的第一列。您引用的错误消息是结果。

Explanation in the manual:

手册中的说明：

If the expression's result data type doesn't match the variable's data type, the value will be coerced as though by an assignment cast (see Section 10.4). If no assignment cast is known for the pair of data types involved, the PL/pgSQL interpreter will attempt to convert the result value textually, that is by applying the result type's output function followed by the variable type's input function. Note that this could result in run-time errors generated by the input function, if the string form of the result value is not acceptable to the input function.

如果表达式的结果数据类型与变量的数据类型不匹配，则该值将被强制转换，就像通过赋值转换一样（参见第10.4节）。如果对于所涉及的数据类型对没有已知的赋值转换，则PL / pgSQL解释器将尝试以文本方式转换结果值，即通过应用结果类型的输出函数，然后应用变量类型的输入函数。请注意，如果输入函数不接受结果值的字符串形式，则可能导致输入函数生成运行时错误。

This can be confusing and fooled the first time as well. The distinction seems necessary since INTO allows assigning a list of target variables at once.
The bottom line is this: Decompose row types with SELECT * FROM ... when assigning to a row / record / composite type with INTO. Else it will be assigned to the first target column as a whole.

这也可能是第一次令人困惑和愚弄。区别似乎是必要的，因为INTO允许一次分配目标变量列表。底线是：当使用INTO分配到行/记录/复合类型时，使用SELECT * FROM ...分解行类型。否则，它将作为一个整体分配给第一个目标列。

Avoid these inefficient forms:
Like you commented:

避免这些效率低下的形式：像你评论的那样：

SELECT INTO routerec (net.get_route_part_dist(beg_, end_, dist)).nodea , (net.get_route_part_dist(beg_, end_, dist)).edgea , (net.get_route_part_dist(beg_, end_, dist)).geom;

SELECT INTO routerec（net.get_route_part_dist（beg_，end_，dist））。nodea，（net.get_route_part_dist（beg_，end_，dist））。edgea，（net.get_route_part_dist（beg_，end_，dist））。geom;

Or, less verbose, but equally inefficient:

或者，更简洁，但同样效率低下：

SELECT INTO routerec (net.get_route_part_dist(beg_, end_, dist)).*;

SELECT INTO routerec（net.get_route_part_dist（beg_，end_，dist））。*;

Each would evaluate the function multiple times - as opposed to:
SELECT INTO routerec * FROM net.get_route_part_dist(beg_, end_, dist)

每个人都会多次评估函数 - 而不是：SELECT INTO routerec * FROM net.get_route_part_dist（beg_，end_，dist）

Related:

有关：

• Use of custom return types in a FOR loop in plpgsql
• 在plpgsql中的FOR循环中使用自定义返回类型
• Passing array of a composite type to stored procedure
• 将复合类型的数组传递给存储过程

Simple alternative

The simple alternative for your simple case: direct assignment (without INTO):

简单案例的简单替代方案：直接分配（无INTO）：

  routerec := net.get_route_part_dist(beg_, end_, dist);
  RETURN routerec;

Simple assignment only allows a single target to begin with.
Or return the result directly:

简单分配仅允许单个目标开始。或者直接返回结果：

  RETURN net.get_route_part_dist(beg_, end_, dist);