Re: Verifying a timestamp is null or in the past

Alexander Farber <alexander(dot)farber(at)gmail(dot)com> wrote:

> 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?

Try "if (not coalesce(has_vip, false)) then ..."

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°