Postgresql – How to avoid multiple SELECTs from a bag of properties table?

plpgsqlpostgresql

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:

FOR..IN loop

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 join

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';

Best Answer

Why not return the properties as key/value pairs wrapped in a single JSON value? 

select jsonb_object_agg("key", value)
from settings 
where "key" in ('prop1','prop2');

That can easily be wrapped into a function: 

create or replace function get_properties(VARIADIC p_keys text[])
  returns jsonb
as
$$
  select jsonb_object_agg("key", value)
  from settings s
  where "key" = any(p_keys);
$$
language sql;

Then you can use

select get_properties('prop1','prop2');

Or if you do need it as columns:

select t.props ->> 'prop1' as prop1, 
       t.props ->> 'prop2' as prop2
from get_properties('prop1','prop2') as t(props);
Related Question