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

Question

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.

I would find it far easier to read if the column names made any sense to me. Therefore, English names would be nicer. — Holger Jakobs
– Holger Jakobs, Commented May 2, 2015 at 22:52

telex-wap · Accepted Answer · 2015-05-02 18:35:55Z

1

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.

answered May 2, 2015 at 18:35

telex-wap

8521 gold badge10 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related