Verifying a timestamp is null or in the past

Поиск
Список
Период
Сортировка
От Alexander Farber
Тема Verifying a timestamp is null or in the past
Дата
Msg-id CAADeyWhZUT0RnSP75_U=GzL_LWivBhaxHShzzy=UWytiYAgr0Q@mail.gmail.com
обсуждение исходный текст
Ответы Re: Verifying a timestamp is null or in the past  (Andreas Kretschmer <akretschmer@spamfence.net>)
Re: Verifying a timestamp is null or in the past  (Alban Hertroys <haramrae@gmail.com>)
Список pgsql-general
Hello fellow postgres users,

in my game using PostgreSQL 8.4.9 players can
purchase a VIP ("very important person") status:

# \d pref_users;
                Table "public.pref_users"
   Column   |            Type             |   Modifiers
------------+-----------------------------+---------------
 id         | character varying(32)       | not null
 vip        | timestamp without time zone |

I.e. if vip has never been purchased it will be NULL.

An expired vip will be < CURRENT_TIMESTAMP.

I'm trying to create PL/pgSQL procedure allowing
players with enough vip status left
to give a week of it to other users, as a "gift":

create or replace function pref_move_week(_from varchar,
    _to varchar) returns void as $BODY$
        declare
                has_vip boolean;
        begin

        select vip > current_timestamp + interval '1 week'
            into has_vip from pref_users where id=_from;

        if (not has_vip) then
                return;
        end if;

        update pref_users set vip = current_timestamp - interval '1
week' where id=_from;
        update pref_users set vip = current_timestamp + interval '1
week' where id=_to;

        end;
$BODY$ language plpgsql;

This procedure compiles, but unfortunately
the IF-statement falls through for
_from players with vip=NULL

Does anybody please have an advice
what to change here and maybe the
has_vip variable isn't really needed either?

Thank you
Alex

В списке pgsql-general по дате отправления:

Предыдущее
От: Adrian Klaver
Дата:
Сообщение: Re: PostgreSQL 9.1 pg_dump setval() sets wrong value
Следующее
От: Andreas Kretschmer
Дата:
Сообщение: Re: Verifying a timestamp is null or in the past