Returning Composite Types in Postgres Stored Procedures

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.

Here we will create the composite type.

CREATE type ziprowtype as (zip varchar, lat float, lon float, city varchar, state varchar, state_abbrev varchar, distance float);

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

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.