PostgreSQL-将整个行作为数组返回 [英] Postgresql - return entire row as array

问题描述

有没有一种方法可以将以下结果转换为数组？

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.

编辑：我正在使用PostgreSql 9.1.4

I'm using PostgreSql 9.1.4

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

Update: I need an equivalent of the following SQL statement, without the need to write column names, applicable for every table:

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个步骤：

How it works (inside-out), 5 steps:

第一：

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 |

这篇关于PostgreSQL-将整个行作为数组返回的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！