Convert a query result to hstore

Question

I am using PostgreSQL 9.4. I need to convert subquery to hstore. I have this query: select code, value_string from dir. It returns

code  | value_string
------|--------------
CODE  | 1
ACC   | 2
...

How to convert his result to hstore('"ACC"=>"2", "CODE"=>"1", ...')?

I am looking something like this: SELECT hstore(select code, value_string from dir).

Answer 1

For the documentation:

hstore(text[], text[]) - construct an hstore from separate key and value arrays.

Use array_agg() as arguments for this function. Example:

create table dir (code text, value_string text);
insert into dir values
('CODE', 1),
('ACC', 2);

select hstore(array_agg(code), array_agg(value_string))
from dir;

         hstore          
-------------------------
 "ACC"=>"2", "CODE"=>"1"
(1 row)