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 CAADeyWg33Bci2bOcMBRmsrxn1HcaN1hdKMFBF-_2B-qZ2FRwiQ@mail.gmail.com
Whole thread Raw
In response to Verifying a timestamp is null or in the past  (Alexander Farber <alexander.farber@gmail.com>)
List pgsql-general
Hello again, please 1 more question:

can I have a SELECT statement inside of an IF-conditional?

The doc
http://www.postgresql.org/docs/8.4/static/plpgsql-control-structures.html
does not list such an example.

I'm asking, because I'd like to get rid of the has_vip
variable in my rewritten procedure below:

/* move 1 week of VIP-status from
   player _from to player _to */

create or replace function pref_move_week(_from varchar,
    _to varchar) returns void as $BODY$
        declare
                has_vip timestamp;
        begin

        select vip into has_vip from pref_users
            where id=_from
            and vip > current_timestamp + interval '1 week';

        if (has_vip is NULL) 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;

pgsql-general by date:

Previous
From: dhaval jaiswal
Date:
Subject: streaming replication vacuum
Next
From: Magnus Hagander
Date:
Subject: Re: streaming replication vacuum