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):

  settings_row RECORD;
  FOR settings_row IN
    SELECT key, value FROM settings
      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


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.

  s1.value AS prop1, s2.value AS prop2
FROM settings s1
CROSS JOIN settings s2
  s1.key = 'prop1' and s2.key = 'prop2';

2 Answers 2


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
  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);
  • Really interesting solution, thank you so much for sharing this! Commented Feb 22, 2020 at 19:46
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);
  • 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. Commented Feb 22, 2020 at 19:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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