使用Django在PostgreSQL函数中进行异常处理

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.

在Postgres写了以下商店程序。此SP只接受传入的参数，将其插入表中并返回当前标识。更多我还声明了一个附加变量，它将告诉sp运行是否成功。

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.

我是Postgres的新手，并且对Postgres的做法知之甚少。我想要一些像BEGIN TRY，END TRY和BEGIN CATCH，END CATCH这样的东西就像我们在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.

更多我必须在django视图中捕获这两个返回值。

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.

但它还没有工作......我不知道现在哪个id做错了，这里的任何django / postgres专家都会帮助我。

2 个解决方案

#1

There are several problems with your function:

您的功能有几个问题：

Statements need to be terminated with a ; - always
声明需要以; - 永远
Variable assignments are done using := (see: http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-ASSIGNMENT)
变量赋值使用：=（参见：http：//www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-ASSIGNMENT）完成
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)
您不能从函数返回多个值（除非您创建一个集返回函数，返回一个对象或使用输出参数）
Boolean values are true or false. Not 0 or 1 (those are numbers)
布尔值为true或false。不是0或1（这些是数字）
The result of an automatically generated ID value is better obtained using lastval() or `` INSERT ... RETURNING expressions INTO ...not through aSET` statement.
使用lastval（）或``INSERT ... RETURNING表达式INTO ...而不是通过aSET`语句可以更好地获得自动生成的ID值的结果。

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.

但正如我所说：你不能从一个函数返回多个值。唯一的方法是声明OUT参数，但我个人觉得它们在SQL客户端中有点难以处理。

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

您有以下选项向调用者报告错误：

let the caller handle the exception/error that might arise (which is what I prefer)
让调用者处理可能出现的异常/错误（这是我更喜欢的）
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!)
定义一个新的用户定义数据类型，其中包含message_id和success标志并返回该标志（但这意味着您将丢失错误消息！）
return a NULL for the message_id to indicate that something went wrong (but that also means you lose the error information)
为message_id返回NULL以指示出现错误（但这也意味着您丢失了错误信息）
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
使用out参数传递两个值。手册中提供了一个示例：http：//www.postgresql.org/docs/current/static/xfunc-sql.html#XFUNC-OUTPUT-PARAMETERS

#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','taqi@gmail.com','');

#1