| 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: | Whole Thread | Raw Message | 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
| 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 |