Search code examples
postgresqlhstore

Easily output the hstore format of a table row

Is there a better way to get a row of a table into hstore format than going

SELECT hstore(ARRAY['col1','col2','col3'], ARRAY[col1::text, col2::text, col3::text]) FROM tbl;

It works, but I figure there has to be a better way than typing out each column. hstore takes a record type for input, but I couldn't figure out how to feed the single-row producing query into the function and make it happy. Postgres version 9.0.4.

Solution

  • Yes - you can cast row to hstore type with hstore() function.

    SELECT hstore(tbl.*) FROM tbl;

    Works for me:

    filip@filip=# select hstore(foo.*) from foo;
         hstore
------------------------
 "bar"=>"1", "baz"=>"2"
(1 row)

    See http://www.postgresql.org/docs/9.0/static/hstore.html#HSTORE-FUNC-TABLE