Opposite function of hstore each function
Each function returns key/value pairs from hstore type.
Is there any opposite function that returns hstore type from key/value rows?
I know hstore (text[], text[]) can construct it. Is there any other choice?
I have a table with ID & Description columns and want to return hstore type
(ID1=>Desc1,ID2=>Desc2,....) that matches a query condition.
From top of my head, this is the only query I can think of
SELECT hstore ((SELECT array(SELECT CAST(ID as TEXT) FROM T1 WHERE ....)),
(SELECT array(SELECT Description FROM T1 WHERE ...)))
But I don't want to run the same where condition twice.
If I can construct a single array (K1, V1, K2, V2, ...) from key/value
rows, then I can use hstore(text[]) function.
Of course, I can create a stored function that loops through the rows and
construct it, that's the last resort and I don't like that approach.
Any idea?
Thank you,
Choon Park
ChoonSoo Park wrote:
Each function returns key/value pairs from hstore type.
Is there any opposite function that returns hstore type from key/value rows?I know hstore (text[], text[]) can construct it. Is there any other choice?
I have a table with ID & Description columns and want to return hstore type
(ID1=>Desc1,ID2=>Desc2,....) that matches a query condition.From top of my head, this is the only query I can think of
SELECT hstore ((SELECT array(SELECT CAST(ID as TEXT) FROM T1 WHERE ....)),
(SELECT array(SELECT Description FROM T1 WHERE ...)))But I don't want to run the same where condition twice.
You could try:
select hstore(array_agg(CAST(ID as TEXT)), array_agg(Description)) from T1 where ...
I don't have hstore loaded but that seems like it should work.
HTH
Bosco.
It works!
Thank you,
Choon Park
On Fri, Mar 2, 2012 at 12:19 PM, Bosco Rama <postgres@boscorama.com> wrote:
Show quoted text
ChoonSoo Park wrote:
Each function returns key/value pairs from hstore type.
Is there any opposite function that returns hstore type from key/valuerows?
I know hstore (text[], text[]) can construct it. Is there any other
choice?
I have a table with ID & Description columns and want to return hstore
type
(ID1=>Desc1,ID2=>Desc2,....) that matches a query condition.
From top of my head, this is the only query I can think of
SELECT hstore ((SELECT array(SELECT CAST(ID as TEXT) FROM T1 WHERE
....)),
(SELECT array(SELECT Description FROM T1 WHERE ...)))
But I don't want to run the same where condition twice.
You could try:
select hstore(array_agg(CAST(ID as TEXT)), array_agg(Description)) from
T1 where ...I don't have hstore loaded but that seems like it should work.
HTH
Bosco.