循环遍历 RECORD 列 [英] Loop through columns of RECORD

问题描述

我需要通过键/索引循环遍历类型 RECORD 项，就像我可以使用其他编程语言中的数组结构来做到这一点一样.

例如:

DECLARE数据1记录；数据2文本；...开始...FOR data1 IN选择*从有的环形对于数据 2 输入选择unnest(data1)——这是行不通的！环形返回下一个数据1[数据2];-- 像这样结束循环；结束循环；

解决方案

正如@Pavel 所解释的，它不能像遍历数组那样简单地遍历记录.但是有几种方法可以解决它 - 取决于您的确切要求.最终，由于您想返回同一列中的所有值，您需要将它们强制转换为相同的类型 - text 是明显的共同点，因为每种类型都有一个文本表示.>

又快又脏

假设您有一个包含 integer、text 和 date 列的表格.

CREATE TEMP TABLE tbl(a int, b text, c date);插入 tbl 值(1, '1text', '2012-10-01'),(2, '2text', '2012-10-02'),(3, ',3,ex,', '2012-10-03') -- 带逗号的文本,(4, '",4,"ex,"', '2012-10-04') -- 带逗号和双引号的文本

那么解决方案可以很简单:

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

适用于前两行，但不适用于第 3 行和第 4 行的特殊情况.
您可以轻松解决文本表示中的逗号问题:

SELECT unnest(('{' || trim(t::text, '()') || '}')::text[])从 tbl t哪里一个 <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[]; -- 有效

正确的解决方案

如果你事先知道列名，一个干净的解决方案会很简单:

SELECT unnest(ARRAY[a::text,b::text,c::text])从表

由于您对已知类型的记录进行操作，因此您只需查询系统目录:

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

把它放在一个带有动态 SQL 的函数中:

创建或替换函数 unnest_table(_tbl text)返回 SETOF 文本语言 plpgsql AS$func$开始返回查询执行'SELECT unnest(数组[' || (SELECT string_agg(a.attname || '::text', ',' ORDER BY a.attnum)FROM pg_catalog.pg_attribute a哪里 a.attrelid = _tbl::regclassAND a.attnum >0AND a.attisdropped = false) ||'])从'||_tbl::regclass;结尾$func$;

调用:

SELECT unnest_table('tbl') AS val

返回:

val-----11个文本2012-10-0122文本2012-10-023,3,ex,2012-10-034",4,"例如,"2012-10-04

无需安装其他模块即可工作.另一种选择是安装 hstore 扩展并使用它就像@Craig 演示的.

I need to loop through type RECORD items by key/index, like I can do this using array structures in other programming languages.

For example:

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;

解决方案

As @Pavel explained, it is not simply possible to traverse a record, like you could traverse an array. But there are several ways around it - depending on your exact requirements. Ultimately, since you want to return all values in the same column, you need to cast them to the same type - text is the obvious common ground, because there is a text representation for every type.

Quick and dirty

Say, you have a table with an integer, a text and a date column.

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

Then the solution can be a simple as:

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

Works for the first two rows, but fails for the special cases of row 3 and 4.
You can easily solve the problem with commas in the text representation:

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

This would work fine - except for line 4 which has double quotes in the text representation. Those are escaped by doubling them up. But the array constructor would need them escaped by . Not sure why this incompatibility is there ...

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

Yields:

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

But you would need:

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

Proper solution

If you knew the column names beforehand, a clean solution would be simple:

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

Since you operate on records of well know type you can just query the system catalog:

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

Put this in a function with dynamic 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$;

Call:

SELECT unnest_table('tbl') AS val

Returns:

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

This works without installing additional modules. Another option is to install the hstore extension and use it like @Craig demonstrates.

这篇关于循环遍历 RECORD 列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！