循环通过RECORD的列 [英] Loop through columns of RECORD

问题描述

我需要通过key / index循环访问 RECORD 项目，就像我可以在其他编程语言中使用数组结构一样。

例如：

  DECLARE 
 data1 record; 
 data2文本; 
 ... 
 BEGIN 
 ... 
 FOR data1 IN 
 SELECT 
 * 
 FROM 
 sometable 
 LOOP 
 
 for data2 IN 
 SELECT 
 unnest（data1） - 这是行不通的！ 
 LOOP 
 RETURN NEXT data1 [data2]; - 像这样
 END LOOP; 
 
 END LOOP; 
  

解决方案

正如@Pavel所解释的那样，遍历一个记录，就像你可以遍历一个数组。但有几种方法 - 取决于您的具体要求。最终，因为您想要返回同一列中的所有值，所以您需要将它们转换为相同类型 - text 是显而易见的共同点，因为存在文本表示为每一种类型。

快速和肮脏的

说，你有一个表 integer ，a 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'） - 带逗号的文本
，（4，'，4，ex，'，'2012-10- 04'） - 带逗号和双引号的文本
  

然后解决方案可以很简单：

  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行在文本表示中有双引号外，但是数组的构造函数会需要它们逃脱b y \ 。不知道为什么这种不兼容性是存在的...

  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替换功能unnest_table（_tbl text）
 RETURNS SETOF文本语言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 
和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 扩展名并使用它< $ @ 
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屋！