Here we will create the composite type.
testdb=# create type ziprowtype as (zip varchar, lat float, lon float, city varchar, state varchar, state_abbrev varchar, distance float); CREATE TYPE

Now, to modify the stored procedure, you will need to change the return type from refcursor to SETOF ziprowtype. The body of the function changes a bit too.
First we load the result of our query into record type “r”, then loop and “RETURN NEXT r”.
testdb=# CREATE OR REPLACE FUNCTION zip_proximity2(varchar, double precision, varchar) RETURNS SETOF ziprowtype AS $_$ DECLARE home_lat float; home_lon float; r record; BEGIN SELECT lat, lon INTO home_lat, home_lon FROM zipcodes WHERE zip = $1; FOR r IN SELECT zip, lat, lon, city, state, state_abbrev, calculate_distance($3, home_lat, home_lon, lat, lon) AS distance FROM zipcodes WHERE calculate_distance($3, home_lat, home_lon, lat, lon) < $2 ORDER BY distance LOOP RETURN NEXT r; END LOOP; END; $_$ LANGUAGE plpgsql;
NOTE: To see how we implemented calculate_distance, please read this post.

Now, instead of using cursors and transactions, we can use the following query to return the desired results.
testdb=# select * from zip_proximity2('94043', 3.0, 'mi'); zip | lat | lon | city | state | state_abbrev | distance -------+----------+------------+---------------+------------+--------------+------------------- 94043 | 37.42337 | -122.07981 | MOUNTAIN VIEW | CALIFORNIA | CA | 0 94039 | 37.41884 | -122.09124 | MOUNTAIN VIEW | CALIFORNIA | CA | 0.701004112864842 94035 | 37.41753 | -122.05283 | MOUNTAIN VIEW | CALIFORNIA | CA | 1.53459076775345 94042 | 37.39314 | -122.07827 | MOUNTAIN VIEW | CALIFORNIA | CA | 2.09052870375317 94041 | 37.38961 | -122.07715 | MOUNTAIN VIEW | CALIFORNIA | CA | 2.33729808540454 94306 | 37.41478 | -122.12139 | PALO ALTO | CALIFORNIA | CA | 2.35776644118448 94303 | 37.44424 | -122.11736 | PALO ALTO | CALIFORNIA | CA | 2.51480871338068 (7 rows)
NOTE: If you’re getting ERROR: wrong record type supplied in RETURN NEXT, then it’s likely your composite type does not match the columns you’re querying.

You can also select any subset of the row (composite type) using standard SQL.
testdb=# select zip, distance from zip_proximity2('94043', 3.0, 'mi') limit 5; zip | distance -------+------------------- 94043 | 0 94039 | 0.701004115082249 94035 | 1.53459076784732 94042 | 2.09052870372395 94041 | 2.33729808557031 (5 rows)

As always, please feel free to leave a comment with any questions or suggestions.

The table that we will be using was created as follows:
CREATE TABLE zipcodes ( zip varchar(5), lat double precision, lon double precision, city varchar(30), state varchar(30), state_abbrev varchar(2));

I use 57.2958 as a constant for 180 / π to convert between degrees and radians, beyond this I will focus on the implementation and point you to the Haversine Formula and the Law of Consines if you wish to read more on the math.

First we will examine how these functions are written for PostgreSQL.

The first function implements the Law of Consines, and allows the user to specify which measurement (miles or kilometers) to use, to determine the distance between two coordinates.
CREATE FUNCTION calculate_distance(varchar, double precision, double precision, double precision, double precision) RETURNS double precision AS $_$ DECLARE earth_radius double precision; BEGIN IF $1 = 'mi' THEN earth_radius := 3959.0; ELSIF $1 = 'km' THEN earth_radius := 6371.0; END IF; RETURN earth_radius * acos(sin($2 / 57.2958) * sin($4 / 57.2958) + cos($2/ 57.2958) * cos($4 / 57.2958) * cos(($5 / 57.2958) - ($3 / 57.2958))); END; $_$ LANGUAGE plpgsql;
The calculate_distance function can certainly be used stand alone, but in our example it is called exclusively from our zip_proximity function below.

zip_proximity takes a refcursor, a zipcode, a distance, and a distance metric (‘mi’ or ‘km’).

First we retrieve the coordinates for the “home” zipcode, and query the databases, performing calculate_distance on each entry in the database, capturing only those that fall withing the given distance. We add a field to the cursor we return, which is the distance from the home zipcode, to the zipcode which fell within our provided distance.

CREATE FUNCTION zip_proximity(refcursor, character, double precision, varchar) RETURNS refcursor AS $_$ DECLARE home_lat float; home_lon float; BEGIN SELECT lat, lon INTO home_lat, home_lon FROM zipcodes WHERE zip = $2; OPEN $1 FOR SELECT zip, lat, lon, city, state, state_abbrev, calculate_distance($4, home_lat, home_lon, lat, lon) AS distance FROM zipcodes WHERE calculate_distance($4, home_lat, home_lon, lat, lon) < $3 ORDER BY distance; RETURN $1; END; $_$ LANGUAGE plpgsql;

