In PostgreSQL 8.4.9 I have a small game, where users 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 |
If vip has never been purchased it will be NULL.
If vip has expired it will be < CURRENT_TIMESTAMP.
I'm trying to create PL/pgSQL procedure allowing users 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;
Unfortunately this procedure does not work properly if vip of the giving user is NULL:
# update pref_users set vip=null where id='DE16290';
UPDATE 1
# select id,vip from pref_users where id in ('DE16290', 'DE1');
id | vip
---------+----------------------------
DE1 | 2012-01-05 17:35:17.772043
DE16290 |
(2 rows)
# select pref_move_week('DE16290', 'DE1');
pref_move_week
----------------
(1 row)
# select id,vip from pref_users where id in ('DE16290', 'DE1');
id | vip
---------+----------------------------
DE1 | 2012-01-05 17:43:11.589922
DE16290 | 2011-12-22 17:43:11.589922
(2 rows)
I.e. the IF-statement above doesn't seem to work and falls through.
Also I wonder if the has_vip variable is needed here at all?
And how could I ensure that the primary keys _from and _to are really present in the pref_users table or is this already taken care of (because one of the UPDATE statements will "throw an exception" and the transaction will be rollbacked)?
UPDATE:
Thank you for all the replies and I've also got a tip to use:
if (not coalesce(has_vip, false)) then
return;
end if;
But now I have a new problem:
# select id,vip from pref_users where id in ('DE16290', 'DE1');
id | vip
---------+----------------------------
DE1 | 2012-01-05 17:43:11.589922
DE16290 |
(2 rows)
(i.e. DE1 has vip until May and should be able to give a week to DE16290, but):
# select pref_move_week('DE1', 'DE16290');
pref_move_week
----------------
(1 row)
# select id,vip from pref_users where id in ('DE16290', 'DE1');
id | vip
---------+----------------------------
DE1 | 2012-01-05 17:43:11.589922
DE16290 |
(2 rows)
(For some reason nothing has changed?)
UPDATE 2: The final solution -
create or replace function pref_move_week(_from varchar,
_to varchar) returns void as $BODY$
begin
select 1 from pref_users
where id=_from and
vip > current_timestamp + interval '1 week';
if not found then
return;
end if;
update pref_users set
vip = vip - interval '1 week'
where id=_from;
update pref_users set
vip = greatest(vip, current_timestamp) + interval '1 week'
where id=_to;
end;
$BODY$ language plpgsql;
Null values return false on any compare short of is null (so where null <> 1 is false and null = 1 is false...null never equals or not equals anything). Instead of
if (not has_vip) then return
go with
if (not has_vip) or has_vip is null then return
The syntax you are using is a bit unique here and I'm not used to reading it...guessing you've come from a different background than SQL and haven't encountered null fun yet. Null is a special concept in SQL that you'll need to handle differently. Remember it is not equal to blank or 0, nor is it less than or greater than any number.
Edit in: I'm a bit confused by this:
select vip > current_timestamp + interval '1 week'
into has_vip from pref_users where id=_from;
I believe this is equivalent to:
select vip
into has_vip from pref_users
where id=_from
and vip > current_timestamp + interval '1 week';
I hope anyway. Now this is going to return a null if no record is found or if the VIP value is null. can you just go if has_vip is null then return?