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

我想在 postgresql中创建一个更新函数(存储过程),我在google上搜索了很多次,但没有找到更新函数(存储过程)的正确示例.如何在 Postgresql中编写更新函数并更改表中的现有数据？

提前致谢.

Example of Function

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)
  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 
    ─────────────
     t

Update the specified user. The user must be active. The nickname cannot be
changed. 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中编写更新函数(存储过程)？

猜你在找的Postgre SQL相关文章