Re: How to use hstore

On Mon, Jun 4, 2012 at 4:08 PM, Jan Eskilsson <janeskil1525(at)gmail(dot)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(at)postgresql(dot)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(dot)ru(at)gmail(dot)com Skype: gray-hemp Phone: +79160686204