CODEWITHSUNDEEP
sqlpostgresqlhstore
Dek Dekku
Dek Dekku

Reputation: 1461

Postgres: how to View an hstore Column as a Table

Simple problem but my search fu is failing somehow so I don't even know if this is possible or not.

I have a table in Postgres, call it key_value_store, with an hstore field, call it document. Also I defined a type, call it key_value_type, which has as properties the fields that I want to extract from the hstore into a full blown table row:

CREATE TYPE key_value_type AS (property1 text, property2 text, property3 text)

So I'd like to output (eventually using a VIEW) a table with as many columns as the properties of key_value_type and I need to do this for many combinations of properties, therefore I don't want to create a table for each combination.

I tried with:

SELECT populate_record(null::key_value_type, document) FROM key_value_store

but instead of:

| PROPERTY 1 | PROPERTY 2 | PROPERTY 3 |
........................................
| value 1.1  | value 1.2  | value 1.3  |
........................................
| value 2.1  | value 2.2  | value 2.3  |
........................................
| and so on  | and so on  | and so on  |
........................................

what I get is:

| populate_record                      |
........................................
| (value 1.1, value 1.2, value 1.3)    |
........................................
| (value .1, value 2.2, value 2.3)     |
........................................
| (and so on, and so on, and so on)    |
........................................

How do I get to the desired result from here (or from anywhere else for that matter)? I noticed that using a table name instead of key_value_type I actually get what I want, but as I said I'd avoid creating a table for each combination of properties I need.

Upvotes: 1

Views: 769

Answers (1)

user330315
user330315

Reputation:

populate_record() does exactly that: it populates a record.

Therefor you get only a single column, not three columns.

If you want all columns from the record, you need to explicitly say so:

SELECT (populate_record(null::key_value_type, document)).*
FROM key_value_store

SQLFiddle example: http://sqlfiddle.com/#!15/b6794/1

Upvotes: 2

Related Questions