用PL/pgSQL写postgreSQL的存储过程

前端之家收集整理的这篇文章主要介绍了用PL/pgSQL写postgreSQL的存储过程前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

今天学会了用 PL/pgsql 写 postgresql 的存储过程,网上资料实在少得可怜,唯一能搜到的一些还是抄来抄去的;还是翻postgresql的文档吧,把今天解决的问题说一下吧,希望对其他人有帮助。
问题是这样的,有一张message表:
CREATE TABLE message
(
id int8 NOT NULL,
receiveuserid int8,
senduserid int8,
receivedelete bool DEFAULT false,
senddelete bool DEFAULT false,
……
CONSTRAINT usermessage_pkey PRIMARY KEY (id)
)
略去其他字段,senduserid是发信息的用户id,senddelete如为true则表示这条消息被发信人所删除;至于receive我就不用说了。一条信息只有被发信人和收信人都删除,才能真正从表里删除(这是显然的,否则收信人删了一条消息后,发信人的“发件箱”里就会找不到这条消息)。
所以删除消息(可能是多条消息)的时候要进行各种判断(是否是发信人?是否是收信人?是否真正删除?),用单条sql语句完成这个工作显然有些困难(当然用循环嵌套select不考虑效率的话也是可以实现的),只好写存储过程了。
顺便介绍常用的PL/pgsql结构和语法吧:

  • 结构
  • PL/pgsql是一种块结构的语言,比较方便的是用pgAdmin III新建Function,填入一些参数就可以了。基本上是这样的:

    CREATE OR REPLACE FUNCTION 函数名(参数1,[整型 int4,整型数组 _int4,...])
    RETURNS 返回值类型 AS
    $BODY$
    DECLARE
    变量声明
    BEGIN
    函数
    END;
    $BODY$
    LANGUAGE ‘plpgsql’ VOLATILE;

  • 变量类型
  • 除了postgresql内置的变量类型外,常用的还有 RECORD ,表示一条记录。

  • 赋值
  • 赋值和Pascal有点像:“变量 := 表达式;”
    有些奇怪的是连接字符串的是“||”,比如 sql := ‘SELECT * FROM’ || table || ‘WHERE …’;

  • 判断
  • 判断又和VB有些像:
    IF 条件 THEN

    ELSEIF 条件 THEN

    ELSE

    END IF;

  • 循环
  • 循环有好几种写法:
    WHILE expression LOOP
    statements
    END LOOP;
    还有常用的一种是:(从1循环到9可以写成FOR i IN 1..9 LOOP)
    FOR name IN [ REVERSE ] expression .. expression LOOP
    statements
    END LOOP;

  • 其他
  • 还有几个常用的函数
    SELECT INTO record …; 表示将select的结果赋给record变量(RECORD类型)
    PERFORM query; 表示执行query并丢弃结果
    EXECUTE sql; 表示执行sql语句,这条可以动态执行sql语句(特别是由参数传入构造sql语句的时候特别有用)

最后,贴出解决上面这个问题的存储过程吧:

  1. CREATEORREPLACEFUNCTIONmessage_deletes(ids"varchar",useridint8)
  2. RETURNSint4AS
  3. $BODY$
  4. DECLARE
  5. rRECORD;
  6. delbool;
  7. numint4:=0;
  8. sql"varchar";
  9. BEGIN
  10. sql:='select id,receiveuserid,senduserid,senddelete,receivedelete from message where id in ('||ids||')';
  11. FORrINEXECUTEsqlLOOP
  12. del:=false;
  13. IFr.receiveuserid=useridandr.senduserid=useridTHEN
  14. del:=true;
  15. ELSEIFr.receiveuserid=useridTHEN
  16. IFr.senddelete=falseTHEN
  17. updatemessagesetreceivedelete=truewhereid=r.id;
  18. ELSE
  19. del:=true;
  20. ENDIF;
  21. ELSEIFr.senduserid=useridTHEN
  22. IFr.receivedelete=falseTHEN
  23. updatemessagesetsenddelete=truewhereid=r.id;
  24. ELSE
  25. del:=true;
  26. ENDIF;
  27. ENDIF;
  28. IFdelTHEN
  29. deletefrommessagewhereid=r.id;
  30. num:=num+1;
  31. ENDIF;
  32. ENDLOOP;
  33. returnnum;
  34. END;
  35. $BODY$
  36. LANGUAGE'plpgsql'VOLATILE;

测试使用:

原文链接:https://www.f2er.com/postgresql/196964.html

猜你在找的Postgre SQL相关文章