Exception handling in PostgreSQL function with Django

Question

have write the following store procedure in Postgres. This SP simply accept the incoming parameters, insert it into the table and return current identity. More I have also declare an addition variable that will tell is the sp runs successfully or not.

I am new to Postgres and have not much knowledge about Postgres way to do this. I want some thing like BEGIN TRY, END TRY and BEGIN CATCH, END CATCH like we do in MSSQL.

CREATE OR REPLACE FUNCTION usp_save_message(msg_sub character varying(80), msg_content text, msg_type character(12), msg_category character(255),msg_created_by character(255),msg_updated_by character(255))
  RETURNS msg_id character, success boolean AS
$BODY$
DECLARE
    msg_id character;
    success boolean;
BEGIN
  BEGIN TRY:
  set success = 0
  set msg_id = 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;
   set success = 1
   RETURN msg_id,success;
END;
$BODY$
LANGUAGE plpgsql VOLATILE

I want something like this:

begin proc()
BEGIN
  BEGIN TRY:
        set success = 0
        execute the query
        set success = 1
  END TRY
  BEGIN CATCH:
   set success = 0
  END CATCH
  set success = 1
END

More I have to catched both these return values in django views.

I have updated the question and it is as now;

Here is the table,

CREATE TABLE tbl_messages
(
  message_subject character varying(80),
  message_content text,
  message_type character(12),
  message_category character(255),
  created_on timestamp without time zone,
  created_by character(255),
  updated_on timestamp without time zone,
  updated_by character(255),
  message_id serial NOT NULL,
  CONSTRAINT tbl_messages_pkey PRIMARY KEY (message_id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE tbl_messages
  OWNER TO gljsxdlvpgfvui;

Here is the function i created;

CREATE FUNCTION fn_save_message(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, OUT success boolean, OUT msg_id integer) RETURNS integer AS
$BODY$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,success;
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;

But it is not still working... i don't know what id have done wrong now, any django/postgres expert here kindly help me out.

Answer 1

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

Answer 2

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','[email protected]','');