Converting an hstore into a key/value record

From: Jon Smark <jon(dot)smark(at)yahoo(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Converting an hstore into a key/value record
Date: 2011-06-04 11:12:04
Message-ID: 486393.91832.qm@web112814.mail.gq1.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hallo,

Suppose I have the following 'scores_t' record:

create type scores_t as (item text, score int);

What's the cleanest way of converting a hstore into a set of scores_t,
with each key mapping to field 'item', and the value to field 'score'?

As an example, consider the following (non-working) code:

create table people (id int4 unique not null, scores hstore);

create function get_scores (int4)
returns setof scores_t
language sql stable as
$$
select each (scores) from people where id = $1;
$$

Sure, I could cook up some PL/pgSQL to do this task, but I suspect
there must be some much simpler SQL statement that does the same...

Thanks in advance!
Jon

Browse pgsql-general by date

  From Date Subject
Next Message Nick Raj 2011-06-04 14:04:25 Pl/pgsql function
Previous Message Esmin Gracic 2011-06-04 10:18:35 Re: Need suggestion