PostgreSQL trigger syntax error

Question

I wonder how to use trigger in PostgreSQL with RAISE SQLSTATE dynamic instruction?

IF i>0 THEN
    RAISE SQLSTATE '23505' USING MESSAGE = 'la planète est déjà occupée (planet_non_free)=(%, %, %)', NEW.galaxie, NEW.systeme_solaire, NEW.position;
END IF;

It doesn't work.

Ok, I just found the solution...just surrounded MESSAGE value by parenthesis like this : MESSAGE = ('la planète..., NEW.position); — Olivier J.
– Olivier J., Commented Dec 25, 2012 at 19:58
What PostgreSQL version is this supposed to be? Also, "It doesn't work" is not a PostgreSQL error message known to me. It should go without saying to include the actual error message. If your system runs with a french locale, just run SET lc_messages='C'; to switch to English for the current session. — Erwin Brandstetter
– Erwin Brandstetter, Commented Dec 26, 2012 at 4:03

Erwin Brandstetter · Accepted Answer · 2012-12-26 03:59:21Z

1

I suggest to use:

IF i > 0 THEN
    RAISE SQLSTATE '23505'
    USING MESSAGE = format('la planète est déjà occupée (planet_non_free)=(%s, %s, %s)', NEW.galaxie, NEW.systeme_solaire, NEW.position);
END IF;

Then your error message reads:

la planète est déjà occupée (planet_non_free)=(<g>, <s>, <p>)

.. instead of:

("la planète est déjà occupée (planet_non_free)=(%, %, %)",<g>, <s>, <p>)

answered Dec 26, 2012 at 3:59

Erwin Brandstetter

667k159 gold badges1.2k silver badges1.3k bronze badges

Add a comment |

Olivier J. · Accepted Answer · 2012-12-26 09:14:11Z

Ok, I've found the solution. I have only to surround MESSAGE value with parenthesis :

So :

IF i>0 THEN
   RAISE SQLSTATE '23505' USING MESSAGE = 'la planète est déjà occupée (planet_non_free)=(%, %, %)', NEW.galaxie, NEW.systeme_solaire, NEW.position;
END IF;

becomes :

IF i>0 THEN
   RAISE SQLSTATE '23505' USING MESSAGE = ('la planète est déjà occupée (planet_non_free)=(%, %, %)', NEW.galaxie, NEW.systeme_solaire, NEW.position);
END IF;

Simple but I did not saw explicit example with dynamic MESSAGE on the web.

Hope this helps

EDIT :

Ok sorry, the right syntax is this one :

IF i>0 THEN
    RAISE SQLSTATE '23505' USING MESSAGE = 'la planète est déjà occupée (planet_non_free)=(' || NEW.galaxie || ',' || NEW.systeme_solaire || ',' || NEW.position || ')';
END IF;

It seems that we can not use % with USING MESSAGE statement.

Collectives™ on Stack Overflow

PostgreSQL trigger syntax error

2 Answers 2

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Related