Are ORDER BYs allowed in SQL functions? Or do SQL functions work like
views that are as unordered as their underlting tables?
CREATE FUNCTION foo (INT)
RETURNS SETOF INT AS ' SELECT id FROM table WHERE some_colunm > $1
' LANGUAGE 'sql';
works. But if I try
CREATE FUNCTION foo (INT)
RETURNS SETOF INT AS ' SELECT id FROM table WHERE some_colunm > $1 ORDER BY some_other_colunm
' LANGUAGE 'sql';
the parser comes back with
ERROR: function declared to return int4 returns multiple values in
final retrieve
The sorting must occur in the function, because once I have a set of
IDs, there is no way to order them by some_other_column.
The function cannot be rewritten in pl/pgsql because it may return more
than one value.
--
K. Ari Krupnikov
DBDOM - bridging XML and relational databases
http://www.iter.co.il
