Postgres Pro
Downloads
Home   >   mailing lists

Thread: ORDER BY in SQL functions

ORDER BY in SQL functions

From
"K. Ari Krupnikov"
Date:
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

Re: ORDER BY in SQL functions

From
Tom Lane
Date:
"K. Ari Krupnikov" <ari@iln.net> writes:
> CREATE FUNCTION foo (INT)
> RETURNS SETOF INT AS '
>     SELECT id
>     FROM   table
>     WHERE  some_colunm > $1
>     ORDER BY some_other_colunm
> ' LANGUAGE 'sql';

> ERROR:  function declared to return int4 returns multiple values in
> final retrieve

This is a bug in the SQL-function support --- the check for correct
return type gets confused by the extra hidden column used for the
ORDER BY.  It'll work if you ORDER BY the column you're returning,
not that that helps you much.

It's fixed in 7.1.
        regards, tom lane

Re: ORDER BY in SQL functions

From
"K. Ari Krupnikov"
Date:
Tom Lane wrote:
> 
> "K. Ari Krupnikov" <ari@iln.net> writes:
> > CREATE FUNCTION foo (INT)
> > RETURNS SETOF INT AS '
> >     SELECT id
> >     FROM   table
> >     WHERE  some_colunm > $1
> >     ORDER BY some_other_colunm
> > ' LANGUAGE 'sql';
> 
> > ERROR:  function declared to return int4 returns multiple values in
> > final retrieve
> 
> This is a bug in the SQL-function support --- the check for correct
> return type gets confused by the extra hidden column used for the
> ORDER BY.  It'll work if you ORDER BY the column you're returning,
> not that that helps you much.

What if the function is declared to retun a complex type, can I then
sort it?
I it works, this function can be wrapped in another function that simply
returns this single column.

Also, why did I get this message after posting to
comp.databases.postgresql.sql? Do articles in that group automatically
get posted to the mailing list?

> Subject: Stalled post to pgsql-sql
> 
> Your message to pgsql-sql has been delayed
> pending approval of the list owner for
> the following reason(s):
> 
> Non-Member Submission from "K. Ari Krupnikov" <ari@iln.net>


-- 
K. Ari Krupnikov

DBDOM - bridging XML and relational databases
http://www.iter.co.il