Skip site navigation (1) Skip section navigation (2)

Re: How to use hstore

From: Sergey Konoplev <gray(dot)ru(at)gmail(dot)com>
To: Jan Eskilsson <janeskil1525(at)gmail(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: How to use hstore
Date: 2012-06-05 06:05:24
Message-ID: CAL_0b1vixnR069O0PjLiWhbBquAk-oAqsqaRqS2z+du96uHrBg@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
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

In response to

Responses

pgsql-sql by date

Next:From: Jan EskilssonDate: 2012-06-05 06:14:07
Subject: Re: How to use hstore
Previous:From: Jan EskilssonDate: 2012-06-04 12:08:29
Subject: How to use hstore

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group