循环遍历RECORD的列

Question

我需要按键/索引循环遍历类型为RECORD的项目，就像其他编程语言中可以使用数组结构一样。

例如：

DECLARE
    data1    record;
    data2    text;
...
BEGIN
...
FOR data1 IN
    SELECT
        *
    FROM
        sometable
LOOP

    FOR data2 IN
        SELECT
            unnest( data1 )   -- THIS IS DOESN'T WORK!
    LOOP
        RETURN NEXT data1[data2];   -- SMTH LIKE THIS
    END LOOP;

END LOOP;

- RKI

---

根据使用的数据类型，有不同的解决方案。请添加一个典型的表定义和一些示例数据，以及所需的输出形式和完整的函数定义，包括参数。该函数是针对一个表还是不同的表？后者需要您通过参数提交表名并使用动态SQL... - Erwin Brandstetter

Answer 1

"As @Pavel已经解释过的，遍历一个记录并不像你遍历一个数组那么简单。但是，根据您的具体要求，有几种方法可以绕过去。最终，因为您想返回同一列中的所有值，所以您需要将它们转换为相同的类型-文本是明显的共同点，因为每种类型都有一个文本表示。

快速而简单的方法

假设您有一个包含一个整数、一个文本和一个日期列的表。

"

CREATE TEMP TABLE tbl(a int, b text, c date);
INSERT INTO tbl VALUES
 (1, '1text',     '2012-10-01')
,(2, '2text',     '2012-10-02')
,(3, ',3,ex,',    '2012-10-03')  -- text with commas
,(4, '",4,"ex,"', '2012-10-04')  -- text with commas and double quotes

那么解决方案可能就像这样简单：

SELECT unnest(string_to_array(trim(t::text, '()'), ','))
FROM   tbl t;

第一和第二行的工作正常，但在第三和第四行的特殊情况下失败。
您可以通过在文本表示中使用逗号轻松解决该问题。

SELECT unnest(('{' || trim(t::text, '()') || '}')::text[])
FROM   tbl t
WHERE  a < 4;

这会很好地运作 - 除了第4行在文本表示中有双引号。它们通过加倍来转义。但是数组构造器需要用\来转义它们。不确定为什么会存在这种不兼容性...

SELECT ('{' || trim(t::text, '()') || '}') FROM tbl t WHERE a = 4

产生：

{4,""",4,""ex,""",2012-10-04}

但您需要：

SELECT '{4,"\",4,\"ex,\"",2012-10-04}'::text[];  -- works

正确的解决方案

如果您事先知道列名，那么一个干净的解决方案将会很简单：

SELECT unnest(ARRAY[a::text,b::text,c::text])
FROM tbl

由于您操作的记录是众所周知的类型，因此可以直接查询系统目录：

SELECT string_agg(a.attname || '::text', ',' ORDER  BY a.attnum)
FROM   pg_catalog.pg_attribute a 
WHERE  a.attrelid = 'tbl'::regclass
AND    a.attnum > 0
AND    a.attisdropped = FALSE

将此代码放入一个带有动态SQL的函数中：

CREATE OR REPLACE FUNCTION unnest_table(_tbl text)
  RETURNS SETOF text LANGUAGE plpgsql AS
$func$
BEGIN

