Re: Verifying a timestamp is null or in the past

Hello again,

> On Fri, Dec 30, 2011 at 2:07 PM, Alban Hertroys <haramrae(at)gmail(dot)com> wrote:
>> select 1 from pref_users where id=_from and vip > current_timestamp + interval '1 week';
>>
>> if not found then
>>    return;
>> end if;
>>

unfortunately I get the error in PostgreSQL 8.4.9:

# select pref_move_week('DE16290', 'DE1');
ERROR: query has no destination for result data
HINT: If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT: PL/pgSQL function "pref_move_week" line 3 at SQL statement

# 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 is not NULL 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;

while a single SELECT works:

# select 1 from pref_users
where id='DE1' and
vip is not NULL and
vip > current_timestamp + interval '1 week';
?column?
----------
1
(1 row)

Regards
Alex