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

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