pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedORDER BY in SQL functions
3+ messages / 2 participants
[nested] [flat]
* ORDER BY in SQL functions
@ 2001-02-10 01:16 K. Ari Krupnikov <ari@iln.net>
0 siblings, 1 reply; 3+ messages in thread
From: K. Ari Krupnikov @ 2001-02-10 01:16 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: ORDER BY in SQL functions
@ 2001-02-12 21:17 Tom Lane <tgl@sss.pgh.pa.us>
parent: K. Ari Krupnikov <ari@iln.net>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2001-02-12 21:17 UTC (permalink / raw)
To: K. Ari Krupnikov <ari@iln.net>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: ORDER BY in SQL functions
@ 2001-02-13 00:36 K. Ari Krupnikov <ari@iln.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: K. Ari Krupnikov @ 2001-02-13 00:36 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2001-02-13 00:36 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-02-10 01:16 ORDER BY in SQL functions K. Ari Krupnikov <ari@iln.net>
2001-02-12 21:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-02-13 00:36 ` K. Ari Krupnikov <ari@iln.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox