Exploring Stored Procedures in MySQL and PostgreSQL

The PostgreSQL PL/pgSQL procedural language is well documented here and the MySQL Reference Manual is available here. The MySQL documentation is, in my opinion, lacking, however the MySQL Stored Procedure Forum helps a great deal in making up for the lack of documentation.

Now, let’s take for example this simplified user table. In reality it would contain additional information, a hashed password, real name, who knows, but for the sake of our example, we’ll keep it minimal. Once the table is created, we will want to create a stored procedure that automatically expires accounts that have not logged on in more than a month.

CREATE TABLE users (
   username varchar(12),
   last_login timestamp,
   expired boolean
);

Now, we could create a stored procedure that expires all accounts that have not logged on in a month, but to make it more versatile, and to demonstrate more of the capabilities, we will allow the user to define the number of days since last logged on before the account expires.

First, let’s see how this we could write this for PostgreSQL, we will be using the PL/pgSQL procedural language.

CREATE OR REPLACE FUNCTION mark_expired(days INT) RETURNS VOID AS
$$
DECLARE
   delta BIGINT;
BEGIN
   delta := $1*86400;
   UPDATE users SET expired=true WHERE EXTRACT(epoch from age(CURRENT_TIMESTAMP, last_login)) > delta;
END;
$$
LANGUAGE plpgsql;

Let’s look at the user table before running the stored procedure

 username  |     last_login      | expired
----------+---------------------+---------
 homer    | 2008-12-18 00:00:00 | f
 marge    | 2008-11-18 00:00:00 | f
 bart     | 2008-12-14 00:00:00 | f
 lisa     | 2008-12-17 00:00:00 | f
 maggie   | 2008-12-19 00:00:00 | f

Here is how you would execute the stored procedure, with our user defined 30 day argument.

testdb=# select mark_expired(30);
testdb=# select * from users;
 username |     last_login      | expired
----------+---------------------+---------
 homer    | 2008-12-18 00:00:00 | f
 bart     | 2008-12-14 00:00:00 | f
 lisa     | 2008-12-17 00:00:00 | f
 maggie   | 2008-12-19 00:00:00 | f
 marge    | 2008-11-18 00:00:00 | t

Success!, Marge’s account has now been marked expired, since she has not logged on in the last 30 days.

Your first inclination may be to extract the day from age, but this does not work as one may think. Take for example the following…

testdb=# select age(now(), CURRENT_DATE-31);
             age
-----------------------------
 1 mon 1 day 13:02:30.065623
(1 row)

Now if we extract day, this is what we get…

testdb=# select extract(day from age(now(), CURRENT_DATE-31));
 date_part
-----------
         1
(1 row)

This is not in fact what we were looking for, if we were to use this method to expire accounts, this account appears to have been logged in as recently as one day ago, when in fact 31 days have elapsed. So this is why we chose to use epoch, which in this case will get total elapsed seconds.

Now, let’s review how you might write this for MySQL.

DELIMITER //
CREATE PROCEDURE mark_expired (days INT)
BEGIN
   UPDATE users SET expired = true
              WHERE last_login < SUBDATE(CURRENT_TIMESTAMP, INTERVAL days DAY);
END //
DELIMITER ;

In this example we take a slightly different approach, we subtract the days argument from the current timestamp, and see if the last login timestamp is older than this. This leverages MySQL's SUBDATE function and avoids having to convert days into seconds.

Let's take a look at our table again, before calling the procedure

mysql> select * from users;
+----------+---------------------+---------+
| username | last_login          | expired |
+----------+---------------------+---------+
| homer    | 2008-12-18 00:00:00 |       0 |
| marge    | 2008-11-18 00:00:00 |       0 |
| bart     | 2008-12-14 00:00:00 |       0 |
| lisa     | 2008-12-17 00:00:00 |       0 |
| maggie   | 2008-12-19 00:00:00 |       0 |
+----------+---------------------+---------+

And how you would call the procedure in MySQL.

mysql> CALL mark_expired(30);
mysql> select * from users;
+----------+---------------------+---------+
| username | last_login          | expired |
+----------+---------------------+---------+
| homer    | 2008-12-18 00:00:00 |       0 |
| marge    | 2008-11-18 00:00:00 |       1 |
| bart     | 2008-12-14 00:00:00 |       0 |
| lisa     | 2008-12-17 00:00:00 |       0 |
| maggie   | 2008-12-19 00:00:00 |       0 |
+----------+---------------------+---------+

Again, success, Marge's account has been expired.