Ticket #332 (closed bug: fixed)
SQL for TRIGGER with WHEN clause broken
| Reported by: | brsa | Owned by: | gleu |
|---|---|---|---|
| Priority: | minor | Milestone: | 1.14 |
| Component: | pgadmin | Version: | trunk |
| Keywords: | browser trigger | Cc: | |
| Platform: | all |
Description
Brackets around the WHEN condition are missing or unmatched in the SQL pane. (See ticket #98)
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 !
