Thread: plpgsql - sorting result set
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. Thanks, Bob Gobeille Hewlett Packard Open Source Program Office
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
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
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
On Thu, Aug 21, 2008 at 1:03 AM, Bob Gobeille <bob.gobeille@hp.com> wrote: > 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 >>>> 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. You can always pass the order by clause (or hardcode it) into the execute statement. Also if you are using 8.3 you may want to check out to the new improvements to 'execute'...using. merlin
On Aug 21, 2008, at 6:21 AM, Merlin Moncure wrote: > > You can always pass the order by clause (or hardcode it) into the > execute statement. Also if you are using 8.3 you may want to check > out to the new improvements to 'execute'...using. Hi Merlin, I can't use an order by on the execute, because I'm trying to order the results from multiple executes (the executes are in a loop). For now, I've done the select * from (select * from myfunc()) order by that you originally suggested. Thank you for that. Bob