Thread: How to use hstore

How to use hstore

From
Jan Eskilsson
Date:
Hi all


I log data in a table using a hstore field.

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.

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.


Re: How to use hstore

From
Sergey Konoplev
Date:
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


Re: How to use hstore

From
Jan Eskilsson
Date:
Hi Sergey


Thats exacly what i was looking for, thanks a million !


Best regards
Jan Eskilsson

2012/6/5 Sergey Konoplev <gray.ru@gmail.com>:
> 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



--
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.