Google+ Followers

donderdag 12 februari 2015

Why I like PostgreSQL more than MySQL: DELIMITER ;

MySQL supports triggers, and that's good.

One common use for triggers is to copy data from a table to an audit table to track changes made to the table. Something like:

CREATE TRIGGER `trg_member_audit` AFTER UPDATE ON `member` FOR EACH ROW BEGIN
INSERT INTO member_audit
(sql_action, id, created, name, email)
VALUES
('UPDATE', NEW.id, NOW(), NEW.name, NEW.email);
END;


Great! Except, that doesn't work: syntax error at or near '' at line 6.

What that means is: "There is something missing on that line".

The missing bit is the END statement, but that's not  missing, it's right there on the next line. Yes, but the semicolon that ends the INSERT also ends the CREATE statement. MySQL can see the CREATE and the BEGIN, but it is not smart enough to look for the END.

So how can you solve this problem? Well it's simple really; you just have to change the default query delimiter from a semicolon to something else that doesn't appear anywhere in your query. A double dollar sign for example. (sound familiar?) Then you replace the semicolon that ends the CREATE with the new delimiter, and then reset the delimiter back to a semicolon:






DELIMITER $$
CREATE TRIGGER `trg_member_audit` AFTER UPDATE ON `member` FOR EACH ROW BEGIN
INSERT INTO member_audit
(sql_action, id, created, name, email)
VALUES
('UPDATE', NEW.id, NOW(), NEW.name, NEW.email);
END$$
DELIMITER ;

Note that the DELIMITER statement does not end with a delimiter and that the delimiter is not quoted, that would be crazy...

How does PostgreSQL do this?
In PostgreSQL a trigger always executes a stored function and the CREATE FUNCTION statement has solved the problem by simply forcing you to put a delimiter at the beginning and at the end of the SQL that is part of the function:

CREATE FUNCTION myfunc RETURNS trigger AS
$$
BEGIN
  RAISE EXCEPTION 'This triggerfunction is empty' ;
  RETURN NEW;
END;
$$
LANGUAGE plpgsql;

The delimiter is usually just two dollar signs, but if your code itself contains a double dolllar sign you can add text between the dollarsigns, the interpreter will read the first delimiter and keep processing until it find the exact same delimiter again.

CREATE FUNCTION myfunc RETURNS trigger AS
$my-code$
BEGIN
  RAISE EXCEPTION 'This trigger has no $$ sign!' ;
  RETURN NEW;
END;
$my-code$
LANGUAGE plpgsql;