Re: plpgsql - sorting result set - Mailing list pgsql-general

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

> On Wed, Aug 20, 2008 at 7:20 PM, Robert Gobeille
> <bob.gobeille@hp.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