Postgre将整行作为数组返回

有没有办法将以下结果转换为数组？

select pg_tables from pg_tables

这将仅返回一列，但数据类型不是数组。

编辑：我正在使用PostgreSql 9.1.4

更新：我需要以下SQL语句的等效项，而无需编写列名，适用于每个表：

select 
    string_to_array(
    schemaname || '|' ||
    tablename || '|' || 
    tableowner || '|' ||
    coalesce(tablespace,'') || '|' ||
    hasindexes || '|' ||
    hasrules || '|' ||
    hastriggers
    ,'|')
from 
    pg_tables

可能是

这样的： http://www.sqlfiddle.com/#!1/d41d8/364

select translate(string_to_array(x.*::text,',')::text,'()','')::text[] 
from pg_tables as x

工作原理(由内而外(，5 个步骤：

第一：

select x.*::text from pg_tables as x;

示例输出：

|                                                            X |
----------------------------------------------------------------
|                    (pg_catalog,pg_statistic,postgres,,t,f,f) |
|                         (pg_catalog,pg_type,postgres,,t,f,f) |

第二：

select string_to_array(x.*::text,',') from pg_tables as x;

示例输出：

|                           STRING_TO_ARRAY |
---------------------------------------------
| (pg_catalog,pg_statistic,postgres,,t,f,f) |
|      (pg_catalog,pg_type,postgres,,t,f,f) |

第三：

select string_to_array(x.*::text,',')::text from pg_tables as x;

示例输出：

|                               STRING_TO_ARRAY |
-------------------------------------------------
| {(pg_catalog,pg_statistic,postgres,"",t,f,f)} |
|      {(pg_catalog,pg_type,postgres,"",t,f,f)} |

第四名：

select translate( string_to_array(x.*::text,',')::text, '()', '') from pg_tables as x

示例输出：

|                                   TRANSLATE |
-----------------------------------------------
| {pg_catalog,pg_statistic,postgres,"",t,f,f} |
|      {pg_catalog,pg_type,postgres,"",t,f,f} |

最后：

select translate( string_to_array(x.*::text,',')::text, '()', '')::text[] 
from pg_tables as x

示例输出：

|                               TRANSLATE |
-------------------------------------------
| pg_catalog,pg_statistic,postgres,,t,f,f |
|      pg_catalog,pg_type,postgres,,t,f,f |

实时测试：http://www.sqlfiddle.com/#!1/d41d8/373

为了证明它有效：

with a as 
(
  select translate( string_to_array(x.*::text,',')::text, '()', '')::text[] as colArray 
  from pg_tables as x
)
select row_number() over(), unnest(colArray)
from a;

示例输出：

| ROW_NUMBER |                  UNNEST |
----------------------------------------
|          1 |              pg_catalog |
|          1 |            pg_statistic |
|          1 |                postgres |
|          1 |                         |
|          1 |                       t |
|          1 |                       f |
|          1 |                       f |
|          2 |              pg_catalog |
|          2 |                 pg_type |
|          2 |                postgres |
|          2 |                         |
|          2 |                       t |
|          2 |                       f |
|          2 |                       f |

另一种方法，使用hstore类型，这样更健壮，可以解决字段值中的逗号

添加 hstore contrib 类型，方法是执行一次：

CREATE EXTENSION hstore;

创建此函数：

create or replace function hstore_to_array(r hstore) returns text[] as
$$
begin
    return array(select (each(r)).value);
end;
$$ language 'plpgsql';

然后试试这个：

select hstore_to_array(hstore(r)) from pg_tables r limit 10;

输出：

                  hstore_to_array                  
---------------------------------------------------
 {f,pg_statistic,t,pg_catalog,postgres,NULL,f}
 {f,pg_type,t,pg_catalog,postgres,NULL,f}
 {f,pg_attribute,t,pg_catalog,postgres,NULL,f}
 {f,xx,t,public,postgres,NULL,f}
 {f,yy,t,public,postgres,NULL,f}
 {f,tblx,f,public,postgres,NULL,f}
 {f,pg_authid,t,pg_catalog,postgres,pg_global,f}
 {f,pg_proc,t,pg_catalog,postgres,NULL,f}
 {f,pg_class,t,pg_catalog,postgres,NULL,f}
 {f,pg_database,t,pg_catalog,postgres,pg_global,f}
(10 rows)

再比如：

create table Beatle(firstname text, middlename text, lastname text);

insert into Beatle(firstname, middlename, lastname) values
('John','Winston','Lennon'),
('Paul','','McCartney'),
('George',NULL,'Harrison'),
('Ringo','my passions are ring,drum and shades','Starr');

查询：

select hstore_to_array(hstore(b)) from Beatle b;

输出：

                   hstore_to_array                    
------------------------------------------------------
 {Lennon,John,Winston}
 {McCartney,Paul,""}
 {Harrison,George,NULL}
 {Starr,Ringo,"my passions are ring,drum and shades"}
(4 rows)

如我们所见，即使是带逗号的值也会正确保留。

不过，精明的读者会注意到上述输出中的一些内容。hstore 函数不会预先确定字段的原始顺序。为了保留它，将表放在子查询上，即

select hstore_to_array(hstore(b)) from (select * from Beatle) as b

输出：

                   hstore_to_array                    
------------------------------------------------------
 {John,Winston,Lennon}
 {Paul,"",McCartney}
 {George,NULL,Harrison}
 {Ringo,"my passions are ring,drum and shades",Starr}
(4 rows)

参考文献： http://okbob.blogspot.com/2009/10/dynamic-access-to-record-fields-in.html

下一个要关注的功能：http://www.postgresonline.com/journal/archives/254-PostgreSQL-9.2-Preserving-column-names-of-subqueries.html

更新

看起来通过子查询保留列排序只是侥幸。我尝试进行排序(例如名字(。

select hstore_to_array(hstore(b)) 
from (select * from Beatle order by firstname) as b

输出不再保留原始列顺序：

                   hstore_to_array                    
------------------------------------------------------
 {Harrison,George,NULL}
 {Lennon,John,Winston}
 {McCartney,Paul,""}
 {Starr,Ringo,"my passions are ring,drum and shades"}
(4 rows)

将进一步研究如何保留原始列顺序。

更新

如果需要对表进行排序，为了保留原始列顺序，请将ORDER BY放在子查询之外：

select hstore_to_array(hstore(b)) 
from (select * from Beatle) as b order by firstname;

输出：

                   hstore_to_array                    
------------------------------------------------------
 {George,NULL,Harrison}
 {John,Winston,Lennon}
 {Paul,"",McCartney}
 {Ringo,"my passions are ring,drum and shades",Starr}
(4 rows)

现在没错了。

从内存表中进行选择也有效：

select hstore_to_array(hstore(b)) 
from 
(
    select * from 
    (values
        ('John',1940,'Winston','Lennon'),
        ('Paul',1942,'','McCartney'),
        ('George',1943,NULL,'Harrison'),
        ('Ringo',1940,'my passions are ring,drum and shades','Starr')
    ) as x(Firstname,BirthYear,Middlename,Lastname)
) as b     
order by BirthYear desc, Firstname desc

输出：

                      hstore_to_array                      
-----------------------------------------------------------
 {George,1943,NULL,Harrison}
 {Paul,1942,"",McCartney}
 {Ringo,1940,"my passions are ring,drum and shades",Starr}
 {John,1940,Winston,Lennon}
(4 rows)

更新

事实证明，功能hstore_to_array已经是内置功能，只需使用avals：http://www.postgresql.org/docs/9.1/static/hstore.html

select 
    avals (hstore(b))
from 
(
    select * from 
    (values
        ('John',1940,'Winston','Lennon'),
        ('Paul',1942,'','McCartney'),
        ('George',1943,NULL,'Harrison'),
        ('Ringo',1940,'my passions are ring,drum and shades','Starr')
    ) as x(Firstname,BirthYear,Middlename,Lastname)
) as b 
order by BirthYear desc, Firstname desc;

输出：

                           avals                           
-----------------------------------------------------------
 {George,1943,NULL,Harrison}
 {Paul,1942,"",McCartney}
 {Ringo,1940,"my passions are ring,drum and shades",Starr}
 {John,1940,Winston,Lennon}
(4 rows)

另一个示例：

select avals(hstore(b)) 
from (select * from Beatle) as b order by Firstname;

输出：

                        avals
------------------------------------------------------
 {George,NULL,Harrison}
 {John,Winston,Lennon}
 {Paul,"",McCartney}
 {Ringo,"my passions are ring,drum and shades",Starr}
(4 rows)

只需使用avalsツ

现场测试：http://www.sqlfiddle.com/#!1/d41d8/388

请注意，虽然sqlfiddle输出没有数组指示符(大括号(和双引号">我的激情是环，鼓和阴影">，但avals结果是一个数组，其中带有逗号的字符串在实际结果中有双引号，您可以在pgAdmin或psql上对其进行测试

此函数适用于所有极端情况，包括值中的NULL值、空字符串或特殊字符。

CREATE OR REPLACE FUNCTION f_rows_to_arr(_tbl text)
  RETURNS SETOF text[] AS
$BODY$
BEGIN
RETURN QUERY EXECUTE '
SELECT ARRAY[' || (
        SELECT string_agg(quote_ident(attname) || '::text', ',')
        FROM   pg_catalog.pg_attribute 
        WHERE  attrelid = _tbl::regclass  -- valid, visible table name 
        AND    attnum > 0                 -- exclude tableoid & friends
        AND    attisdropped = FALSE       -- exclude dropped columns
        ) || ' ]
FROM   ' || _tbl::regclass;
END;
$BODY$ LANGUAGE plpgsql;

叫：

SELECT * FROM f_rows_to_arr ('mytbl');

regclass的强制转换避免了SQLi。在此版本中不对列进行排序。有关此相关答案中使用的技术和链接的更多说明。

string_to_array可能会有所帮助：

SELECT string_to_array(pg_tables::text,','::text) FROM pg_tables;

相关内容

最新更新

热门标签：