[Answer]-Exception handling in PostgreSQL function with Django

There are several problems with your function:

1. Statements need to be terminated with a ; – always
2. Variable assignments are done using := (see: http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-ASSIGNMENT)
3. You can’t return more than one value from a function (unless you create a set returning function, return an object or use out parameters)
4. Boolean values are true or false. Not 0 or 1 (those are numbers)
5. The result of an automatically generated ID value is better obtained using lastval() or “ INSERT … RETURNING expressions INTO …not through aSET` statement.

Exception handling is done using the exception clause as documented in the manual

So you need something like this:

DECLARE
....

BEGIN
  BEGIN
    INSERT INTO tbl_messages
        (message_subject, message_content, message_type, message_category, 
       created_on, created_by, updated_on, updated_by)
    VALUES 
      (msg_sub, msg_cont, msg_type,msg_category, LOCALTIMESTAMP, 
       msg_created_by, LOCALTIMESTAMP, msg_updated_by)
    returning message_id
    into msg_id;

    success := true;
  EXCEPTION 
    WHEN others then 
      success := false;
      msg_id := null;
  END;
  return msg_id;
END;

But as I said: you can’t return more than one value from a function. The only way to do this is to declare OUT parameters, but personally I find them a bit hard to handle in SQL clients.

You have the following options to report an error to the caller:

1. let the caller handle the exception/error that might arise (which is what I prefer)
2. define a new user defined data type that contains the message_id and the success flag and return that (but that means you lose the error message!)
3. return a NULL for the message_id to indicate that something went wrong (but that also means you lose the error information)
4. Use out parameters to pass both values. An example is available in the manual: http://www.postgresql.org/docs/current/static/xfunc-sql.html#XFUNC-OUTPUT-PARAMETERS

CREATE FUNCTION fn_save_message3(IN msg_sub character varying, IN msg_cont text, IN msg_type character varying, IN msg_category character varying, IN msg_created_by character varying, IN msg_updated_by character varying) RETURNS integer AS
$BODY$ DECLARE msg_id integer := 0;
BEGIN
    INSERT INTO tbl_messages
        (message_subject, message_content, message_type, message_category, 
       created_on, created_by, updated_on, updated_by)
    VALUES 
      (msg_sub, msg_cont, msg_type, msg_category, LOCALTIMESTAMP, 
       msg_created_by, LOCALTIMESTAMP, msg_updated_by);
      Select into msg_id currval('tbl_messages_message_id_seq');
  return msg_id;
END;$BODY$
LANGUAGE plpgsql VOLATILE NOT LEAKPROOF
COST 100;
ALTER FUNCTION public.fn_save_message(IN character varying, IN text, IN character varying, IN character varying, IN character varying, IN character varying)
  OWNER TO gljsxdlvpgfvui;



SELECT fn_save_message3('Test','fjaksdjflksadjflas','email','news','taqi@gmail.com','');