Re: Verifying a timestamp is null or in the past

Hello fellow postgres users,

in my game using PostgreSQL 8.4.9 players can
purchase a VIP ("very important person") status:

# \d pref_users;
Table "public.pref_users"
Column | Type | Modifiers
------------+-----------------------------+---------------
id | character varying(32) | not null
vip | timestamp without time zone |

I.e. if vip has never been purchased it will be NULL.

An expired vip will be < CURRENT_TIMESTAMP.

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;

update pref_users set vip = current_timestamp - interval '1
week' where id=_from;
update pref_users set vip = current_timestamp + interval '1
week' where id=_to;

end;
$BODY$ language plpgsql;

This procedure compiles, but unfortunately
the IF-statement falls through for
_from players with vip=NULL

Does anybody please have an advice
what to change here and maybe the
has_vip variable isn't really needed either?

Thank you
Alex

Alexander Farber <alexander(dot)farber(at)gmail(dot)com> wrote:

> Hello fellow postgres users,
>
> in my game using PostgreSQL 8.4.9 players can
> purchase a VIP ("very important person") status:
>
> # \d pref_users;
> Table "public.pref_users"
> Column | Type | Modifiers
> ------------+-----------------------------+---------------
> id | character varying(32) | not null
> vip | timestamp without time zone |
>
> I.e. if vip has never been purchased it will be NULL.
>
> An expired vip will be < CURRENT_TIMESTAMP.
>
> 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;
>
> update pref_users set vip = current_timestamp - interval '1
> week' where id=_from;
> update pref_users set vip = current_timestamp + interval '1
> week' where id=_to;
>
> end;
> $BODY$ language plpgsql;
>
> This procedure compiles, but unfortunately
> the IF-statement falls through for
> _from players with vip=NULL
>
> Does anybody please have an advice
> what to change here and maybe the
> has_vip variable isn't really needed either?

Try "if (not coalesce(has_vip, false)) then ..."

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

Thank you Andreas - now that one case works ok,

On Thu, Dec 29, 2011 at 7:44 PM, Andreas Kretschmer
<akretschmer(at)spamfence(dot)net> wrote:
> Try "if (not coalesce(has_vip, false)) then ..."

but the other case not:

# 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 coalesce(has_vip, false)) then
return;
end if;

update pref_users set vip = current_timestamp -
interval '1 week' where id=_from;
update pref_users set vip = current_timestamp +
interval '1 week' where id=_to;

end;
$BODY$ language plpgsql;

# select id,vip from pref_users where id in ('DE16290', 'DE1');
id | vip
---------+----------------------------
DE1 | 2012-01-05 17:43:11.589922
DE16290 |
(2 rows)

(I.e. player DE1 has vip until May and should
be able to give a week of VIP to DE16290, but):

# select pref_move_week('DE1', 'DE16290');
pref_move_week
----------------

(1 row)

# select id,vip from pref_users where id in ('DE16290', 'DE1');
id | vip
---------+----------------------------
DE1 | 2012-01-05 17:43:11.589922
DE16290 |
(2 rows)

(For some reason nothing has changed?)

Regards
Alex

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Alexander Farber
Sent: Thursday, December 29, 2011 3:01 PM
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Verifying a timestamp is null or in the past

Thank you Andreas - now that one case works ok,

On Thu, Dec 29, 2011 at 7:44 PM, Andreas Kretschmer
<akretschmer(at)spamfence(dot)net> wrote:
> Try "if (not coalesce(has_vip, false)) then ..."

but the other case not:

# 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 coalesce(has_vip, false)) then
return;
end if;

update pref_users set vip = current_timestamp - interval '1
week' where id=_from;
update pref_users set vip = current_timestamp + interval '1
week' where id=_to;

end;
$BODY$ language plpgsql;

# select id,vip from pref_users where id in ('DE16290', 'DE1');
id | vip
---------+----------------------------
DE1 | 2012-01-05 17:43:11.589922
DE16290 |
(2 rows)

(I.e. player DE1 has vip until May and should be able to give a week of VIP
to DE16290, but):

# select pref_move_week('DE1', 'DE16290'); pref_move_week
----------------

(1 row)

# select id,vip from pref_users where id in ('DE16290', 'DE1');
id | vip
---------+----------------------------
DE1 | 2012-01-05 17:43:11.589922
DE16290 |
(2 rows)

(For some reason nothing has changed?)

Regards
Alex

----------------------------------------------------------------------------
------
Alexander,

The following update confuses me:
update pref_users set vip = current_timestamp - interval '1
week' where id=_from;

You end up setting "vip" to a date one week in the past ALWAYS; regardless
of whether subtracting a week from "VIP" would result in a time still in the
future.

I am thinking maybe you are not providing the correct update code? If the
code goes something like:

Update pref_users SET vip = vip + '1 week'::interval WHERE id = _to;

You are going to still have issues since adding anything to "NULL" results
in NULL. You probably want something like:

