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
-- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
