1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
|
/*
* Source: https://stackoverflow.com/questions/21087710/how-to-write-update-function-stored-procedure-in-postgresql
*/
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;
$$;
|
