From: "Leif B. Kristensen" <leif@solumslekt.org>
To: pgsql-sql@postgresql.org
Subject: Multiple return values and assignment
Date: Sat, 25 Apr 2009 13:06:44 +0200
User-Agent: KMail/1.9.9
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200904251306.44880.leif@solumslekt.org>

I've got a function that returns both an integer and a string as a 
user-defined composite type int_text:

-- CREATE TYPE int_text AS (number INTEGER, string TEXT);

Basically, the function does some heuristics to extract a sort order 
number from a text, and conditionally modify the text:

CREATE OR REPLACE FUNCTION get_sort(INTEGER, INTEGER, TEXT)
RETURNS int_text AS $$
-- parse text to infer sort order; factored out of add_source() below
-- because the functionality needs to be accessed directly from PHP as
-- well, and this approach eliminates previous duplication of code.
-- CREATE TYPE int_text AS (number INTEGER, string TEXT)
DECLARE
    par_id INTEGER = $1;
    srt INTEGER = $2;
    txt TEXT = $3;
    sort_text int_text;
BEGIN
    -- default condition: if nothing is modified, return input values
    sort_text.number := srt;
    sort_text.string := txt;
    -- 1) use page number for sort order
    -- (low priority, may be overridden)
    IF srt = 1 THEN -- don't apply this rule unless sort = default
        IF txt SIMILAR TO E'%side \\d+%' THEN
            -- use page number as sort order
            SELECT SUBSTR(SUBSTRING(txt, E'side \\d+'), 5,
                LENGTH(SUBSTRING(txt, E'side \\d+')) -4)::INTEGER
            INTO sort_text.number;
        END IF;
    END IF;
    -- 2) use ^#(\d+) for sort order
    IF txt SIMILAR TO E'#\\d+%' THEN
        SELECT SUBSTR(SUBSTRING(txt, E'#\\d+'), 2,
            LENGTH(SUBSTRING(txt, E'#\\d+')) -1)::INTEGER
        INTO sort_text.number;
        -- strip #number from text
        sort_text.string := REGEXP_REPLACE(txt, E'^#\\d+ ', '');
    END IF;
    -- 3) increment from max(sort_order) of source group
    IF txt LIKE '++ %' THEN
        SELECT MAX(sort_order) + 1
            FROM sources
            WHERE get_source_gp(source_id) =
                (SELECT parent_id FROM sources WHERE source_id = par_id)
        INTO sort_text.number;
         -- strip symbol from text
        sort_text.string := REPLACE(txt, '++ ', '');
    END IF;
    RETURN sort_text;
END
$$ LANGUAGE plpgsql STABLE;

To use the two values in an other function where I've declared a 
variable sort_text of type int_text, I do like this:

SELECT number, string FROM get_sort(par_id, srt, txt) INTO sort_text;
srt := sort_text.number;
txt := sort_text.string;

But I feel it's a little awkward. Is there a more elegant way to do it? 
I can't run the get_sort() function twice, because it modifies its 
input values.
-- 
Leif Biberg Kristensen | Registered Linux User #338009
Me And My Database: http://solumslekt.org/blog/