Re: Verifying a timestamp is null or in the past

On 31/12/2011 06:15, Alexander Farber wrote:
> 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';

As the error message says, if you don't need the result of the SELECT
then do PERFORM instead:

perform 1 from pref_users...

In plpgsql, if you use SELECT, you need INTO also; the result has to go
somewhere.

select 1 into previously_declared_variable from ....

HTH,

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod(at)iol(dot)ie