RETURN QUERY EXECUTE '
SELECT unnest(ARRAY[' || (
    SELECT string_agg(a.attname || '::text', ',' ORDER  BY a.attnum)
    FROM   pg_catalog.pg_attribute a 
    WHERE  a.attrelid = _tbl::regclass
    AND    a.attnum > 0
    AND    a.attisdropped = false
    ) || '])
FROM   ' || _tbl::regclass;

END
$func$;

电话：

SELECT unnest_table('tbl') AS val

返回：

val
-----
1
1text
2012-10-01
2
2text
2012-10-02
3
,3,ex,
2012-10-03
4
",4,"ex,"
2012-10-04

这个方法不需要安装额外的模块。另一个选择是安装 hstore 扩展并使用它，就像 @Craig 演示的那样。

- Erwin Brandstetter

---

谢谢！这看起来很酷，但如果我不“了解”表结构，这对我来说就不适用。 - RKI

@RomanKutsy：我认为你误解了。最终的解决方案只适用于那种情况。请再试着阅读一遍。 - Erwin Brandstetter

谢谢！现在我明白了。我会在几天内尝试您的解决方案。 - RKI

Answer 2

PL/pgSQL并不适合您想要做的事情。它不认为记录是可迭代的，而是由可能具有不同和不兼容数据类型的元组组成。

PL/pgSQL具有EXECUTE用于动态SQL，但EXECUTE查询不能直接引用PL/pgSQL变量，如NEW或其他记录。

您可以将记录转换为hstore键/值结构，然后对hstore进行迭代。使用each(hstore(the_record))，它会产生一个key,value元组的行集。所有值都被转换为它们的text表示形式。

这个玩具函数通过创建一个匿名的ROW(..)来演示对记录的迭代 - 它将具有列名f1，f2，f3 - 然后将其转换为hstore，迭代其列/值对，并返回每个对。

CREATE EXTENSION hstore;

CREATE OR REPLACE FUNCTION hs_demo()
RETURNS TABLE ("key" text, "value" text)
LANGUAGE plpgsql AS
$$
DECLARE
  data1 record;
  hs_row record;
BEGIN
  data1 = ROW(1, 2, 'test');
  FOR hs_row IN SELECT kv."key", kv."value" FROM each(hstore(data1)) kv
  LOOP
    "key" = hs_row."key";
    "value" = hs_row."value";
    RETURN NEXT;
  END LOOP;
END;
$$;

实际上，你永远不会这样编写代码，因为整个循环可以用一个简单的RETURN QUERY语句替代，而且它做的事情和each(hstore)一样 - 所以这只是为了展示each(hstore(record))的工作原理，上述函数实际上不应该被使用。

- Craig Ringer

---

非常感谢！我正在考虑这种方式。 - RKI

我猜这就是Erwin所指的 create or replace function row_to_jsonarray(r anyelement) returns json language sql immutable AS $$ select to_json(array (select value from each(hstore(r)) ) ); $$ ; 在 PostgreSQL 9.4中有效。但要注意，hstore不保证记录中的列顺序与生成的json相同。 - markop

Answer 3

这个功能在plpgsql中不受支持——记录（Record）与其他脚本语言不同，它不是哈希数组，而类似于C或ADA，因此无法实现此功能。您可以使用其他PL语言，如PLPerl或PLPython，或一些技巧——您可以使用HSTORE数据类型（扩展）或通过动态SQL进行迭代。

请参见如何使用动态SQL设置复合变量字段的值

但是，对此功能的请求通常意味着您做错了什么。当您使用PL/pgSQL时，必须与使用Javascript或Python时有所不同。

- Pavel Stehule

Answer 4

FOR data2 IN
    SELECT d
    from  unnest( data1 ) s(d)
LOOP
    RETURN NEXT data2;
END LOOP;

- Clodoaldo Neto

---

regress=# SELECT unnest(ROW(1,2,'test')); 错误：函数unnest(record)不存在 - Craig Ringer

@Craig 是的，他必须选择 data1.myArray。至少我认为它是一个数组，因为他试图对其进行 unnest 操作。 - Clodoaldo Neto

我认为他想要将记录“unnest”成键/值对。这在类型关系模型中根本不被支持，除非你像hstore一样将所有值都转换为文本。 - Craig Ringer

谢谢！但是你没有理解我的问题。 - RKI

Answer 5

如果在循环之前对结果进行排序，你能达到你想要的效果吗？

for rc in select * from t1 order by t1.key asc loop
 return next rc;
end loop;

这将完全满足您的需求。它也是执行此类任务的最快方法。

- Loek Bergman

---

谢谢！但是你没有理解我的问题。 - RKI

Answer 6

我找不到适当的循环记录的方法，所以我先将记录转换成JSON格式，再遍历JSON。

declare
    _src_schema varchar := 'db_utility';
    _targetjson json;
    _key   text;
    _value text;
BEGIN
    select row_to_json(c.*) from information_schema.columns c where c.table_name = prm_table and c.column_name = prm_column
        and c.table_schema = _src_schema into _targetjson;
        raise notice '_targetjson %', _targetjson;

        FOR _key, _value IN
        SELECT * FROM jsonb_each_text(_targetjson)
        LOOP
        -- do some math operation on its corresponding value
            RAISE NOTICE '%: %', _key, _value;
        END LOOP;
    return true;
end;

- Muhammad Awais

Answer 7

已经过去了10多年，仍然没有机会动态循环记录列。但是我有一个解决方案，可以稍微帮助一下。下面的函数将创建并返回一个JSON对象中的记录定义。

CREATE OR REPLACE FUNCTION transactions.f_get_record_definitions(p_txt_sql text)
 RETURNS json
 LANGUAGE plpgsql
AS $function$
declare
    j_rec_defs json;
    rec record;
    rec2 record;
    txt_type text;
    txt_j_build text;
begin
    execute p_txt_sql limit 1 into rec;
    txt_j_build := '{';
    j_rec_defs := row_to_json(rec)::json;
    for rec2 in
        select * from json_each(j_rec_defs)
    loop
        case json_typeof(rec2.value::json)
        when 'object' then txt_type := 'json';
        when 'string' then txt_type := 'text';
        when 'array' then txt_type := 'array';
        when 'boolean' then txt_type := 'bool';
        when 'number' then txt_type := 'numeric';
        else
            txt_type := 'json';
        end case;
        txt_j_build := txt_j_build||'"'||rec2.key||'":"'||txt_type||'",';
    end loop;
    txt_j_build := left(txt_j_build, -1)||'}';
    return txt_j_build::json;
end;
$function$
;

嗯，你只能使用json或jsonb类型，但在许多情况下这是可以的。如果有人知道如何直接从rec中使用pg_type获取类型，那就太好了。

我真的不知道这是否解决了初始问题，但是通过这个函数，你将能够创建typelists以在其他函数中动态返回记录或记录集。如果你不喜欢这些定义，你也可以在循环中简单地使用"return next rec2"而不是确定类型。

- Nicht von Bedeutung