1

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

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);
1
  • Really interesting solution, thank you so much for sharing this! Feb 22 '20 at 19:46
1
SELECT prop1, prop2
FROM   crosstab(
   'SELECT 1 as a, key, value 
    FROM   settings 
    ORDER  BY 1,2' 
   ) AS ct ("a" int,"prop1" text, "prop2" text);
1
  • This is actually the third solution I should've included it in the question. I initially wrapped this in a materialised view but I've given up as the whole solution seemed to have been overly engineered (function querying a view that wraps a pivoted table that, in turn, pulls data from a bag of props...). Also, if you needed another property, the signature of the view would need to change and at this point you might as well forget about the whole bag of props idea and go with an old school table with one prop per column. Thanks for taking time to provide this solution anyway. Feb 22 '20 at 19:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.