Re: plpgsql - sorting result set

On Aug 20, 2008, at 7:37 PM, Merlin Moncure wrote:

> On Wed, Aug 20, 2008 at 7:20 PM, Robert Gobeille
> <bob(dot)gobeille(at)hp(dot)com> wrote:
>> Is it possible to sort a result set in plpgsql?
>>
>> That is, after building up the result set with RETURN NEXT from
>> multiple
>> queries, I'd like to sort the set before returning.
>>
>> I'm still using 8.1 if that is an issue.
>
> Have you already ruled out:
>
> select * from (select * from your_func()) order by something?
>
> if so, why?
> merlin

I've ruled this out because I do multiple queries. Here is my
function. I want to reorder the result set (output table) before
returning.

CREATE or REPLACE function uploadtree2path(uploadtree_pk_in int)
returns setof uploadtree as $$
DECLARE
UTrec uploadtree;
UTpk integer;
sql varchar;
BEGIN

UTpk := uploadtree_pk_in;

WHILE UTpk > 0 LOOP
sql := 'select * from uploadtree where uploadtree_pk=' || UTpk;
execute sql into UTrec;

IF ((UTrec.ufile_mode & (1<<28)) = 0) THEN RETURN NEXT UTrec;
END IF;
UTpk := UTrec.parent;
END LOOP;
RETURN;
END;
$$
LANGUAGE plpgsql;

Thanks,
Bob