如何在PostgreSQL函数中声明rowtype数组? [英] How to declare an array of rowtype in a PostgreSQL function?

问题描述

我试图创建一个PostgreSQL函数，在该函数中，我将遍历查询的行并将其中一些存储在数组中，然后再进行更多处理.如何创建一个行类型数组?

I am trying to create a PostgreSQL function where I will loop over the rows of a query and store some of them in an array, before doing more stuff with it. How can I create an array of rowtype?

CREATE OR REPLACE FUNCTION forExample() RETURNS integer AS
$BODY$
DECLARE
    r "WEBHOST"%rowtype;
    b "WEBHOST"%rowtype[];   <- This does not work !!!
BEGIN
    FOR r IN SELECT * FROM "WEBHOST"
    LOOP
        array_append(b, r);
    END LOOP;
    RETURN 33;
END
$BODY$
LANGUAGE 'plpgsql';

上面的功能会更复杂，但是我为此问题提供了一个简化的版本.

The above function will be more complex, but I am providing a simplified version for this question.

推荐答案

CREATE OR REPLACE FUNCTION for_example()
  RETURNS integer AS
$func$
DECLARE
   r "WEBHOST";
   b "WEBHOST"[];         -- this works
BEGIN
   FOR r IN 
      SELECT * FROM "WEBHOST"
   LOOP
      b := b || r;        -- this, too
   END LOOP;

   RAISE NOTICE '%', b;   -- get feedback
   RETURN 33;
END
$func$  LANGUAGE plpgsql; -- and lose the quotes

%rowtype通常不是必需的.通常，表的关联行类型可以用作相同名称的类型.

%rowtype is generally not necessary. Normally, the associated rowtype of a table is available as type of the same name.

并且不引用语言名称.

您不能只在 plpgsql 中进行独立的函数调用.改用作业.

And you cannot just have stand-alone function calls in plpgsql. Using an assignment instead.

在Postgres中使用CaMeL-case标识符也不是一个好主意.使用合法的小写字母标识符使您的生活更轻松.

It's also not a good idea to use CaMeL-case identifiers in Postgres. Use legal, lower-case identifiers to make your life easier.

最适合的:使用汇总函数

The best for last: This can be much simpler with the aggregate function array_agg():

CREATE OR REPLACE FUNCTION for_example()
  RETURNS integer AS
$func$
DECLARE
   b "WEBHOST"[];
BEGIN
   SELECT array_agg(tbl) INTO b FROM "WEBHOST" tbl;       

   RAISE NOTICE '%', b;
   RETURN 33;
END
$func$  LANGUAGE plpgsql;

这篇关于如何在PostgreSQL函数中声明rowtype数组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！