CODEWITHSUNDEEP
postgresql
Adam Piotrowski
Adam Piotrowski

Reputation: 3315

How to split a string in a smart way?

Function string_to_array splits strings without grouping substrings in apostrophes:

# select unnest(string_to_array('one, "two,three"', ','));
 unnest
--------
 one
 "two
 three"
(3 rows)

I would like to have a smarter function, like this:

# select unnest(smarter_string_to_array('one, "two,three"', ','));
 unnest
--------
 one
 two,three
(2 rows)

Purpose.

I know that COPY command does it in a proper way, but I need this feature internally. I want to parse a text representation of rows of existing table. Example:

# select * from dataset limit 2;
 id |      name       | state  
----+-----------------+--------
  1 | Smith, Reginald | Canada
  2 | Jones, Susan    | 
(2 rows)

# select dataset::text from dataset limit 2;
           dataset            
------------------------------
 (1,"Smith, Reginald",Canada)
 (2,"Jones, Susan","")
(2 rows)

I want to do it dynamically in a plpgsql function for different tables. I cannot assume constant number of columns of a table nor a format of columns values.

Upvotes: 2

Views: 291

Answers (1)

klin
klin

Reputation: 121889

There is a nice method to transpose a whole table into a one-column table:

select (json_each_text(row_to_json(t))).value from dataset t;

If the column id is unique then

select id, array_agg(value) arr from (
    select row_number() over() rn, id, value from (
        select id, (json_each_text(row_to_json(t))).value from dataset t
        ) alias
    order by id, rn
    ) alias
group by id;

gives you exactly what you want. Additional query with row_number() is necessary to keep original order of columns.

Upvotes: 1

Related Questions