Re: Verifying a timestamp is null or in the past

[Alexander Farber <alexander.farber@xxxxxxxxx>]

Hello again,

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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general