Postgresql - 将整行作为数组返回
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11469059/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgresql - return entire row as array
提问by Tomas Greif
Is there a way how you can cast the following result to array?
有没有办法将以下结果转换为数组？
select pg_tables from pg_tables
This will return one column only, however the data type is not array.
这将只返回一列，但数据类型不是数组。
Edit: I'm using PostgreSql 9.1.4
编辑：我使用的是 PostgreSql 9.1.4
Update: I need an equivalent of the following SQL statement, without the need to write column names, applicable for every table:
更新：我需要一个相当于下面的 SQL 语句，不需要写列名，适用于每个表：
select
string_to_array(
schemaname || '|' ||
tablename || '|' ||
tableowner || '|' ||
coalesce(tablespace,'') || '|' ||
hasindexes || '|' ||
hasrules || '|' ||
hastriggers
,'|')
from
pg_tables
回答by Michael Buen
Might be this: http://www.sqlfiddle.com/#!1/d41d8/364
可能是这样的：http://www.sqlfiddle.com/#!1/d41d8/ 364
select translate(string_to_array(x.*::text,',')::text,'()','')::text[]
from pg_tables as x
How it works (inside-out), 5 steps:
工作原理（由内而外），5 个步骤：
1st:
第一：
select x.*::text from pg_tables as x;
Sample Output:
示例输出：
| X |
----------------------------------------------------------------
| (pg_catalog,pg_statistic,postgres,,t,f,f) |
| (pg_catalog,pg_type,postgres,,t,f,f) |
2nd:
第二：
select string_to_array(x.*::text,',') from pg_tables as x;
Sample Output:
示例输出：
| STRING_TO_ARRAY |
---------------------------------------------
| (pg_catalog,pg_statistic,postgres,,t,f,f) |
| (pg_catalog,pg_type,postgres,,t,f,f) |
3rd:
第三：
select string_to_array(x.*::text,',')::text from pg_tables as x;
Sample Output:
示例输出：
| STRING_TO_ARRAY |
-------------------------------------------------
| {(pg_catalog,pg_statistic,postgres,"",t,f,f)} |
| {(pg_catalog,pg_type,postgres,"",t,f,f)} |
4th:
第四：
select translate( string_to_array(x.*::text,',')::text, '()', '') from pg_tables as x
Sample Output:
示例输出：
| TRANSLATE |
-----------------------------------------------
| {pg_catalog,pg_statistic,postgres,"",t,f,f} |
| {pg_catalog,pg_type,postgres,"",t,f,f} |
Finally:
最后：
select translate( string_to_array(x.*::text,',')::text, '()', '')::text[]
from pg_tables as x
Sample Output:
示例输出：
| TRANSLATE |
-------------------------------------------
| pg_catalog,pg_statistic,postgres,,t,f,f |
| pg_catalog,pg_type,postgres,,t,f,f |
Live test: http://www.sqlfiddle.com/#!1/d41d8/373
现场测试：http: //www.sqlfiddle.com/#!1/d41d8/ 373
To prove that it works:
为了证明它有效：
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;
Sample output:
示例输出：
| 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 |
回答by Michael Buen
Another approach, use hstoretype, this is more robust, can solve the comma in a field's value
另一种方法，使用hstore类型，这个更健壮，可以解决字段值中的逗号
Add the hstorecontrib type, by executing this once:
添加hstorecontrib 类型，执行一次：
CREATE EXTENSION hstore;
Create this function:
创建这个函数：
create or replace function hstore_to_array(r hstore) returns text[] as
$$
begin
return array(select (each(r)).value);
end;
$$ language 'plpgsql';
Then try this:
然后试试这个：
select hstore_to_array(hstore(r)) from pg_tables r limit 10;
Output:
输出：
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)
Another example:
另一个例子：
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');
Query:
询问：
select hstore_to_array(hstore(b)) from Beatle b;
Output:
输出：
hstore_to_array
------------------------------------------------------
{Lennon,John,Winston}
{McCartney,Paul,""}
{Harrison,George,NULL}
{Starr,Ringo,"my passions are ring,drum and shades"}
(4 rows)
As we can see, even the value with comma is preserved properly.
正如我们所见，即使是带逗号的值也被正确保存。
An astute reader will notice something on the above output though. The hstore function doesn't preseve the original order of fields. In order to preserve it, put the table on subquery, i.e.
不过，精明的读者会注意到上述输出中的某些内容。hstore 函数不会保留字段的原始顺序。为了保留它，将表放在子查询上，即
select hstore_to_array(hstore(b)) from (select * from Beatle) as b
Output:
输出：
hstore_to_array
------------------------------------------------------
{John,Winston,Lennon}
{Paul,"",McCartney}
{George,NULL,Harrison}
{Ringo,"my passions are ring,drum and shades",Starr}
(4 rows)
References used: http://okbob.blogspot.com/2009/10/dynamic-access-to-record-fields-in.html
使用的参考资料：http: //okbob.blogspot.com/2009/10/dynamic-access-to-record-fields-in.html
Next feature to watch: http://www.postgresonline.com/journal/archives/254-PostgreSQL-9.2-Preserving-column-names-of-subqueries.html
下一个要观看的功能：http: //www.postgresonline.com/journal/archives/254-PostgreSQL-9.2-Preserving-column-names-of-subqueries.html
UPDATE
更新
It looks like the preservation of column ordering via subquery is just a fluke. I tried putting a sorting(e.g. on firstname).
看起来通过子查询保留列排序只是侥幸。我尝试进行排序（例如在名字上）。
select hstore_to_array(hstore(b))
from (select * from Beatle order by firstname) as b
The output doesn't preserve the original columns order anymore:
输出不再保留原始列顺序：
hstore_to_array
------------------------------------------------------
{Harrison,George,NULL}
{Lennon,John,Winston}
{McCartney,Paul,""}
{Starr,Ringo,"my passions are ring,drum and shades"}
(4 rows)
Will investigate further how to preserve original columns order.
将进一步研究如何保留原始列顺序。
UPDATE
更新
If you need sort the table, in order to preserve the original columns order, put the ORDER BYoutside of subquery:
如果需要对表进行排序，为了保留原来的列顺序，ORDER BY把子查询放在外面：
select hstore_to_array(hstore(b))
from (select * from Beatle) as b order by firstname;
Output:
输出：
hstore_to_array
------------------------------------------------------
{George,NULL,Harrison}
{John,Winston,Lennon}
{Paul,"",McCartney}
{Ringo,"my passions are ring,drum and shades",Starr}
(4 rows)
It's correct now.
现在是正确的。
And selecting from in-memory table works too:
从内存表中选择也有效：
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
Output:
输出：
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)
UPDATE
更新
It turns out functionality hstore_to_arrayis a built-in functionality already, just use avals: http://www.postgresql.org/docs/9.1/static/hstore.html
事实证明功能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;
Output:
输出：
avals
-----------------------------------------------------------
{George,1943,NULL,Harrison}
{Paul,1942,"",McCartney}
{Ringo,1940,"my passions are ring,drum and shades",Starr}
{John,1940,Winston,Lennon}
(4 rows)
Another sample:
另一个示例：
select avals(hstore(b))
from (select * from Beatle) as b order by Firstname;
Output:
输出：
avals
------------------------------------------------------
{George,NULL,Harrison}
{John,Winston,Lennon}
{Paul,"",McCartney}
{Ringo,"my passions are ring,drum and shades",Starr}
(4 rows)
Just use avalsツ
就用avalsツ
Live test: http://www.sqlfiddle.com/#!1/d41d8/388
现场测试：http: //www.sqlfiddle.com/#!1/ d41d8/ 388
Please do note that although sqlfiddle output don't have an array indicator(curly brackets) and double quotes on "my passions are ring,drum and shades", the avals result is an array and the string with comma in them has double quote in actual results, you can test it on your pgAdmin or psql
请注意，尽管 sqlfiddle 输出没有数组指示符（大括号）和“我的激情是戒指，鼓和阴影”上的双引号，但 avals 结果是一个数组，其中带有逗号的字符串在其中有双引号实际结果，你可以在你的 pgAdmin 或 psql 上测试
回答by Erwin Brandstetter
This function works with all corner cases including NULLvalues, empty strings or special characters in the values.
此函数适用于所有极端情况，包括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;
Call:
称呼：
SELECT * FROM f_rows_to_arr ('mytbl');
The cast to regclassavoids SQLi. Columns are not sorted in this version.
More explanation for used techniques and links in this related answer.
回答by Frank Heikens
string_to_array might help:
string_to_array 可能有帮助：
SELECT string_to_array(pg_tables::text,','::text) FROM pg_tables;
