Create Trigger Postgresql

Question

I am doing a simple exercise that given a Mission view i have to recalculate the data on new insertions to do this i need to make a trigger this is what i have done at the moment:

CREATE VIEW Missioni AS
SELECT d.Codice, SUM(v.Chilometri) AS KmTotali, SUM(v.Chilometri) * a.CostoKm AS CostoTotale
FROM Dipendente d join Viaggio v on v.Dipendente = d.Codice join Auto a on a.Targa = v.Auto
GROUP BY d.Codice, a.CostoKm;

CREATE OR REPLACE FUNCTION CALCULATE_MISSION()
RETURNS trigger AS $CALCULATE_MISSION$
BEGIN
    DELETE FROM Missioni;
    INSERT INTO Missioni SELECT Dipendente, SUM(Chilometri), SUM(Chilometri * CostoKm) FROM Viaggio v JOIN AUTO a ON a.targa = v.auto GROUP BY Dipendente;
END;
$CALCULATE_MISSION$ LANGUAGE plpgsql;

CREATE TRIGGER CalcolaVistaOneShot AFTER INSERT ON Viaggio
FOR EACH STATEMENT
EXECUTE FUNCTION CALCULATE_MISSION();

At the moment I am doing this on pgAdmin 4 in the query editor and it is giving me the following error:

ERROR: it is not possible to delete from the "missions" view DETAIL: Views containing GROUP BY are not auto-updatable. HINT: To allow deletions from the view, either an INSTEAD OF DELETE trigger or an ON DELETE DO INSTEAD rule without conditions must be provided. CONTEXT: SQL statement "DELETE FROM Missions" PL / pgSQL calculate_mission () function line 3 to SQL statement

Missing a begin and this where clause is wrong: WHERE Viaggio v JOIN Auto a ON a.Targa = v.Auto — Jeremy
– Jeremy, Commented May 14, 2021 at 14:02
You need a DECLARE section, there your variables ( NewKmTotali and NewCostoTotale) are declared. — kofemann
– kofemann, Commented May 14, 2021 at 14:07
@Jeremy Klaver thank you, I have a new problem could you help me? I am a beginner in postgresql — DRE
– DRE, Commented May 14, 2021 at 14:52

halfer · Accepted Answer · 2022-07-09 10:52:58Z

Regardless of which type you want, views cannot be directly updated. With standard views there is no dml applied - they are defined by the query and that is run on each time the view is referenced. In this case there would be no triggering at all as there is nothing to do. A materialized view is refreshed (see documentation previously referenced) with the REFRESH MATERIALIZED VIEW ddl statement. In this case your trigger consists of a single execute statement and return null.

create or replace function calculate_mission()
  returns trigger 
 language plpgsql 
as $$
begin 
    execute 'refresh materialized view missioni'; 
    return null; 
end; $$;

A couple of notes: It seems you are attempting to maintain a live "as of now" value for KmTotali, and CostoTotale. If the result set is small a standard view will likely perform sufficiently. If the result set is large the a materialized view likely performs better on select, but DML operations of the tables involved must absorb the entire refresh time; especially problematic when dml activity is heavy on the underlying tables.

Additionally, you are refreshing only on insert, what happens if I update the values in the calculation of when rows are delete or updated. I have put together a small fictitious demo.

Finally, spend some time and familiarize yourself with the Postgres Documentation. It is quite complete, although sometimes it can be difficult to navigate to exactly what you need.

Collectives™ on Stack Overflow

Create Trigger Postgresql

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