Postgresql - returns an entire string as an array

Question

Is there a way you can apply to an array of the following result?

select pg_tables from pg_tables 

This will return only one column, however the data type is not an array.

Edit: I am using PostgreSql 9.1.4

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

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

Answer 1

Maybe it will be: 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:

first:

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

Output Example:

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

second:

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

Output Example:

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

third:

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

Output Example:

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

fourth:

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

Output Example:

 | 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 

Output Example:

 | 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

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; 

Output Example:

 | 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 | 

Answer 2

Another approach, use the hstore type, it is more reliable, can resolve the comma in the field value

Add the hstore contrib type by doing this once:

 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 the following:

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

Conclusion:

  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; 

Conclusion:

  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 a semicolon value is stored properly.

An astute reader will notice something on the above output. The hstore function does not provide the initial field order. To save it, put the table in a subquery, i.e.

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

Conclusion:

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

---

Used links: http://okbob.blogspot.com/2009/10/dynamic-access-to-record-fields-in.html

The following function to view: http://www.postgresonline.com/journal/archives/254-PostgreSQL-9.2-Preserving-column-names-of-subqueries.html

---

UPDATE

It seems that keeping column ordering through a subquery is just an accident. I tried to sort (for example, by first name).

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

The output does not preserve the order of the original columns:

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

Next, we’ll explore how to preserve the original column order.

---

UPDATE

If you need to sort the table to preserve the original column order, put ORDER BY outside the subquery:

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

Conclusion:

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

Now this is correct.

And choosing from a table in memory also works:

 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 

Conclusion:

  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 that the hstore_to_array functionality already has built-in functionality, just use 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; 

Conclusion:

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

One more example:

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

Conclusion:

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

Just use avalsツ

Live test : http://www.sqlfiddle.com/#!1/d41d8/388

Please note that although the sqlfiddle output does not have an array indicator (curly braces) and double quotes on , my passions are a ring, a drum and shades, "the result of avals is an array and the semicolon in them has a double quote in the actual results, you can check it on your pgAdmin or psql

Answer 3

This function works with all angles, including NULL values, empty strings, or special characters in values.

 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'); 

Casting to regclass avoids SQLi. Columns are not sorted in this version. More explanation for the methods and references used in this related answer .

Answer 4

string_to_array can help:

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