CODEWITHSUNDEEP
postgresqlhstore
EvilAmarant7x
EvilAmarant7x

Reputation: 2069

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.

Upvotes: 7

Views: 3821

Answers (1)

filiprem
filiprem

Reputation: 7124

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

Upvotes: 13

Related Questions