Expires and auto delete

[fundon]

http://stackoverflow.com/questions/26046816/is-there-a-way-to-set-an-expiry-time-after-wich-a-data-entry-is-automatically

[fundon]

• https://github.com/postgres-ci/core/blob/master/src/functions/auth/gc.sql

create or replace function auth.gc() returns void as $$
    begin 
        DELETE FROM postgres_ci.sessions WHERE expires_at < CURRENT_TIMESTAMP;
    end;
$$ language plpgsql security definer;

• https://github.com/postgres-ci/core/blob/master/src/functions/auth/login.sql

create or replace function auth.login(
    _login         text, 
    _password      text, 
    out session_id text
) returns text as $$
    declare
        _user_id          int;
        _invalid_password boolean;
    begin 

        SELECT
            U.user_id,
            encode(digest(U.salt || _password, 'sha1'), 'hex') != U.hash
            INTO
                _user_id,
                _invalid_password
        FROM  postgres_ci.users AS U
        WHERE lower(U.user_login) = lower(_login)
        AND   is_deleted          = false;

        CASE 
            WHEN NOT FOUND THEN
                RAISE EXCEPTION 'NOT_FOUND' USING ERRCODE = 'no_data_found';
            WHEN _invalid_password THEN 
                RAISE EXCEPTION 'INVALID_PASSWORD' USING ERRCODE = 'invalid_password';
            ELSE 
                INSERT INTO postgres_ci.sessions (
                    user_id,
                    expires_at
                ) VALUES (
                    _user_id,
                    CURRENT_TIMESTAMP + '1 hour'::interval
                ) RETURNING sessions.session_id INTO session_id;
        END CASE;
    end;
$$ language plpgsql security definer;

• https://github.com/postgres-ci/core/blob/master/src/functions/auth/logout.sql

create or replace function auth.logout(_session_id text) returns void as $$
    begin 
        DELETE FROM postgres_ci.sessions WHERE session_id = _session_id;
    end;
$$ language plpgsql security definer;

• https://github.com/postgres-ci/core/blob/master/src/functions/auth/get_user.sql

create or replace function auth.get_user(
    _session_id text
) returns table(
    user_id      int,
    user_name    text,
    user_login   text,
    user_email   text,
    is_superuser boolean,
    created_at   timestamptz
) as $$
    begin 

        return query 
        SELECT 
            U.user_id,
            U.user_name,
            U.user_login,
            U.user_email,
            U.is_superuser,
            U.created_at
        FROM postgres_ci.users    AS U 
        JOIN postgres_ci.sessions AS S USING(user_id)
        WHERE U.is_deleted = false
        AND   S.session_id = _session_id
        AND   S.expires_at > CURRENT_TIMESTAMP;

        IF FOUND THEN 
            UPDATE postgres_ci.sessions 
                SET 
                    expires_at = CURRENT_TIMESTAMP + '1 hour'::interval 
            WHERE session_id   = _session_id;
        END IF;
    end;
$$ language plpgsql security definer rows 1;