Update pref_users SET vip = COALESCE(vip, current_timestamp) + '1
week'::interval WHERE id = _to;

Adding a Raise Notice within the pl/pgsql block (just before the return
within the IF) would help you determine whether the "UPDATE" statements are
being reached (but have no effect) or whether the procedure is ending early.

Also, are you positive that the construct "... + '1 week'::interval", when
using the current_timestamp and VIP timestamp of '2010-01-05 17:43 ...',
indeed evaluates to "TRUE"?

David J.

Hello again, please 1 more question:

can I have a SELECT statement inside of an IF-conditional?

The doc
http://www.postgresql.org/docs/8.4/static/plpgsql-control-structures.html
does not list such an example.

I'm asking, because I'd like to get rid of the has_vip
variable in my rewritten procedure below:

/* move 1 week of VIP-status from
player _from to player _to */

create or replace function pref_move_week(_from varchar,
_to varchar) returns void as $BODY$
declare
has_vip timestamp;
begin

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

if (has_vip is NULL) 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;

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.

Awesome advices here.

Thank you and happy new year.

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;
>
> "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');

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

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

Is it because my procedure is declared as "void"?

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

Hello Ray and others,

On Sat, Dec 31, 2011 at 10:26 AM, Raymond O'Donnell <rod(at)iol(dot)ie> wrote:
>> # 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...
>

I've tried that of course, but "perform 1 ..." fails with 8.4.9:

# select 1 from pref_users
where id='DE1' and
vip is not NULL and
vip > current_timestamp + interval '1 week';
?column?
----------
1
(1 row)

# perform 1 from pref_users
where id='DE1' and
vip is not NULL and
vip > current_timestamp + interval '1 week';
ERROR: syntax error at or near "perform"
LINE 1: perform 1 from pref_users
^

And also - does PERFORM works with FOUND?

Thank you
Alex

On 01/01/2012 14:42, Alexander Farber wrote:
> Hello Ray and others,
>
> On Sat, Dec 31, 2011 at 10:26 AM, Raymond O'Donnell <rod(at)iol(dot)ie> wrote:
>>> # 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...
>>
>
> I've tried that of course, but "perform 1 ..." fails with 8.4.9:
>
>
> # select 1 from pref_users
> where id='DE1' and
> vip is not NULL and
> vip > current_timestamp + interval '1 week';
> ?column?
> ----------
> 1
> (1 row)
>
> # perform 1 from pref_users
> where id='DE1' and
> vip is not NULL and
> vip > current_timestamp + interval '1 week';
> ERROR: syntax error at or near "perform"
> LINE 1: perform 1 from pref_users

Is this in a pl/pgsql function, or a straight SQL query? PERFORM in this
form is a pl/pgsql construct; you'll get a syntax error if you try it at
the psql command line.

> And also - does PERFORM works with FOUND?

Not sure what you mean - can you elaborate?

Ray.

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

Hi,

On 2 January 2012 03:26, Raymond O'Donnell <rod(at)iol(dot)ie> wrote:
>> And also - does PERFORM works with FOUND?
>
> Not sure what you mean - can you elaborate?

No, perform (and execute) doesn't populate 'found' variable:
http://www.postgresql.org/docs/9.0/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS

You have to use something like this:
get diagnostics rr = row_count;

--
Ondrej Ivanic
(ondrej(dot)ivanic(at)gmail(dot)com)

On Jan 2, 2012, at 16:46, Ondrej Ivanič <ondrej(dot)ivanic(at)gmail(dot)com> wrote:

> Hi,
>
> On 2 January 2012 03:26, Raymond O'Donnell <rod(at)iol(dot)ie> wrote:
>>> And also - does PERFORM works with FOUND?
>>
>> Not sure what you mean - can you elaborate?
>
> No, perform (and execute) doesn't populate 'found' variable:
> http://www.postgresql.org/docs/9.0/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS
>
> You have to use something like this:
> get diagnostics rr = row_count;
>
>
> --
> Ondrej Ivanic
> (ondrej(dot)ivanic(at)gmail(dot)com)
>
>

Yes, PERFORM does populate FOUND.

From the documentation you just linked to....

A PERFORM statement sets FOUND true if it produces (and discards) one or more rows, false if no row is produced.

Hi

2012/1/3 David Johnston <polobo(at)yahoo(dot)com>:
> On Jan 2, 2012, at 16:46, Ondrej Ivanič <ondrej(dot)ivanic(at)gmail(dot)com> wrote:
> Yes, PERFORM does populate FOUND.
>
> From the documentation you just linked to....
>
> A PERFORM statement sets FOUND true if it produces (and discards) one or
> more rows, false if no row is produced.
>

Bummer! Thanks for the correction! I shouldn't (blindly) rely on my
own comments in the code :) Pgpsql code uses "execute" which is the
reason for 'get diagnostics'...

--
Ondrej Ivanic
(ondrej(dot)ivanic(at)gmail(dot)com)