From: | Red Light <skydelta98(at)yahoo(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | recursive inner trigger call |
Date: | 2011-12-01 18:57:47 |
Message-ID: | 1322765867.82920.YahooMailNeo@web112909.mail.gq1.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi guys,
i got the following problematic : i got a table called bv that have some 'entry data' and i have another column that need to be calculated and put back in the table:
here is my table:
CREATE TABLE public.bv
(
id_bv integer NOT NULL,
c_vmax_actuel real,
d_capacite_barrages_new real,
CONSTRAINT "BV_pkey" PRIMARY KEY (id_bv)
)
WITH (
OIDS=FALSE
);
ALTER TABLE public.bv OWNER TO postgres;
i created a trigger that do the necessary computation:
CREATE OR REPLACE FUNCTION public.store_bv() RETURNS TRIGGER AS $store_bv$
DECLARE
v_vmax_actuel numeric(15,2);
BEGIN
IF (TG_OP = 'UPDATE') OR (TG_OP = 'INSERT') THEN
update ed_explore."bv" set
c_vmax_actuel = ((d_capacite_barrages_new) / (30*86400)) ;
END IF;
RETURN NEW;
END;
$store_bv$ LANGUAGE plpgsql;
the declaration of my trigger :
CREATE TRIGGER store_bv_trigger
after INSERT OR UPDATE ON ed_explore.bv
FOR EACH ROW EXECUTE PROCEDURE public.store_bv();
and now i start to insert my data:
insert into public.bv (id_bv,d_capacite_barrages_new) values (1,7324591);commit;
then the trigger got executed and goes in an infinite loop,here is the error that i got :
ERREUR: dépassement de limite (en profondeur) de la pile
HINT: Augmenter le paramètre « max_stack_depth » après vous être assuré que la
limite de profondeur de la pile de la plateforme est adéquate.
CONTEXT: instruction SQL « update ed_explore."bv" set c_vmax_actuel = ((d_capacite_barrages_new) / (30*86400)) »
And thanks for you help
From | Date | Subject | |
---|---|---|---|
Next Message | Gauthier, Dave | 2011-12-01 19:09:07 | Re: recursive inner trigger call |
Previous Message | Merlin Moncure | 2011-12-01 17:32:02 | Re: Limiting number of connections to PostgreSQL per IP (not per DB/user)? |