postgresql - 循环遍历 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;

Answer 1

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

快速而肮脏

比如说，您有一个包含integer、text 和date 列的表格。

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;

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

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扩展并使用它 like @Craig demonstrates .