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 function body ?
= = = = = = = = = =
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 function body ?
= = = = = = = = = =
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