Re: plpgsql - sorting result set

On Aug 20, 2008, at 10:11 PM, Gobeille, Robert wrote:

> 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;

Merlin,
I just reread what you wrote.  ;-)   Yes, your select * from (select *
from your_func) would work.  The function caller itself can sort the
results (outside of postgres).  I could also have a second function
call the above, sorting the results.  These just seem kludgy.   That's
why I was wondering if it were possible to  select * from (select *
from function_return_set) order by.  But I see no way to reference the
table to be returned.

Thanks,
Bob