Hello again,
> On Fri, Dec 30, 2011 at 2:07 PM, Alban Hertroys <haramrae@gmail.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
