Re: After each row trigger NOT seeing data changes?
От | Rodrigo Gonzalez |
---|---|
Тема | Re: After each row trigger NOT seeing data changes? |
Дата | |
Msg-id | 4A15BA3B.8030309@estrads.com.ar обсуждение исходный текст |
Ответ на | After each row trigger NOT seeing data changes? (Karl Nack <karlnack@futurityinc.com>) |
Ответы |
Re: After each row trigger NOT seeing data changes?
(Alban Hertroys <dalroi@solfertje.student.utwente.nl>)
|
Список | pgsql-general |
you have to change RETURN NULL; with RETURN NEW; On 05/21/2009 04:57 PM, Karl Nack wrote: > I'm attempting to create an inventory of trees. Here's a simplified, > sample table: > > CREATE TABLE tree ( > tree_id SERIAL PRIMARY KEY, > tree_species_id INT NOT NULL REFERENCES tree_species, > tree_location POINT NOT NULL, > tree_install_date DATE NOT NULL, > tree_removal_date DATE, > CHECK (tree_removal_date > tree_install_date) > ); > > > I need to ensure that no two trees are located in the same place at the > same time: > > CREATE OR REPLACE FUNCTION check_unique_tree() > RETURNS trigger > AS $$ > DECLARE > num_trees INT; > BEGIN > -- just to see what's going on > SELECT COUNT(tree_id) INTO num_trees FROM tree; > RAISE NOTICE '% % of new tree %, there are % trees.', > TG_WHEN, TG_OP, NEW, num_trees; > > PERFORM tree_id > FROM tree > WHERE > -- first condition prevents updated tree from matching with itself > NEW.tree_id <> tree_id > AND NEW.tree_location ~= tree_location > AND NEW.tree_install_date < > COALESCE(tree_removal_date, timestamp 'infinity') > AND COALESCE(NEW.tree_removal_date, timestamp 'infinity') > > tree_install_date; > > IF FOUND THEN > RAISE EXCEPTION 'Conflicting trees'; > END IF; > > RETURN NULL; > END; > $$ LANGUAGE plpgsql STABLE; > > > CREATE TRIGGER check_unique_tree > AFTER INSERT OR UPDATE ON tree > FOR EACH ROW EXECUTE PROCEDURE check_unique_tree(); > > > And yet, I'm able to do this: > > => INSERT INTO tree (tree_species_id, tree_location, tree_install_date) > -> VALUES > -> (1, '(1,1)', 'today'), > -> (1, '(1,1)', 'today'); > NOTICE: AFTER INSERT of new tree (20,1,"(1,1)",2009-05-21,), there are 0 > trees. > NOTICE: AFTER INSERT of new tree (21,1,"(1,1)",2009-05-21,), there are 0 > trees. > INSERT 0 2 > > > As a sanity check (on a fresh, truncated table): > > => INSERT INTO tree (tree_species_id, tree_location, tree_install_date) > -> VALUES (1, '(1,1)', 'today'); > NOTICE: AFTER INSERT of new tree (22,1,"(1,1)",2009-05-21,), there are 0 > trees. > INSERT 0 1 > > => INSERT INTO tree (tree_species_id, tree_location, tree_install_date) > -> VALUES (1, '(1,1)', 'today'); > NOTICE: AFTER INSERT of new tree (23,1,"(1,1)",2009-05-21,), there are 1 > trees. > ERROR: Conflicting trees > > > I notice the row count does not reflect the newly-inserted row, which > suggests that the trigger is not seeing changes made to the table. This > seems to be exactly opposite of what's in the manual: > http://www.postgresql.org/docs/8.3/interactive/trigger-datachanges.html > http://www.postgresql.org/docs/8.3/interactive/trigger-example.html > > Am I doing something wrong here? Have I misunderstood the manual? Have I > found a bug? Any help is greatly appreciated, as this check is pretty > key to what I'm trying to do. > > Thanks. > > Karl Nack > > Futurity, Inc. > 773-506-2007 >
В списке pgsql-general по дате отправления:
Следующее
От: Raymond O'DonnellДата:
Сообщение: Re: After each row trigger NOT seeing data changes?