Mercurial > gemma

comparison schema/manage_users.sql @ 233:531d1f8a2b4b

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Fix spelling
author Tom Gottfried <tom@intevation.de>
date Thu, 26 Jul 2018 15:02:19 +0200
parents 4859aa6c96be
children 946baea3d280
comparison
equal deleted inserted replaced
232:4859aa6c96be 233:531d1f8a2b4b
7 userrole varchar, 7 userrole varchar,
8 username users.user_profiles.username%TYPE, 8 username users.user_profiles.username%TYPE,
9 pw varchar, 9 pw varchar,
10 country users.user_profiles.country%TYPE, 10 country users.user_profiles.country%TYPE,
11 map_extent users.user_profiles.map_extent%TYPE, 11 map_extent users.user_profiles.map_extent%TYPE,
12 email_adress users.user_profiles.email_adress%TYPE 12 email_address users.user_profiles.email_address%TYPE
13 ) 13 )
14 RETURNS void 14 RETURNS void
15 AS $$ 15 AS $$
16 BEGIN 16 BEGIN
17 IF map_extent IS NULL 17 IF map_extent IS NULL
18 THEN 18 THEN
19 map_extent = ST_Extent(area) FROM users.responsibility_areas ra 19 map_extent = ST_Extent(area) FROM users.responsibility_areas ra
20 WHERE ra.country = create_user.country; 20 WHERE ra.country = create_user.country;
21 END IF; 21 END IF;
22 INSERT INTO users.user_profiles VALUES ( 22 INSERT INTO users.user_profiles VALUES (
23 username, country, map_extent, email_adress); 23 username, country, map_extent, email_address);
24 EXECUTE format( 24 EXECUTE format(
25 'CREATE ROLE %I IN ROLE %I LOGIN PASSWORD %L', username, userrole, pw); 25 'CREATE ROLE %I IN ROLE %I LOGIN PASSWORD %L', username, userrole, pw);
26 END; 26 END;
27 $$ 27 $$
28 LANGUAGE plpgsql 28 LANGUAGE plpgsql
34 new_userrole varchar, 34 new_userrole varchar,
35 new_username users.user_profiles.username%TYPE, 35 new_username users.user_profiles.username%TYPE,
36 new_pw varchar, 36 new_pw varchar,
37 new_country users.user_profiles.country%TYPE, 37 new_country users.user_profiles.country%TYPE,
38 new_map_extent users.user_profiles.map_extent%TYPE, 38 new_map_extent users.user_profiles.map_extent%TYPE,
39 new_email_adress users.user_profiles.email_adress%TYPE 39 new_email_address users.user_profiles.email_address%TYPE
40 ) 40 )
41 RETURNS void 41 RETURNS void
42 AS $$ 42 AS $$
43 DECLARE 43 DECLARE
44 cur_username name; 44 cur_username name;
45 cur_userrole name; 45 cur_userrole name;
46 BEGIN 46 BEGIN
47 cur_username = username; 47 cur_username = username;
48 48
49 UPDATE users.user_profiles p 49 UPDATE users.user_profiles p
50 SET (username, country, map_extent, email_adress) 50 SET (username, country, map_extent, email_address)
51 = (new_username, new_country, new_map_extent, new_email_adress) 51 = (new_username, new_country, new_map_extent, new_email_address)
52 WHERE p.username = cur_username; 52 WHERE p.username = cur_username;
53 53
54 IF new_username <> cur_username 54 IF new_username <> cur_username
55 THEN 55 THEN
56 EXECUTE format( 56 EXECUTE format(