CODEWITHSUNDEEP
sqlpostgresqlpostgresql-8.4
HP.
HP.

Reputation: 19896

Convert array to rows in Postgres

If I have something like this in SQL statement ('A','B','C'), how do I convert it into a column with multiple rows like this

col
---
 A
 B
 C

I cannot change the way that string is created (as it is injected into SQL query from external program). For example, I cannot make it as ['A','B','C'] (replace with square brackets). I could wrap anything around it though like [('A','B','C')] or whatever.

Any help?

UPDATE 1

I have PostgreSQL 8.4.20

Upvotes: 5

Views: 16954

Answers (2)

jspcal
jspcal

Reputation: 51924

The built-in regexp_split_to_table function will do this for you. Since you plan to inject it directly without escaping, use $$ (dollar quoting) from thibautg's answer.

select * from regexp_split_to_table(
    regexp_replace($$('A','B','C')$$, '^\(''|''\)+', '', 'g'),
    ''','''
);

Upvotes: 2

thibautg
thibautg

Reputation: 2052

You could create an ARRAY from VALUES and then unnest it:

SELECT 
    unnest(ARRAY[col_a, col_b, col_c]) 
FROM 
    (VALUES('A','B','C')) AS x(col_a, col_b, col_c)

Result:

| unnest |
|--------|
|      A |
|      B |
|      C |

Edit: you could also tweak jspcal's answer by using dollar quotes ($$) like this so you can concatenate your string into the SQL statement:

  SELECT * FROM regexp_split_to_table(
    regexp_replace(
      $$('A','B','C','D','foo')$$,
      '^\(''|''\)+', '', 'g'),
      ''','''
    );

Upvotes: 5

Related Questions