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

概述我想在 postgresql中创建一个更新函数(存储过程),我在google上搜索了很多次,但没有找到更新函数(存储过程)的正确示例.如何在 Postgresql中编写更新函数并更改表中的现有数据？ 提前致谢. Example of FunctionCREATE OR REPLACE FUNCTION updateuser_login(userloginidp integer, usercat 我想在 postgresql中创建一个更新函数(存储过程),我在Google上搜索了很多次,但没有找到更新函数(存储过程)的正确示例.如何在 Postgresql中编写更新函数并更改表中的现有数据？

提前致谢.

Example of FunctionCREATE 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)  RETURNS voID AS$BODY$BEGIN    UPDATE  tbuserlogin    SET usercategoryIDf="@usercategoryIDf",usetypeIDf="@usertypeIDf",usertypereferenceIDf="@usertypereferenceIDf",loginname="@loginname",loginpassword="@loginpassword",menutypeIDf="@menutypeIDf",username="@username",dashboardconfig="@dashboardconfig",careprovIDerIDf="@careprovIDerIDf",isactive="@isactive"    WHERE   userloginIDp = "@userloginIDp";END$BODY$  LANGUAGE plpgsql VolATILE  COST 100;ALTER FUNCTION updateuser_login(integer,integer,text,boolean)  OWNER TO postgres;

您可以在PGXN网站的源代码中找到这类内容的优秀示例：

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

用户sql文件的示例：

CREATE OR REPLACE FUNCTION update_user(    nickname   LABEL,full_name  TEXT   DEFAulT NulL,email      EMAIL  DEFAulT NulL,uri        URI    DEFAulT NulL,twitter    CITEXT DEFAulT NulL) RETURNS BOolEAN LANGUAGE plpgsql Security defineR AS $$/*    % SELECT update_user(        nickname  := 'theory',full_name := 'DavID E. Wheeler',email     := 'justatheory@pgxn.org',uri       := 'http://www.justatheory.com/',twitter   :- 'theory'    );     update_user     ─────────────     tUpdate the specifIEd user. The user must be active. The nickname cannot bechanged. The password can only be changed via `change_password()` or`reset_password()`. Pass other attributes as:full_name: The full name of the user.email: The email address of the user. Must be a valID email address as verifIEd by  [Email::ValID](http://search.cpan.org/perldoc?Email::ValID).uri: Optional URI for the user. Should be a valID URI as verifIEd by  [Data::ValIDate::URI](http://search.cpan.org/perldoc?Data::ValIDate::URI).twitter: Optional Twitter username. A leading "@" wil be removed.Returns true if the user was updated,and false if not.*/BEGIN    UPDATE users       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()     WHERE users.nickname = update_user.nickname       AND users.status   = 'active';    RETURN FOUND;END;$$;

总结

以上是内存溢出为你收集整理的如何在Postgresql中编写更新函数(存储过程)？全部内容，希望文章能够帮你解决如何在Postgresql中编写更新函数(存储过程)？所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。