gpt4 book ai didi

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

转载 作者:行者123 更新时间:2023-11-29 11:14:30 25 4
gpt4 key购买 nike

我想在 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, integer, integer, text, text, integer, text, text, integer, 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 - 如何在 Postgresql 中编写更新函数(存储过程)?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/21087710/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com