在plpgsql的FOR循环中使用自定义返回类型 [英] Use of custom return types in a FOR loop in plpgsql

问题描述

下面的代码返回一个整数 1 ：

  CREATE TYPE my_test AS（
 foo Integer 
）; 
 
 CREATE FUNCTION foo_out（）
 RETURNS SETOF Integer AS 
 $$ 
 BEGIN 
 RETURN QUERY 
 SELECT 1 as foo; 
 END 
 $$ 
语言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（）; 
  

为什么相同的代码会返回：

< blockquote>

 错误：整数无效输入语法：（1）
  

如果将返回类型更改为：

  CREATE FUNCTION foo_out（）
 RETURNS SETOF my_test 
  

这也应该是整数！？可能是系统在类型整数和包含整数的自定义类型之间有所不同。什么应该改变，在这里可以使用自定义类型？

解决方案

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

当分配给记录或行类型 FOR 循环查询 form 行中的所有输出列。您的 SELECT 需要返回一个单独的，未找到的 integer 列，然后嵌套到与您的自定义类型匹配的行类型 my_test 。

您也可以在 FOR 循环中分配一个标量变量列表，在这种情况下，查询中的列从左到右分配给变量 - 不构成行。

如果列本身 是 行类型，则可以有一个嵌套级别。包含整数字段的行的文本表示是'（1）'（带圆括号！），这就是你在错误信息中看到的内容。

你可以修复通过从属性表示法中提取整行字段：

  SELECT（foo_out（））。* 
  

或者（更有效地用于多列）通过分解：

  SELECT * FROM foo_out（）
  

示例代码

  CREATE FUNCTION foo_out（）
 RETURNS SETOF  my_test  AS 
 $$ 
  SELECT'（1 ）':: my_test  
 $$ 
 LANGUAGE sql; 
 
 CREATE FUNCTION foo1（）
 RETURNS SETOF my_test AS 
 $$ 
 DECLARE 
 x my_test; 
 BEGIN 
 FOR x IN 
  SELECT * FROM foo_out（） 
 LOOP 
 RETURN NEXT x; 
 END LOOP; 
 END 
 $$ 
 LANGUAGE plpgsql;  

SQL小提琴

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

请记住，循环是很少需要的，因为大多数问题通过基于集合的方法（仅限SQL）更有效地解决。
$ b

Craig和Pavel的相关答案：

• 传递一个复合类型的数组到存储过程
  

The following code that I use returns an integer 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();

But why does the same code return:

ERROR:  invalid input syntax for integer: (1)

if I change the return type to:

CREATE FUNCTION foo_out() 
  RETURNS SETOF my_test 

Which also should be an integer!? It could be the case that the system differs between the type integer and a custom type that includes an integer. What should be changed that the use of custom types is possible here?

解决方案

my_test is a composite type, a row type, that contains a single integer field. It's not an integer.

When assigning to a record or row type in a FOR loop all output columns from the query form the row. Your SELECT needs to return a single, unnested integer column, which is then nested into a row type matching your custom type my_test.
You can also assign to a list of scalar variables in a FOR loop, in which case columns from the query are assigned left to right to variables as is - not forming a row.

If the column itself is a row type, you have one level of nesting to many. The text representation of a row containing an integer field is '(1)' (with parentheses!), and that's what you see in the error message.

You can fix that by extracting the integer field from the row with attribute notation:

SELECT (foo_out()).*

Or (more efficiently for multiple columns) by decomposing with:

SELECT * FROM foo_out()

Example Code

CREATE FUNCTION foo_out() 
  RETURNS SETOF my_test AS
$$ 
SELECT '(1)'::my_test
$$
LANGUAGE sql;

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

SQL Fiddle.

Don't quote the language name plpgsql. It's an identifier.

Remember that looping is rarely needed, since most problems are more efficiently solved with a set-based approach (SQL only).

Related answers by Craig and Pavel:

• Passing array of a composite type to stored procedure

这篇关于在plpgsql的FOR循环中使用自定义返回类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！