Postgres: convert a list of "name=value" values into a table

Question

Convert this string:

n1=10;n2=50;n3=60;n4=20

using something like this (in Postgres 9.x):

select * 
  from (some_engine_to_convert_this('n1=10;n2=50;n3=60;n4=20')) t

to get a result like this:

Name Value
v1   10
v2   50
v3   60
v4   20

PS: I can't create any function, so I need to use Postgres built in functions only.

Thanks in advance.

Answer 1

select split_part(nv, '=', 1) as name,
       split_part(nv, '=', 2) as value
from (
  select unnest(string_to_array('n1=10;n2=50;n3=60;n4=20',';'))
) as t (nv);

string_to_array first creates an array of Key/Value pairs. unnest turns that into rows and the key/value elements are then extracted from the result using split_part.

Alternatively the combination of unnest and string_to_array can be combined using regexp_split_to_table but the regex functions are usually slower. Not sure which one would be more efficient here.

SQLFiddle example: http://sqlfiddle.com/#!15/d41d8/2991