trigger fails
От | Roger Mason |
---|---|
Тема | trigger fails |
Дата | |
Msg-id | y65sfyteme4.fsf@mun.ca обсуждение исходный текст |
Ответы |
Re: trigger fails
("David G. Johnston" <david.g.johnston@gmail.com>)
Re: trigger fails ("David G. Johnston" <david.g.johnston@gmail.com>) |
Список | 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 по дате отправления: