iamtgc

Returning Composite Types in Postgres Stored Procedures

October 1st, 2009 by tgc

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 »

Posted in Postgres | No Comments »


  

Implementing Zip Code Proximity Functions in MySQL and PostgreSQL

January 14th, 2009 by tgc

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 »

Posted in MySQL, Postgres | No Comments »

Exploring Stored Procedures in MySQL and PostgreSQL

December 19th, 2008 by tgc

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 »

Posted in MySQL, Postgres | No Comments »

« Previous Entries