Search Postgresql Archives

Re: Verifying a timestamp is null or in the past

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux