trigger fails - Mailing list pgsql-novice
From | Roger Mason |
---|---|
Subject | trigger fails |
Date | |
Msg-id | y65sfyteme4.fsf@mun.ca Whole thread Raw |
Responses |
Re: trigger fails
Re: trigger fails |
List | pgsql-novice |
Hello, I want to trigger this function on insert to an existing table: CREATE OR REPLACE FUNCTION get_final_energy (id text) RETURNS TABLE ( jid text, "timestamp" text, machine text, scf integer, energy double precision ) AS $function$ BEGIN RETURN query WITH a AS ( SELECT -- public.results.jid AS ajid, regexp_split_to_table(public.results.totenergy_out, '\n') AS teo FROM public.results WHERE public.results.jid = id ), b AS ( SELECT public.results.jid, public.results. "timestamp" AS timestamp, public.results.machine AS machine, cast( CASE WHEN split_part(a.teo, ' ', 2) = '' THEN '0' ELSE split_part(a.teo, ' ', 2) END AS integer) AS scf, cast( CASE WHEN split_part(a.teo, ' ', 3) = '' THEN '0.0' ELSE split_part(a.teo, ' ', 3) END AS double precision) AS energy FROM public.results, a WHERE public.results.jid = id GROUP BY public.results.jid, public.results. "timestamp", public.results.machine, a.teo ), c AS ( SELECT DISTINCT ON (b.jid) b.jid AS jid, b. "timestamp" AS "timestamp", b.machine AS machine, b.scf AS scf, b.energy AS energy FROM b ORDER BY jid, scf DESC ) SELECT * FROM c RETURN; END; $function$ LANGUAGE plpgsql; There are associated trigger functions: CREATE OR REPLACE FUNCTION trigger_final_energy_table_create () RETURNS TRIGGER AS $$ BEGIN CREATE TABLE IF NOT EXISTS final_energy ( jid text, "timestamp" text, machine text, scf integer, energy double precision ); RETURN new; END; $$ LANGUAGE 'plpgsql'; CREATE OR REPLACE FUNCTION trigger_final_energy_table_insert () RETURNS TRIGGER AS $$ BEGIN INSERT INTO final_energy SELECT * FROM get_final_energy (NEW.jid); RETURN new; END; $$ LANGUAGE 'plpgsql'; With these triggers: CREATE TRIGGER atrigger_final_energy_table_create AFTER INSERT ON results FOR EACH ROW EXECUTE PROCEDURE trigger_final_energy_table_create (); CREATE TRIGGER btrigger_final_energy_table_insert AFTER INSERT ON results FOR EACH ROW EXECUTE PROCEDURE trigger_final_energy_table_insert (); All this code seems to run when I insert data into the 'results' table, the 'final_energy' table gets created with the specifiled columns but no data are inserted. I have tried various modifications of get_final_energy without success. If someone could point me to an abvious flaw or suggest how to debug this it woould be most helpful. Thanks for reding this long message, Roger
pgsql-novice by date: