postgresql functions

thinc on 2004-11-11T09:00:59

So a couple of years ago I started using postgresql, and really wanted a feature that I had used in MS-SQL, which is stored procedures that returned recordsets.

So I searched and searched trying to find out how to do it.

Turns out it was a new feature in 7.4 (or mabye 7.3) but nobody knew how to do it. So I kept searcing and finally found a newsgroup post by Tom Lane (I think) that halfway described it. This news post, combined with knowledge gained from my previous searches and investigations yeilded the solution I was after.

So, since I haven't seen it anywhere else (and it's not doing me any good not sharing it), here is the full text for a function (aka stored procedure) that returns a recordset.

DROP TYPE get_contacts_return CASCADE;

CREATE TYPE get_contacts_return AS
(
      "first_name"      varchar(50)
    , "middle_name"     varchar(50)
    , "last_name"       varchar(50)
    , "nickname"        varchar(50)
    , "contact_id"      bigint
    , "title"           varchar(20)
    , "address_city"    varchar(20)
);

CREATE FUNCTION get_contacts(
      integer       --contact_type_id
 )
RETURNS SETOF get_contacts_return AS
'
    DECLARE

        in_contact_type_id      ALIAS FOR $1;

        rec                  get_contacts_return%rowtype;

    BEGIN
        --===================================================--
        --error checking
        -------------------------------------------------------
        IF( in_contact_type_id IS NULL ) THEN
            RAISE EXCEPTION ''Invalid contact_type_id: % '' , in_contact_type_id;
        END IF;

        --===================================================--
        --query
        -------------------------------------------------------

        FOR rec IN
            SELECT DISTINCT ON ( contact.first_name , contact.id )
                  contact.first_name  AS first_name
                , contact.middle_name AS middle_name
                , contact.last_name   AS last_name
                , contact.nickname    AS nickname
                , contact.id          AS contact_id
                , contact.title       AS title
                , address.city        AS address_city
            FROM contact
                JOIN address ON
                    contact.id = address.fk_contact_id
            WHERE
                contact.display = true
            AND
                contact.contact_type_id = in_contact_type_id
            ORDER BY contact.first_name , contact.id
        LOOP
            RETURN NEXT rec;
        END LOOP;

        RETURN null;

    END;

 ' LANGUAGE 'plpgsql';

Note: The select statement is not complete, I have removed some of the where clause.

It boils down to this: You create a new "recordset" return type, populate it with your select statement, then return it.

I have no idea if it is faster, uses less memory or if it is any better at all than calling straight sql from dbi, I just thought it was cool and wanted to try it. And They ave always told me that stored procedures are faster and "more efficient" than just sending queries to the database.

Oh, to call it, use this:

SELECT
      "first_name"
    , "middle_name"
    , "last_name"
    , "nickname"
    , "contact_id"
    , "title"
    , "address_city"
FROM get_contacts( "contact_type_id" );

---

Uses

Matts on 2004-11-11T15:25:36

One of the main ways to use this in MS-SQL is to create a temp table, insert a bunch of things into it (in a way that you couldn't easily do with one statement) and then return the recordset of the temp table.

Unfortunately temp tables have long been broken in Pg functions, because the function caches the table-id even for temp tables (it can't distinguish between temp and non-temp tables) and tries to re-use the old table-id next time around. Of course things break then because the table doesn't exist any more!

I keep hoping this will be fixed. I haven't tried the most recent version (8?) of Pg though.

TechDocs Article

Theory on 2004-11-11T18:57:01

Someone on IRC pointed out to me that there's a TechDocs article, Set Returning Functions that seems to cover the issue pretty well.

--David