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;