如何在Postgresql中编写更新函数(存储过程)?

前端之家收集整理的这篇文章主要介绍了如何在Postgresql中编写更新函数(存储过程)?前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
我想在 postgresql中创建一个更新函数(存储过程),我在google上搜索了很多次,但没有找到更新函数(存储过程)的正确示例.如何在 Postgresql中编写更新函数并更改表中的现有数据?

提前致谢.

  1. Example of Function
  2.  
  3. CREATE OR REPLACE FUNCTION updateuser_login(userloginidp integer,usercategoryidf integer,usertypeidf integer,usertypereferenceidf integer,loginname text,loginpassword text,menutypeidf integer,username text,dashboardconfig text,careprovideridf integer,isactive boolean)
  4. RETURNS void AS
  5. $BODY$BEGIN
  6. UPDATE tbuserlogin
  7. SET usercategoryidf="@usercategoryidf",usetypeidf="@usertypeidf",usertypereferenceidf="@usertypereferenceidf",loginname="@loginname",loginpassword="@loginpassword",menutypeidf="@menutypeidf",username="@username",dashboardconfig="@dashboardconfig",careprovideridf="@careprovideridf",isactive="@isactive"
  8. WHERE userloginidp = "@userloginidp";
  9. END$BODY$
  10. LANGUAGE plpgsql VOLATILE
  11. COST 100;
  12. ALTER FUNCTION updateuser_login(integer,integer,text,boolean)
  13. OWNER TO postgres;
您可以在PGXN网站的源代码中找到这类内容的优秀示例:

https://github.com/pgxn/pgxn-manager/tree/master/sql

用户sql文件的示例:

  1. CREATE OR REPLACE FUNCTION update_user(
  2. nickname LABEL,full_name TEXT DEFAULT NULL,email EMAIL DEFAULT NULL,uri URI DEFAULT NULL,twitter CITEXT DEFAULT NULL
  3. ) RETURNS BOOLEAN LANGUAGE plpgsql SECURITY DEFINER AS $$
  4. /*
  5.  
  6. % SELECT update_user(
  7. nickname := 'theory',full_name := 'David E. Wheeler',email := 'justatheory@pgxn.org',uri := 'http://www.justatheory.com/',twitter :- 'theory'
  8. );
  9. update_user
  10. ─────────────
  11. t
  12.  
  13. Update the specified user. The user must be active. The nickname cannot be
  14. changed. The password can only be changed via `change_password()` or
  15. `reset_password()`. Pass other attributes as:
  16.  
  17. full_name
  18. : The full name of the user.
  19.  
  20. email
  21. : The email address of the user. Must be a valid email address as verified by
  22. [Email::Valid](http://search.cpan.org/perldoc?Email::Valid).
  23.  
  24. uri
  25. : Optional URI for the user. Should be a valid URI as verified by
  26. [Data::Validate::URI](http://search.cpan.org/perldoc?Data::Validate::URI).
  27.  
  28. twitter
  29. : Optional Twitter username. A leading "@" wil be removed.
  30.  
  31. Returns true if the user was updated,and false if not.
  32.  
  33. */
  34. BEGIN
  35. UPDATE users
  36. SET full_name = COALESCE(update_user.full_name,users.full_name),email = COALESCE(update_user.email,users.email),uri = COALESCE(update_user.uri,users.uri),twitter = COALESCE(trim(leading '@' FROM update_user.twitter),users.twitter),updated_at = NOW()
  37. WHERE users.nickname = update_user.nickname
  38. AND users.status = 'active';
  39. RETURN FOUND;
  40. END;
  41. $$;

猜你在找的Postgre SQL相关文章