On Aug 20, 2008, at 7:37 PM, Merlin Moncure wrote:
On Wed, Aug 20, 2008 at 7:20 PM, Robert Gobeille
<bob.gobeille@xxxxxx> 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
