技术点滴

postgresql 存储过程实践

以版本postgresql只支持函数create function()。从11版本,开发真正支持存储过程,create procedure().

procdeure相对function 最大的好处是支持了commit,rollback。这样可利用事务保证数据的一致性。

但pg的prodeure相对其它数据库的存储过程有所不同,需注意已下问题:

1) 不能返回结果集,不像有些数据库中在结尾时加一个select ,甚至多个select 返回多条结果集。只能通过传出参数返回。

2) 传出参数必须是inout,不能直接用out,可传入一个默认值,在过程处理中不用即可。传出参数返回时,是一条结果集。

3) 存储过程中,用的sql 语句,需要把值赋与中间变量时,采用into的方法:如:    

    insert into "JsonTests" ("JsonName","JsonTestData"values(openId,content) RETURNING "JsonTestId" into user_id;

4) 语句出错时,要用 exception来捕获,如要把异常再抛出,要用raise.

完整示例如下:

create or replace procedure sp_add_user(

  openId varchar(64),

  nickName varchar(64),

  avatarUrl varchar(64),

  content JSONB,

  user_id inout int

)

language plpgsql

as $$

  declare

    v_isOK boolean;

  begin

    v_isOK := true;

    insert into "JsonTests" ("JsonName","JsonTestData"values(openId,content) RETURNING "JsonTestId" into user_id;

    exception when others then

      v_isOK := false;

      rollback;

      raise exception '(%)', SQLERRM;

    if (v_isOK) then

      commit;

    end if;

    return;

  end;

$$;


-----

call sp_add_user('test','','','{"name":"test"}',0);

----

user_id

integer

  7

电话咨询
—— 售后咨询 ——
—— 商务合作 ——
微信咨询

售后咨询

商务咨询