Now we will query all zipcodes within a 3 mile radius of Mountain View, California 94043.
Here is how we call our new function(s). Since we are using cursors, we need to be in a transaction.
testdb=# BEGIN; BEGIN testdb=# SELECT zip_proximity('zc', '94043', 3, 'mi'); zip_proximity --------------- zc (1 row) testdb=# FETCH ALL FROM zc; zip | lat | lon | city | state | state_abbrev | distance -------+----------+------------+---------------+------------+--------------+------------------- 94043 | 37.42337 | -122.07981 | MOUNTAIN VIEW | CALIFORNIA | CA | 0 94039 | 37.41884 | -122.09124 | MOUNTAIN VIEW | CALIFORNIA | CA | 0.701004112864842 94035 | 37.41753 | -122.05283 | MOUNTAIN VIEW | CALIFORNIA | CA | 1.53459076775345 94042 | 37.39314 | -122.07827 | MOUNTAIN VIEW | CALIFORNIA | CA | 2.09052870375317 94041 | 37.38961 | -122.07715 | MOUNTAIN VIEW | CALIFORNIA | CA | 2.33729808540454 94306 | 37.41478 | -122.12139 | PALO ALTO | CALIFORNIA | CA | 2.35776644118448 94303 | 37.44424 | -122.11736 | PALO ALTO | CALIFORNIA | CA | 2.51480871338068 (7 rows) testdb=# END; COMMIT

Now let’s take a look at the MySQL functions. The calculate_distance function is essentially identical to the PostgreSQL function above.
DELIMITER // CREATE FUNCTION calculate_distance(measurement varchar(2), base_lat double precision, base_lon double precision, lat double precision, lon double precision) RETURNS double precision BEGIN DECLARE earth_radius double precision; IF measurement = 'km' THEN SET earth_radius = 6371.0; ELSEIF measurement = 'mi' THEN SET earth_radius = 3959.0; END IF; RETURN earth_radius * ACOS(SIN(base_lat / 57.2958) * SIN(lat / 57.2958) + COS(base_lat / 57.2958) * COS(lat / 57.2958) * COS((lon / 57.2958) - (base_lon / 57.2958))); END // DELIMITER ;

This function differs slightly from it’s Postgres counterpart. Since MySQL does not currently support functions that return a cursor, we will create a procedure which will execute the same query that we would have returned in the cursor.
DELIMITER // CREATE PROCEDURE zip_proximity(zipcode varchar(5), radius double precision, measurement varchar(2)) BEGIN DECLARE base_lat double precision; DECLARE base_lon double precision; SELECT lat, lon INTO base_lat, base_lon FROM zipcodes WHERE zip = zipcode; SELECT zip, lat, lon, city, state, state_abbrev, calculate_distance(measurement, base_lat, base_lon, lat, lon) AS distance FROM zipcodes WHERE calculate_distance(measurement, base_lat, base_lon, lat, lon) < radius ORDER BY distance; END // DELIMITER ;

Now here is how we would call the procedure, again we are querying for all zip codes within a three mile radius of 94043.
mysql> call zip_proximity('94043', 3, 'mi'); +-------+----------+------------+---------------+------------+--------------+-------------------+ | zip | lat | lon | city | state | state_abbrev | distance | +-------+----------+------------+---------------+------------+--------------+-------------------+ | 94043 | 37.42337 | -122.07981 | MOUNTAIN VIEW | CALIFORNIA | CA | 0 | | 94039 | 37.41884 | -122.09124 | MOUNTAIN VIEW | CALIFORNIA | CA | 0.701004115082249 | | 94035 | 37.41753 | -122.05283 | MOUNTAIN VIEW | CALIFORNIA | CA | 1.53459076784732 | | 94042 | 37.39314 | -122.07827 | MOUNTAIN VIEW | CALIFORNIA | CA | 2.09052870372395 | | 94041 | 37.38961 | -122.07715 | MOUNTAIN VIEW | CALIFORNIA | CA | 2.33729808557031 | | 94306 | 37.41478 | -122.12139 | PALO ALTO | CALIFORNIA | CA | 2.35776644108718 | | 94303 | 37.44424 | -122.11736 | PALO ALTO | CALIFORNIA | CA | 2.51480871282271 | +-------+----------+------------+---------------+------------+--------------+-------------------+ 7 rows in set (0.75 sec) Query OK, 0 rows affected (0.75 sec)

Feel free to leave a comment with any questions or suggestions.

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, will will be using 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.

