Expanding on the PostgreSQL examples in our previous post, here we will look at taking advantage of some of the features in PostgreSQL 8.3 and modifying our zip_proximity function to avoid using cursors and instead define and return a set of our own composite type. From the postgres documentation “A composite type describes the structure of a row or record…”. Since we know that our stored procedure will return a row including: zip code, latitude, longitude, city, state, state abbreviation, and distance, we can create a composite type called, for example, ziprowtype.
Read the rest of this entry »

On most retail and social networking websites (along with many others), you’ll have the capability to search for people, businesses, store locations, etc within a given distance of your location. This can be implemented in a number of ways both mathematically and programmatically. In an attempt to reduce the amount of code I (or others) have to write, be it in PHP, Python, or any number languages that may interface with our database, I have chosen to implement these zip code proximity and distance functions as stored procedures in the database.
Read the rest of this entry »

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.
Read the rest of this entry »