Re: Verifying a timestamp is null or in the past

On 29 Dec 2011, at 19:15, Alexander Farber wrote:

> I'm trying to create PL/pgSQL procedure allowing
> players with enough vip status left
> to give a week of it to other users, as a "gift":
>
> 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 has_vip) then
> return;
> end if;

I would probably write that as:

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

if not found then
return;
end if;

"found" is a special pl/psql keyword that tells whether the last query returned any results or not. Using that you can get rid of the entire declare-block in your function ;)

Originally I tacked a "vip is not null or" before the check in the where-clause, but that's unnecessary - if vip is null, then the expression also evaluates to null and the where-clause will treat it as false. That's one of the peculiarities of SQL... For posterity's sake it may be better to add that part to the query anyway, that's up to personal preference:

select 1 from pref_users where id=_from and (vip is not null or vip > current_timestamp + interval '1 week');

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.