current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
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. It's 100% free, no registration required.
up vote 0 down vote favorite

For a database systems project which tracks entities histories, I stumbled acros the following problem.

Assume we have the table "entity"

CREATE TABLE entity (
    eID SERIAL PRIMARY KEY,
    eName VARCHAR(255) NOT NULL,
    eValue1 INTEGER,
    eValue2 INTEGER
);

as well as "entity_history"

CREATE TABLE entity_history (
    eID_FK INTEGER REFERENCES entity(eID),
    time TIMESTAMP DEFAULT current_timestamp,
    type VARCHAR(6) CHECK(type IN ('Value1','Value2')),
    val INTEGER,
    importantHistoricalEntryNotInMainTable TEXT
    UNIQUE(eID_FK, time)
);

Usually, the idea is that the history gets an entry on update/insert of the main table. I have to do it the other way around (due to the entitys history being created based on external website information which is not stored in the main table upon insert), so whenever an entry is added to the history, it should update the current value of the field in the main table. Thus I tried using

CREATE RULE update_main_table AS ON INSERT TO entity_history
DO
UPDATE entity SET ("e" || NEW.type) = NEW.val;

wwhich yields a syntax error.

share|improve this question
    
How can you possibly have an expression on the left hand side of an assignment? –  Colin 't Hart Sep 18 '14 at 8:38
    
Well I am not saying I suppose this to work. It was just my naive approach (very naive, TBH) which aims at clarifying my question, and thus I am looking for a workaround to get this done. –  Zahlii Sep 18 '14 at 8:44

1 Answer 1

up vote 1 down vote

You can't use variables for columns. So you would need to do something like this:

CREATE RULE update_main_table AS ON INSERT TO entity_history
DO
UPDATE entity 
   SET eValue1 = case 
                    when new.type = 'Value1' then NEW.val
                    else eValue1
                 end,
       eValue2 = case 
                    when new.type = 'Value2' then NEW.val
                    else eValue2
                 end;

Now I guess you didn't show us the whole picture and you have more than just two columns in the entity table (which will make the above update statement very inefficient and cumbersome).

The whole thing looks like a variation of the (anti) pattern named "entity-attribute-value". As you are using Postgres, I would highly recommend using the hstore data type to store key value pairs.

At least in the entity table you could do that:

CREATE TABLE entity (
    eID     SERIAL PRIMARY KEY,
    eName   VARCHAR(255) NOT NULL,
    value   hstore
);

Then you could just do:

CREATE RULE update_main_table AS ON INSERT TO entity_history
DO
UPDATE entity 
   SET value = value || hstore(NEW.type, NEW.val::text)
WHERE eid = new.eID_FK;

Additionally this will automatically adjust to new "value types" without the need to rewrite the statement.

Maybe you can even restructure the entity_history table to also use a hstore column.

share|improve this answer
    
Thanks for the answer. What would be your approach to cope with that anti-pattern if it comes to the schema? I just had a quick thought about either way creating a view which just simply displays the latest values of entity_history grouped by their type, or adding another table called entity_attributes which would store eID, type and the latest value which would allow for using the rules. –  Zahlii Sep 18 '14 at 9:04

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.