Re: Verifying a timestamp is null or in the past

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Alexander Farber
Sent: Thursday, December 29, 2011 3:01 PM
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Verifying a timestamp is null or in the past

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

----------------------------------------------------------------------------
------
Alexander,

The following update confuses me:
                update pref_users set vip = current_timestamp - interval '1
week' where id=_from;

You end up setting "vip" to a date one week in the past ALWAYS; regardless
of whether subtracting a week from "VIP" would result in a time still in the
future.

I am thinking maybe you are not providing the correct update code?  If the
code goes something like:

    Update pref_users SET vip = vip + '1 week'::interval WHERE id = _to;

You are going to still have issues since adding anything to "NULL" results
in NULL. You probably want something like:

    Update pref_users SET vip = COALESCE(vip, current_timestamp) + '1
week'::interval WHERE id = _to;

Adding a Raise Notice within the pl/pgsql block (just before the return
within the IF) would help you determine whether the "UPDATE" statements are
being reached (but have no effect) or whether the procedure is ending early.

Also, are you positive that the construct "... + '1 week'::interval", when
using the current_timestamp and VIP timestamp of '2010-01-05 17:43 ...',
indeed evaluates to "TRUE"?

David J.