CODEWITHSUNDEEP
sqldatabasepostgresql
user1591727
user1591727

Reputation: 193

postgres rule update field based on unique value from subquery

I'm able to update a field based in a subquery when I substitute id_sitio with a real value:

update sitios
set grado = (
    select count(lin_sit.id_lineamiento) as total
    from lineamientos_sitios lin_sit inner join sitios sit on lin_sit.id_sitio=sit.id_sitio
    where sit.id_sitio=1418
)
where id_sitio=1418;

But not when I implement the rule. 

CREATE or replace RULE actualizar_lineamientos_sitios_view AS ON UPDATE TO lineamientos_sitios_view DO INSTEAD (
UPDATE lineamientos_sitios SET id_lineamiento = new.id_lineamiento, id_sitio = new.id_sitio WHERE (lineamientos_sitios.id_lineamiento = old.id_lineamiento AND lineamientos_sitios.id_sitio = old.id_sitio);
update sitios  set grado = (select count(lin_sit.id_lineamiento) as total from lineamientos_sitios lin_sit inner join sitios sit on lin_sit.id_sitio=sit.id_sitio where sit.id_sitio=new.id_sitio) where id_sitio=new.id_sitio;
);

I believe that the first update is irrelevant as it works, I'm not sure how to reference the id from the record I want to upload.

Any solutions? Thanks!

Upvotes: 0

Views: 313

Answers (1)

user1591727
user1591727

Reputation: 193

Hi Craig (everybody else is welcome ;) )

Thank you very much. You put me on the clue, but having some troubles with the delete rule. The first rule works fine, so i can upload the field grado based in a subquery. But calcularGrado2_view rule not makes the work..

CREATE RULE calcularGrado_view AS ON INSERT TO lineamientos_sitios_view DO INSTEAD (

update sitios  set grado = (select count(lin_sit.id_lineamiento) as total from lineamientos_sitios lin_sit inner join sitios sit on lin_sit.id_sitio=sit.id_sitio where sit.id_sitio=new.id_sitio) where id_sitio=new.id_sitio;

);

CREATE RULE calcularGrado2_view AS ON DELETE TO lineamientos_sitios_view DO INSTEAD (

update sitios  set grado = (select count(lin_sit.id_lineamiento) as total from lineamientos_sitios lin_sit inner join sitios sit on lin_sit.id_sitio=sit.id_sitio where sit.id_sitio=old.id_sitio) where id_sitio=old.id_sitio;

);

DO you know what could be wrong with the rule? Thank you!

Upvotes: 1

Related Questions