I have a table with key-value pairs for managing user settings. This approach is known as the “bag of properties” and one of its benefits is you don’t need schema changes when a new property is required. You simply add a record to the bag of properties table instead.
Now, I need to retrieve multiple properties inside a function (or stored procedure). But since the results are returned as multiple rows, with this approach I cannot do a simple:
SELECT prop1, prop2 FROM settings;
The query returns multiple rows so the results cannot be saved in local variables with the
SELECT col INTO var statement either. Yes, I can run two or more consecutive SELECTs but this doesn’t seem like a particularly efficient and elegant solution:
SELECT value FROM settings WHERE key = ‘prop1’; SELECT value FROM settings WHERE key = ‘prop2’;
A couple of ideas I have considered:
The function has
OUT parameters, in this case called prop1 (VARCHAR) and prop2 (BOOLEAN):
DECLARE settings_row RECORD; BEGIN FOR settings_row IN SELECT key, value FROM settings LOOP CASE WHEN settings_row.key = 'prop1' THEN prop1 := settings_row.value::VARCHAR; WHEN settings_row.key = 'prop2' THEN prop2 := settings_row.value::BOOLEAN; -- more cases handled here in case you needed other properties END CASE; END LOOP; RETURN; END;
Cross joining the settings table with itself, forcing the two properties to end up in columns. You could perhaps live with that if you needed just two properties but with any other value requiring yet another cross join this doesn't seem like a particularly scalable solution.
SELECT s1.value AS prop1, s2.value AS prop2 FROM settings s1 CROSS JOIN settings s2 WHERE s1.key = 'prop1' and s2.key = 'prop2';