Re: Verifying a timestamp is null or in the past - Mailing list pgsql-general

From Alexander Farber
Subject Re: Verifying a timestamp is null or in the past
Date
Msg-id CAADeyWg0ddU3GexPrpG5-1ZqaBmDn6rAFgo9_p5vhUVxVs2FJw@mail.gmail.com
Whole thread Raw
In response to Re: Verifying a timestamp is null or in the past  (Andreas Kretschmer <akretschmer@spamfence.net>)
Responses Re: Verifying a timestamp is null or in the past
List pgsql-general
Thank you Andreas - now that one case works ok,

On Thu, Dec 29, 2011 at 7:44 PM, Andreas Kretschmer
<akretschmer@spamfence.net> wrote:
> Try "if (not coalesce(has_vip, false)) then ..."

but the other case not:

#  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 coalesce(has_vip, false)) 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;


# 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. player DE1 has vip until May and should
be able to give a week of VIP 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?)

Regards
Alex

pgsql-general by date:

Previous
From: Andreas Kretschmer
Date:
Subject: Re: Verifying a timestamp is null or in the past
Next
From: "David Johnston"
Date:
Subject: Re: Verifying a timestamp is null or in the past