Ranking values within a query (pseudo-ROWNUM) **fixed**

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