Gah, I'm dreadfully sorry. The original functions were cut-and-pasted
from a "\df+" window, which meant they lost their quoted-ness, which
means if you try to cut and paste from my message to a SQL prompt,
you'll be sorely disappointed. Below are the edited versions.
create table rank_of_values(rank_of integer, the_value integer);
CREATE FUNCTION fn_rank_values(TEXT,TEXT)
RETURNS setof rank_of_values AS '
DECLARE
t RECORD;
r rank_of_values%ROWTYPE;
curs REFCURSOR;
col ALIAS FOR $1;
stmt ALIAS FOR $2;
rank INTEGER;
BEGIN
OPEN curs FOR EXECUTE ''SELECT "'' || col ||
''" AS "the_value" '' || stmt;
rank := 1;
LOOP
FETCH curs INTO t;
EXIT WHEN NOT FOUND;
r.rank_of = rank;
r.the_value = t.the_value;
RETURN next r;
rank := 1 + rank;
END LOOP;
CLOSE curs;
RETURN;
END;
' LANGUAGE 'plpgsql' STABLE;
CREATE FUNCTION fn_rank_values(TEXT,TEXT,TEXT)
RETURNS setof rank_of_values AS '
DECLARE
t RECORD;
r rank_of_values%ROWTYPE;
curs REFCURSOR;
col ALIAS FOR $1;
grp ALIAS FOR $2;
clause ALIAS FOR $3;
rank INTEGER;
curr_grp INTEGER;
stmt TEXT;
BEGIN
stmt := ''SELECT "'' || col || ''" AS "the_value", "''
|| grp || ''" AS "the_group" '' || clause;
OPEN curs FOR EXECUTE stmt;
rank := 1;
LOOP
FETCH curs INTO t;
EXIT WHEN NOT FOUND;
IF curr_grp IS NULL
THEN
curr_grp = t.the_group;
ELSIF curr_grp != t.the_group
THEN
curr_grp = t.the_group;
rank = 1;
END IF;
r.rank_of = rank;
r.the_value = t.the_value;
RETURN next r;
rank := 1 + rank;
END LOOP;
CLOSE curs;
RETURN;
END;
' LANGUAGE 'plpgsql' STABLE;
--
Jeff Boes vox 269.226.9550 ext 24
Database Engineer fax 269.349.9076
Nexcerpt, Inc. http://www.nexcerpt.com
...Nexcerpt... Extend your Expertise
