Received: from mail.postgresql.org (webmail.postgresql.org [216.126.85.28]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f1CKbGx56073 for ; Mon, 12 Feb 2001 15:37:16 -0500 (EST) (envelope-from pgsql-sql-owner@postgresql.org) Received: from news.tht.net (news.hub.org [216.126.91.242]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f1A4UZx66463 for ; Fri, 9 Feb 2001 23:30:35 -0500 (EST) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.1/8.11.1) id f1A4FmQ26877 for pgsql-sql@postgresql.org; Fri, 9 Feb 2001 23:15:48 -0500 (EST) (envelope-from news) From: "K. Ari Krupnikov" X-Newsgroups: comp.databases.postgresql.sql Subject: ORDER BY in SQL functions Date: Fri, 09 Feb 2001 20:16:13 -0500 Organization: iter.co.il Lines: 36 Message-ID: <3A84965D.44806521@iln.net> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Complaints-To: scrappy@hub.org X-Mailer: Mozilla 4.72 [en] (X11; U; Linux 2.2.14-5.0 i586) X-Accept-Language: en To: pgsql-sql@postgresql.org X-Archive-Number: 200102/231 X-Sequence-Number: 1422 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