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
thisrule 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/
