Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Let's assume we have a table where it has two columns, one an ID and one an hstore column, which stores key/value pairs.

You could query that column as such:

SELECT name, attributes->'device' as device
FROM products
WHERE attributes->'edition'= 'ebook'

Can you create a view for this table where the keys/values appear as columns in the view instead of all in the hstore column?

Example: I have an hstore key where it might have the key of amenity. I want to create a view showing the the ID and the amenity values in a new view.

share|improve this question

Sure you can.

CREATE VIEW v_ebooks AS
SELECT products_id, attributes->'device' AS device
FROM   products
WHERE  attributes->'edition'= 'ebook';

SQL Fiddle demo.

But only for a static set of columns. Dynamic output of all possible attributes in an hstore column conflicts with the requirement that the return type has to be declared at creation time.

This later answer provides a completely dynamic solution (in two steps):

share|improve this answer
    
Thanks Erwin. So does that mean if all keys are not present in all hstore columns, then an issue may arise? – josh1234 Feb 10 '14 at 11:33
    
@josh1234: Columns will just be NULL for rows where the key is not found. – Erwin Brandstetter Feb 10 '14 at 15:04
    
@josh1234: So do you have your answer? – Erwin Brandstetter Dec 6 '15 at 7:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.