Verifying a timestamp is null or in the past

[Alexander Farber]

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

[Andreas Kretschmer]

Post by Alexander Farber
Hello fellow postgres users,
in my game using PostgreSQL 8.4.9 players can
# \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
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°

[Alexander Farber]

Thank you Andreas - now that one case works ok,

On Thu, Dec 29, 2011 at 7:44 PM, Andreas Kretschmer

Post by Andreas Kretschmer
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

[David Johnston]

-----Original Message-----
From: pgsql-general-***@postgresql.org
[mailto:pgsql-general-***@postgresql.org] On Behalf Of Alexander Farber
Sent: Thursday, December 29, 2011 3:01 PM
Cc: pgsql-***@postgresql.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

Post by Andreas Kretschmer
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.

[Alexander Farber]

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;

[Alban Hertroys]

Post by Alexander Farber
I'm trying to create PL/pgSQL procedure allowing
players with enough vip status left
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.

[Alexander Farber]

Awesome advices here.

Thank you and happy new year.

Post by Alban Hertroys
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 ;)
select 1 from pref_users where id=_from and (vip is not null or vip > current_timestamp + interval '1 week');

[Alexander Farber]

Hello again,

Post by Alban Hertroys
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

[Alexander Farber]

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

[Raymond O'Donnell]

Post by Alexander Farber
Hello again,

Post by Alban Hertroys
select 1 from pref_users where id=_from and vip > current_timestamp + interval '1 week';
if not found then
return;
end if;

# 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.

[Alexander Farber]

Hello Ray and others,

Post by Raymond O'Donnell

Post by Alexander Farber
# 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
 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

[Raymond O'Donnell]

Post by Alexander Farber
Hello Ray and others,

Post by Raymond O'Donnell

Post by Alexander Farber
# 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
perform 1 from pref_users...

# 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.

Post by Alexander Farber
And also - does PERFORM works with FOUND?

Not sure what you mean - can you elaborate?

Ray.

[Ondrej Ivanič]

Hi,

Post by Raymond O'Donnell

Post by Alexander Farber
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;

[David Johnston]

Post by Ondrej Ivanič
Hi,

Post by Raymond O'Donnell

Post by Alexander Farber
And also - does PERFORM works with FOUND?

Not sure what you mean - can you elaborate?

http://www.postgresql.org/docs/9.0/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS
get diagnostics rr = row_count;
--
Ondrej Ivanic

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.

[Ondrej Ivanič]

Hi

Post by David Johnston
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'...