From: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Verifying a timestamp is null or in the past |
Date: | 2011-12-29 18:15:54 |
Message-ID: | CAADeyWhZUT0RnSP75_U=GzL_LWivBhaxHShzzy=UWytiYAgr0Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Andreas Kretschmer | 2011-12-29 18:44:00 | Re: Verifying a timestamp is null or in the past |
Previous Message | Adrian Klaver | 2011-12-29 17:54:12 | Re: PostgreSQL 9.1 pg_dump setval() sets wrong value |