Postgresql string concatenation: error: syntax error at or near "msg"

Question

I am trying to raise an issue in a Postgresql function using a concatenated message, but I get an error message at runtime:

error: syntax error at or near "msg"

The code is the following:

CREATE OR REPLACE FUNCTION insertUserAccount(
    id             bigint,
    nname          varchar(40),
    email          varchar(40),
    pwd            varchar,
    status         smallint,
    last_update  bigint,
    preferences    json,
    bits           integer,
    adm            json)
    RETURNS bigint AS $$
DECLARE
    rowc INTEGER;
    ret_id bigint;
    msg text;
BEGIN
    ...
    IF ( rowc > 0 ) THEN
        msg = 'User account already exists or name or email is unavailable (id=' || id
            || ', name=' || nname
            || ', email=' || email || ')';
        RAISE EXCEPTION msg USING ERRCODE = '23505';
    ELSE
    ...

Use `:=` for [assignment](http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-ASSIGNMENT) in PL/pgSQL. — Milen A. Radev, Mar 20 '14 at 08:39
While `:=` is good advice, it cannot explain the error. Details [here](http://stackoverflow.com/questions/7462322/the-forgotten-assignment-operator-and-the-commonplace). [Syntax options for `RAISE`](http://www.postgresql.org/docs/current/interactive/plpgsql-errors-and-messages.html) depend on your *version of Postgres*. There have been several updates in recent versions. — Erwin Brandstetter, Mar 20 '14 at 09:42

Erwin Brandstetter · Answer 1 · 2014-03-20T09:58:09.703

The actual problem is the faulty syntax for RAISE EXCEPTION. I would simplify overall:

IF rowc > 0 THEN
   RAISE EXCEPTION 'User account already exists or name or email is unavailable (id=%,name=%,email=%)'
                 , id, nname, email  USING ERRCODE = '23505';
ELSE ...

The variable msg might not be needed at all then. It is generally best to keep the number of assignments low, since those are rather expensive in PL/pgSQL (as compared to other programming languages). Not dramatic, but still ..

score 1 · Accepted Answer · answered Mar 20 '14 at 08:53

1

Use := for assignment in PL/pgSQL

answered Mar 20 '14 at 08:53

Milen A. Radev

60,241
22
105
110

I had to change RAISE EXCEPTION msg USING ERRCODE = '23505'; into RAISE EXCEPTION '%', msg USING ERRCODE = '23505'; to make it work too. – Jérôme Verstrynge Mar 20 '14 at 08:55

Postgresql string concatenation: error: syntax error at or near "msg"

2 Answers2