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@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';

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@iol.ie