Re: Verifying a timestamp is null or in the past

Alexander Farber <alexander.farber@gmail.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°