dpage
commented
13 years ago Comment migrated from Redmine: https://redmine.postgresql.org/issues/576#note-1 Originally created by Anonymous at 2011-08-12 18:23:37 UTC.
The fix is pretty obvious in this case. The code mistakenly trims all outer enclosing brackets, which leads to unmatched brackets in test 2.) - both left brackets were removed.
pgTrigger.cpp, line 393:
- wxT(" trim(substring(pg_get_triggerdef(t.oid), 'WHEN (.*) EXECUTE PROCEDURE'), '()') AS whenclause\n")
- wxT(" substring(pg_get_triggerdef(t.oid), 'WHEN (.*) EXECUTE PROCEDURE') AS whenclause\n")
Issue migrated from Redmine: https://redmine.postgresql.org/issues/576 Originally created by Anonymous at 2011-08-12 18:06:13 UTC.
Brackets around the WHEN condition are missing or unmatched in the SQL pane. (See ticket #387)
pg feature applies to postgresql 9.0+ Tested with pgAdmin 1.14.0 Beta 3 on Win XP; pg 9.0.4 on Debian Squeeze.
Docs: http://www.postgresql.org/docs/9.0/interactive/sql-createtrigger.html
-- Test case --
CREATE TABLE foo(a serial, b text, c text, d text);
CREATE OR REPLACE FUNCTION trg_foo_upaft() RETURNS trigger AS $BODY$ BEGIN
-- do something RETURN NEW;
END; $BODY$ LANGUAGE plpgsql VOLATILE;
-- Test 1.) -- I say: -- DROP TRIGGER up_aft ON foo; CREATE TRIGGER up_aft AFTER UPDATE ON foo FOR EACH ROW WHEN ((old.b, old.c, old.d) <> (new.b, new.c, new.d)) EXECUTE PROCEDURE trg_foo_upaft();
--> pgAdmin says: CREATE TRIGGER up_aft AFTER UPDATE ON foo FOR EACH ROW WHEN (old.b <> new.b) OR (old.c <> new.c) OR (old.d <> new.d) EXECUTE PROCEDURE trg_foo_upaft(); --! no enclosing brackets !
-- Test 2.) -- I say: -- DROP TRIGGER up_aft ON foo; CREATE TRIGGER up_aft AFTER UPDATE ON foo FOR EACH ROW WHEN ((old.b <> new.b) OR (old.c <> new.c) OR (old.d <> new.d)) EXECUTE PROCEDURE trg_foo_upaft();
--> pgAdmin says: CREATE TRIGGER up_aft AFTER UPDATE ON foo FOR EACH ROW WHEN (old.b <> new.b) OR (old.c <> new.c)) OR (old.d <> new.d) EXECUTE PROCEDURE trg_foo_upaft(); --! unmatched bracket and missing enclosing brackets !