Thread: order by, within a plpgsql fx

Please consider this plpgsql function:
= = = = = = = = = =
CREATE Or Replace FUNCTION fx_order_by ( )
RETURNS table( last_name text, first_name )
AS $eofx$
DECLARE
--
BEGIN

  Return Query
  select
    lname, fname
  from
    my_table
  order by
    lname  ASC
  ;

END;
$eofx$ LANGUAGE plpgsql;
= = = = = = = = = =

So, is this select statement's result set  guaranteed to be ordered as specified by the [order by] coded within the
functionbody ? 
= = = = = = = = = =
select last_name, first_name from fx_order_by() ;
= = = = = = = = = =

Or, must I code another [order by] to be sure ?
= = = = = = = = = =
select last_name, first_name from fx_order_by()   order by last_name ;
= = = = = = = = = =

Also, is the answer the same for a "sql" function ?

Thanks,
-dvs-

<david.sahagian@emc.com> writes:
> Please consider this plpgsql function:
> = = = = = = = = = =
> CREATE Or Replace FUNCTION fx_order_by ( )
> RETURNS table( last_name text, first_name )
> AS $eofx$
> DECLARE
> --
> BEGIN

>   Return Query
>   select
>     lname, fname
>   from
>     my_table
>   order by
>     lname  ASC
>   ;

> END;
> $eofx$ LANGUAGE plpgsql;
> = = = = = = = = = =

> So, is this select statement's result set  guaranteed to be ordered as specified by the [order by] coded within the
functionbody ? 
> = = = = = = = = = =
> select last_name, first_name from fx_order_by() ;
> = = = = = = = = = =

Well, it's not "guaranteed" exactly, but as long as the outer query
doesn't have any occasion to do anything but seqscan the function
result, you'd get the rows in that order.  If you were to throw in
DISTINCT, or GROUP BY, or a JOIN to something else, etc, then maybe
not.

            regards, tom lane