Easy to output hstore table row format

Question

Is there a better way to get a table row in hstore format than go

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

This works, but I believe there should be a better way than printing each column. hstore takes the type of entry to input, but I could not figure out how to submit a request to create a single line to a function and make it happy. Postgres version 9.0.4.

Answer 1

Yes - you can assign an hstore-style string using the 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

Answer 2

Instead of * you should refer to the table alias:

 SELECT hstore(tbl) FROM tbl; 

With an asterisk, its longer and less independent syntax:

 SELECT hstore(tbl.*) FROM tbl;