I have a PostgreSQL table which stores attributes about different physical objects. It has the following fields:
attr_name (string e.g. attr_1, attr_2 etc.)
SELECT * FROM View1 WHERE attr_1 = 5 and attr_2 > 3 etc.
Here's an example query that returns the all the values with the highest priority in the format that you want:
SELECT object_name, (SELECT attr_value AS attr_1 FROM t WHERE t.object_name=t1.object_name AND attr_name='attr_1' ORDER BY priority DESC LIMIT 1), (SELECT attr_value AS attr_2 FROM t WHERE t.object_name=t1.object_name AND attr_name='attr_2' ORDER BY priority DESC LIMIT 1) FROM (SELECT DISTINCT object_name FROM t) t1;
I assumed that the table is called
t. The query returns 2
attr_??? columns. If you have more, you have to add more subqueries, one for each column. If you don't like that (e.g. because you have a large number of
attr_name), then we can do something that does not require to write a huge query but it will return the results as JSON objects (so it will be in different format).
If you want to create a view, just run the following:
CREATE VIEW myview AS SELECT ... ^^^ insert the query from above
If you need this to be as fast as possible, I would recommend using 2 tables: one for the highest priority values and one for everything else.