How can I expand a PostgreSQL row into multiple rows for updating "custom fields"?

Question

I have a table used by one piece of 3rd party software (say user_fields) that has for each row a user_id, a field_id and a field_value. Crappy I know.

I also have another user table of my own, users, that contains the values needed to populate this, except the field_id which is "magic" and corresponds to the custom field mapping.

How can I write a select query that will turn this:

user_id | company_name       | first_name | country
---------------------------------------------------
1       | Nutty Choc Company | Si         | GB
2       | Ljubljanske R Us   | Pie        | IT

Into this?

user_id | field_value        | field_id
---------------------------------------
1       | Nutty Choc Company | 10
1       | Si                 | 11
1       | GB                 | 12
2       | Ljubljanske R Us   | 10
2       | Pie                | 11
2       | IT                 | 12

I'm using Postgresql 8.4, and intend the query to be fed to insert.

I had a stab at it by using unnest and an array, to expand the fields into rows, but it unnests after processing any other field in the select, and so just duplicates the value for the unnested rows.

Any ideas?

Cheers, Si

EDIT: formatting and clarification that dodgy name-value table is 3rd party.

Answer 1

My first idea would be to get rid of the name-value pair architecture. In a relational database you're just asking for never-ending trouble as you're already starting to see.

Now with the required scolding out of the way...

INSERT INTO My_Horrible_Name_Value_Pair_Table
(
     user_id,
     field_id,
     field_value
)
SELECT
     id,
     10,
     first_name
FROM
     Users
UNION ALL
SELECT
     id,
     11,
     country
FROM
     Users
UNION ALL
SELECT
     id,
     12,
     company_name
FROM
     Users

I'm sure that you can imagine how horribly this scales as you add more fields.

Answer 2

I'm adding this as a separate answer since it's quite different from the other that I've posted. You could try something like this:

SELECT
     U.id,
     UF.id AS field_id,
     CASE UF.id
          WHEN 10 THEN U.first_name
          WHEN 11 THEN U.country
          WHEN 12 THEN U.company_name
          ELSE NULL
     END AS field_value
FROM
     Users U
CROSS JOIN User_Fields UF

This is assuming that you have a User_Fields table (or whatever it might be named) which gives you all of the user fields that are possible. You would have to keep your CASE statement up to date though.