Postgres Return Query bug

Postgres introduced the Return Query syntax in version 8.3, released in 2008. It looks like most hackers didn’t warm to the new and improved syntax for retrieving table rows in set returning functions.   As recently as version 8.4, Return Query is seriously handicapped with a painful bug that may appear when you change your function’s return type:

Structure of query does not match function result type

The only known workaround for this bug:  drop and re-create the return type object.  According to the mailing lists, a patch is available to those with root level db server access and an applicable version of Postgres.

If you’re unable to apply the patch, this bug can seem debilitating.  Many functions that return a set actually return rows from a specific table, so it’s not uncommon to set the return type to a table object.  Obviously, dropping a table isn’t much of an option.   As a workaround, I chose to create a composite type that mirrors the structure of the table’s structure.
Yes, it means that you need to maintain a type for the sole purpose of enabling a Return Query function, but it’s better than any alternatives I tried.  (You could create a separate table via inheritance to serve as your return type, but it’s a very sloppy kind of hack.)

Be on the lookout for this bug in beta versions of 9.0.  And post a comment if you know of a better workaround.

April 23, 2010 | Filed Under Npgsql, Postgres 

Comments

One Response to “Postgres Return Query bug”

  1. Muhammad on August 17th, 2011 10:40 am

    hi there,

    I have tried it with postgresql 9 and can confirm that this bug has fixed.

    As I am using 9, I didn’t try it with 8.4
    Are you sure using type is a successful workaround for it?

    Regards,
    Muhammad

Leave a Reply