“This separation allows server and application processes to be restarted independently — an important consideration for busy web sites. It also facilitates per-application security policies — important for ISPs and web hosting companies.”

The lighttpd web server includes the binary spawn-fcgi, which as the name suggests, allows you to spawn FastCGI processes. This can be done independently of the web server processes.

Lighttpd’s FastCGI module, mod_fastcgi is well documented here, but there are a lot of arguments to sift through and it can be a process of trial and error to get a working configuration.

In this example, we will review how to configure lighttpd and spawn-fcgi on separate servers. The web server which will run lighttpd and host all html, images, css, etc. files. The FastCGI server will host all PHP files.

Here is the relevant excerpt from lighttpd.conf on Server A: (the server hosting the html, images, css, etc)
server.modules = ( [ ... ] "mod_fastcgi", [ ... ] ) [ ... ] $HTTP["host"] =~ "(^|\.)iamtgc\.com$" { server.name = "iamtgc.com" server.document-root = "/lighttpd/" + server.name fastcgi.server = ( ".php" => (( "host" => "192.168.25.120", "port" => 9999, "docroot" => "/php/iamtgc.com", "check-local" => "disable" )) ) accesslog.filename = "/var/log/lighttpd/" + server.name + "-access.log" } [ ... ]

On Server B (where the FastCGI processes are running) you start the FastCGI server like this:
# spawn-fcgi -f /usr/local/bin/php-cgi -a 192.168.25.120 -p 9999

Note, the php needs to exist in the docroot reference above, in this case /php/iamtgc.com. The bind address (-a) and port (-p) also correspond to the host and port variables referenced in lighttpd.conf on Server A.

Also note, php-cgi can be replaced with something like dispatch.fcgi in the case of Ruby on Rails or a similar fcgi file for Django as seen here. In both of these cases, other changes would be also required to Server A’s lighttpd.conf.

Unfortunately, and much to my surprise, the custom code did not work flawlessly the first time and I was left staring at errors like:

“Fatal error: Call to undefined function test(); in wp-content/plugins/php-code-widget /execphp.php(37): eval()’d code on line 1″.

Unfortunately, as this error shows up in the dashboard where I would normally edit the widgets content, this is not as straight forward to fix as one would hope. So… after diving into the database and some trial and error I found that the widgets reside in the wp_options table, as this table is quite large, and contains more than just widget information, I had to narrow down what I was looking for. I ended up with this query:

mysql> select option_name from wp_options where option_name like 'widget_%'; +------------------------+ | option_name | +------------------------+ | widget_akismet | | widget_archives | | widget_calendar | | widget_categories | | widget_execphp | | widget_meta | | widget_pages | | widget_recent_comments | | widget_recent_entries | | widget_rss | | widget_tag_cloud | | widget_text | +------------------------+

In this case, it is content in the php-code-widget that is causing our errors, as there is no widget_php-code-widget, it seems that the widget_execphp is the next closest thing. So let’s inspect the widget_execphp entry.

mysql> select * from wp_options where option_name='widget_execphp'; +-----------+---------+----------------+-----------------------------------------------------------------------------------+----------+ | option_id | blog_id | option_name | option_value | autoload | +-----------+---------+----------------+-----------------------------------------------------------------------------------+----------+ | 347 | 0 | widget_execphp | a:1:{i:291302011;a:2:{s:5:"title";s:4:"Test";s:4:"text";s:16:"<?php test();?>";}} | yes | +-----------+---------+----------------+-----------------------------------------------------------------------------------+----------+

Bingo… here we can see the code that was entered into the widget. In this case the test() function does not exist.

The first, and arguably the simplest option is to simply delete the entry, we delete it using the option_id value we obtained above. The option_id value is also used later in the article, in the event you chose to update the entry as opposed to removing it.
mysql> delete from wp_options where option_id=347; Query OK, 1 row affected (0.29 sec)

At this point you can simply go back into the dashboard and recreate your widget, more careful not to include non existent functions this time. But that would be too easy, a more interesting alternative is to modify the existing entry.

mysql> update wp_options set option_value = "a:1:{i:291302011;a:2:{s:5:\"title\";s:4:\"Test\";s:4:\"text\";s:20:\"<?php new_test(); ?>\";}}" where option_id=347; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0

Let’s look at this more closely, the above query updates option_value to:

a:1:{i:291302011;a:2:{s:5:”title”;s:4:”Test”;s:4:”text”;s:20:”<?php new_test(); ?>”;}}

If you notice, two things have changed from the previous option_value value.

The first change is to the string length variable, represented by s, which precedes the string. In our example s:16 becomes s:20. More simply put <?php test(); ?> (16 characters in length) becomes <?php new_test(); ?> (20 characters in length).

The necessary changes is to the string itself, and probably the reason you are getting the undesirable behavior in the first place, is left up to the reader. It is my hope that this sets you in the right direction.