Update PostgreSQL hstore field with sql variable

Question

I have table files which has hstore column details. In my sql statement I insert data to it:

  UPDATE files SET details =  'new_users=>new_users_count'::hstore where id = v_file_id;

but I want to update this hstore field not with string but with variable that is available in my sql statement. How can I do this?

Answer 1

PL/pgSQL can't detect variables inside a string literal. You need to use the "constructor" method of the hstore type to pass a variable:

UPDATE files 
   SET details = hstore('new_users', p_new_user_count)
where id = v_file_id;

If p_new_user_count is defined as a number (rather than a varchar or text) you need to cast this to a text value:

UPDATE files 
   SET details = hstore('new_users', p_new_user_count::text)
where id = v_file_id;

Edit after the question was changed:

To do this for multiple variables you can either concatenate two hstore values:

details = hstore('new_users', p_new_user_count::text)||hstore('post_count', p_post_count::text)

or use arrays:

details = hstore(array['new_users','post_count'], array[p_user_count, p_post_count]::text[]);

This is all documented in the manual: http://www.postgresql.org/docs/current/static/hstore.html