CODEWITHSUNDEEP
postgresql
telex-wap
telex-wap

Reputation: 852

Toggle boolean value inside a non updatable view in PostgreSQL (using a rule)

I have created a view in PostgreSQL (9.4), which pulls information from several tables, so as far as I understand, is not updatable unless I use triggers or rules.

For instance, this is the view definition (the tables and everything are located inside a scheme called pra1: 

CREATE VIEW pra1.vacunaciones AS 
    SELECT pra1.responsable.poliza, 
        pra1.recibe.niño, 
        pra1.niño.nombre, 
        pra1.vacunas.nombre_vacuna, 
        pra1.recibe.fecha, 
        pra1.recibe.fiebre 
    FROM pra1.responsable, pra1.recibe, pra1.niño, pra1.vacunas 
    WHERE pra1.recibe.vacuna = pra1.vacunas.id 
        AND pra1.niño.id = pra1.recibe.niño 
        AND pra1.responsable.dni = pra1.niño.responsable;

So if I do 

SELECT * FROM pra1.vacunaciones

Everything works fine and I get the information I want to get.

My question: The field coming from pra1.recibe.fiebre is a boolean value. I would like to set a rule, so whenever I do an UPDATEstatement specifying the pra1.recibe.niño value (which is a bigint), then the pra1.recibe.fiebre field corresponding to pra1.recibe.niño toggles from true to false or viceversa.

I got as far as this:

RULE DEFINITION: 

CREATE or REPLACE RULE vac_boolean AS
ON UPDATE TO pra1.vacunaciones
DO INSTEAD
UPDATE pra1.recibe SET fiebre (no idea how to follow)
WHERE pra1.recibe.niño = 
    //I don't know how to specify that it needs to act 
    on the pra1.recibe.niño value that I pass to the UPDATE.

So after the rule is created I would like to do a short UPDATE statement specifying WHERE pra1.recibe.niño = XXX, and the rule should act on the view and update the pra1.recibe.fiebre toggling its boolean value.

Is this idea easy to accomplish? I am a newcomer to PostgreSQL, and so far I am pleased with it, but there are still many confusing areas I need to understand well.

Upvotes: 0

Views: 52

Answers (1)

telex-wap
telex-wap

Reputation: 852

Maybe someone comes with a better suggestion, but I just realized what I needed:

The rule: 

CREATE or REPLACE RULE vacunaciones_fiebre AS  
    ON UPDATE TO pra1.vacunaciones
    DO INSTEAD 
    UPDATE pra1.recibe SET fiebre = NOT fiebre WHERE niño = NEW.niño;

The update (for instance, to update the status of niño = 4: 

    UPDATE pra1.vacunaciones SET fiebre = NOT fiebre WHERE niño='4';

Like I said, maybe there is a better way, but this does it.

Upvotes: 1

Related Questions