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
    ...

While := is good advice, it cannot explain the error. Details here. Syntax options for RAISE depend on your version of Postgres. There have been several updates in recent versions. — Erwin Brandstetter
– Erwin Brandstetter, Commented Mar 20, 2014 at 9:42

Erwin Brandstetter · Accepted Answer · 2014-03-20 09:58:09Z

3

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 ..

edited Mar 20, 2014 at 9:58

answered Mar 20, 2014 at 9:51

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Milen A. Radev · Accepted Answer · 2014-03-20 08:53:14Z

1

Use := for assignment in PL/pgSQL

answered Mar 20, 2014 at 8:53

Milen A. Radev

63.2k22 gold badges111 silver badges112 bronze badges

1 Comment

Jérôme Verstrynge Over a year ago

I had to change RAISE EXCEPTION msg USING ERRCODE = '23505'; into RAISE EXCEPTION '%', msg USING ERRCODE = '23505'; to make it work too.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related