]> git.stg.codes - stg.git/blob - projects/stargazer/inst/var/00-alter-01.postgresql.sql
Catch proper exception.
[stg.git] / projects / stargazer / inst / var / 00-alter-01.postgresql.sql
1 /*
2  *  DB migration from v00 to v01 (postgres)
3  */
4
5 ALTER TABLE tb_sessions_log ADD free_mb dm_money;
6 ALTER TABLE tb_sessions_log ADD reason TEXT;
7
8 DROP FUNCTION sp_add_session_log_entry ( dm_name, timestamp without time zone, dm_session_event_type, inet, dm_money);
9
10 CREATE FUNCTION sp_add_session_log_entry(_login dm_name,
11                                          _event_time TIMESTAMP,
12                                          _event_type dm_session_event_type,
13                                          _ip INET,
14                                          _cash dm_money,
15                                          _free_mb dm_money,
16                                          _reason TEXT)
17 RETURNS INTEGER
18 AS $$
19 DECLARE
20     _pk_user INTEGER;
21     _pk_session_log INTEGER;
22 BEGIN
23     SELECT pk_user INTO _pk_user
24         FROM tb_users
25         WHERE name = _login;
26     IF _pk_user IS NULL THEN
27         RAISE EXCEPTION 'User % not found', _login;
28         RETURN -1;
29     END IF;
30     
31     INSERT INTO tb_sessions_log
32         (fk_user,
33          event_time,
34          event_type,
35          ip,
36          cash,
37          free_mb,
38          reason)
39     VALUES
40         (_pk_user,
41          _event_time,
42          _event_type,
43          _ip,
44          _cash,
45          _free_mb,
46          _reason);
47
48     SELECT CURRVAL('tb_sessions_log_pk_session_log_seq') INTO _pk_session_log;
49
50     RETURN _pk_session_log;
51 END;
52 $$ LANGUAGE plpgsql;
53
54 UPDATE tb_info SET version = 6;