Is it possible to name SQL result columns from rows in another table? (Postgres)

Question

Basically I have users with a dynamic attributes table. Simplified:

SELECT * FROM users;
 id  |                  email                   
-----+------------------------------------------
  1 | example@example.com


SELECT * FROM user_attributes;
 id |      name      
----+----------------
  1 | Salutation
  2 | Given Name
  3 | Surname
  4 | Alias
  5 | Address
  6 | Address 2
  7 | Address 3
  8 | City
  9 | Region
....

SELECT * FROM user_attribute_values;
 client_id | attribute_id | value 
-----------+--------------+-------

What I'm looking to do is a SELECT that would return columns user_id, city, region where city & region are not empty.

The reason for the user_attributes table is one may want to store any number of custom fields about the user, and it's impossible to know beforehand what they will be to create them as columns of the user table.

Answer 1

Use INNER JOIN for that:

SELECT u.id, a_city.value AS city, a_region.value AS region
 FROM users u
 INNER JOIN user_attribute_values a_city ON a_city.client_id = u.id AND a_city.attribute_id = 8
 INNER JOIN user_attribute_values a_region ON a_region.client_id = u.id AND a_region.attribute_id = 9
 WHERE LENGTH(a_city.value) > 0
   AND LENGTH(a_region.value) > 0