In my trigger procedures I use RAISE EXCEPTION for messages. I have no problem with simple messages, but if I want to give the user some more complex feedback, I face a problem: the concatenation operator doesn't work within RAISE EXCEPTION statement.
First, I tried this:
CREATE OR REPLACE FUNCTION hlidej_datum_kon() RETURNS trigger AS $$
DECLARE
od date;
BEGIN
SELECT a.datum_od FROM akce AS a WHERE a.kod_akce = (
SELECT b.kod_akce FROM sj AS b WHERE b.kod_sj = NEW.kod_sj
) INTO od;
IF NEW.datum < od THEN
RAISE EXCEPTION 'Kontext nemohl být odkryt před začátkem akce ('||TO_CHAR(od)||')!'
ROLLBACK;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
Didn't work. So I tried to put the whole text to a text variable, but I didn't find how to put the variable's contents to the exception statement so that it would be printed as a message.
My question is: how to print a message containing variables in a PostgreSQL trigger function?
Just for sake of completeness, here is my trigger:
CREATE TRIGGER hlidej_datum_kon
AFTER INSERT OR UPDATE ON kontext
FOR EACH ROW
EXECUTE PROCEDURE hlidej_datum_kon();
END;
There are two bugs
first parameter of RAISE statement is format string - this string should be constant. It can contains a substitution symbols '%' and values for these symbols are places as others parameters of RAISE statement.
There should not be used ROLLBACK statement. RAISE EXCEPTION throws exceptions and ROLLBACK statement is newer executed. You cannot control transaction explicitly in PL/pgSQL - so you cannot use ROLLBACK or COMMIT statement in plpgsql ever.You can use a exception trapping
BEGIN
RAISE EXCEPTION 'blabla';
EXCEPTION WHEN some_exception_identif_see_list_of_exception_in_doc THEN
.. do some or do nothing
END;