postgresql - 在 plpgsql 的 FOR 循环中使用自定义返回类型

Question

我使用的以下代码返回一个整数 1:

CREATE TYPE my_test AS (
   foo Integer
  );

CREATE FUNCTION foo_out() 
  RETURNS SETOF Integer AS
$$ 
BEGIN
RETURN QUERY
SELECT 1 as foo;
END
$$
LANGUAGE plpgsql;

CREATE FUNCTION foo1()
  RETURNS SETOF my_test
AS $$
DECLARE
    x my_test;
BEGIN
    FOR x IN    
        SELECT foo_out()
    LOOP     
        RETURN NEXT x;
    END LOOP;
END;
$$
LANGUAGE 'plpgsql';

select * from foo1();

但为什么同样的代码会返回:

ERROR:  invalid input syntax for integer: (1)

如果我将返回类型更改为:

CREATE FUNCTION foo_out() 
  RETURNS SETOF my_test 

这也应该是一个整数!？可能是系统在整数类型和包含整数的自定义类型之间存在差异。应该改变什么才能在这里使用自定义类型？

Answer 1

my_test 是一种复合类型，一种行类型，包含一个整数字段。它不是整数。

在 FOR loop 中分配给记录或行类型时来自查询的所有输出列形成行。您的 SELECT 需要返回单个未嵌套的 integer 列，然后将其嵌套到与您的自定义类型 my_test 匹配的行类型中。
您还可以在 FOR 循环中分配给标量变量列表，在这种情况下，查询中的列按原样从左到右分配给变量 - 不形成一行.

如果列本身是行类型，则您有一层到多层的嵌套。包含整数字段的行的文本表示形式是“(1)”(带括号!)，这就是您在错误消息中看到的内容。

您可以通过 extracting the integer field from the row with attribute notation 解决这个问题:

SELECT (foo_out()).*

或者(对于多列更有效)分解为:

SELECT * FROM foo_out()

示例代码

CREATE FUNCTION foo_out() 
  RETURNS SETOF <b>my_test</b>
  LANGUAGE sql AS
'<b>SELECT ''(1)''::my_test</b>';

CREATE FUNCTION foo1()
  RETURNS SETOF my_test
  LANGUAGE plpgsql AS
$func$
DECLARE
   x my_test;
BEGIN
   FOR x IN    
      <b>SELECT * FROM foo_out()</b>
   LOOP     
      RETURN NEXT x;
   END LOOP;
END
$func$;

db<> fiddle here
<子>旧sqlfiddle

不要引用语言名称plpgsql。这是一个标识符。

请记住很少需要循环，因为使用基于集合的方法(仅限 SQL)可以更有效地解决大多数问题。

Craig 和 Pavel 的相关回答:

• Passing array of a composite type to stored procedure