Re: How to use hstore - Mailing list pgsql-sql

On Mon, Jun 4, 2012 at 4:08 PM, Jan Eskilsson <janeskil1525@gmail.com> wrote:
> My problem is that i would like to be able to retrieve a group of
> records from the hstore table and present them in a grid so I would
> like to convert the hstore records back to the original table format.
> In the manual i found an example how to convert to a predefined record
> type, exactly what i like to do but the example works great as long as
> there is only one record to convert.

I think this will answer your question:

grayhemp=# \d t      Table "public.t"Column |  Type   | Modifiers
--------+---------+-----------i      | integer |t      | text    |h      | hstore  |

grayhemp=# select * from t;i |  t  |         h
---+-----+--------------------1 | aaa |2 | bbb | "a"=>"1", "b"=>"2"3 | ccc | "a"=>"1", "c"=>"3"
(3 rows)

grayhemp=# \d r      Table "public.r"Column |  Type   | Modifiers
--------+---------+-----------a      | integer |b      | integer |c      | integer |

grayhemp=# select (populate_record(null::r, h)).* from t;a | b | c
---+---+---  |   |1 | 2 |1 |   | 3
(3 rows)

>
> SELECT * FROM populate_record(null::test,
>                              '"col1"=>"456", "col2"=>"zzz"');.
>
> But i like to to something like this
> SELECT * FROM populate_record(null::test,
>                              (select logfield from logtable where
> '"col1"=>"456" ));
>
>
> So i wonder how should i do if i have lots of hstore records to
> convert ? My SQl knowledge is a bit short :-)
>
>
> Thank you in advance!
>
>
> Jan Eskilsson
>
> --
> No trees were killed in the creation of this message.
> However, many electrons were terribly inconvenienced.
>
> Please consider the environment before you print this email.
>
> (1) The contents of this transmission are privileged and confidential
> and intended solely for the use of the addressee. Any disclosure,
> distribution or copying of the contents, other than by the addressee,
> is strictly prohibited. If you receive this transmission in error,
> please notify us immediately and destroy the material received.
> (2) All incoming and outgoing emails and any attachments are subjected
> to a virus scanner and are believed to be free of any virus, or any
> other defect which might affect any computer or IT system into which
> they are received and opened. Therefore, it is the responsibility of
> the recipient to ensure that they are virus free and no responsibility
> is accepted by Jan Eskilsson  for any loss or damage arising in any
> way from receipt or use thereof.
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql



--
Sergey Konoplev

a database and software architect
http://www.linkedin.com/in/grayhemp

Jabber: gray.ru@gmail.com Skype: gray-hemp Phone: +79160686204