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

Question

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

21

我想在PostgreSQL中创建一个更新函数 (存储过程)，我已经在Google上搜索了很多次，但没有找到一个合适的更新函数 (存储过程) 的示例。请问如何编写一个更新函数并修改表中现有的数据？

先行致谢。

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;

- Iren Patel

什么是更新函数？我不知道这个术语。这个函数应该做什么？ - Pavel Stehule

一个存储过程可以在Postgresql中调用函数。 - Iren Patel

还是不太明白，你能给一些例子吗？ - Pavel Stehule

@Pavel，现在你看到我在问题中放了一个函数的例子。 - Iren Patel

在PostgreSQL术语中，这是一个带有嵌入式SQL语句的空标量函数。它可以用SQL或PL/pgSQL语言实现。"更新函数"这个名称并不常见。 - Pavel Stehule

2个回答

5

PostgreSQL将双引号作为列名。请使用单引号进行替换，或者您可以使用参数来代替。

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;

- darkangel

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Denis de Bernardy · Accepted Answer

你可以在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;
$$;