<drevil@sidereal.kz> writes:
> CREATE FUNCTION foo(...) RETURNS INT4 AS '
> SELECT shoesize FROM customers ORDER BY time LIMIT 1
> ' LANGUAGE 'sql';
> It gives an error that returning multiple values is not allowed.
In what version of Postgres, pray tell? I can't duplicate that behavior
in 7.0.3 nor current.
> Is there any other better way to do this perhaps? Is
> there a way to find a row where some field is the most in its range?
> In my application I'm having to do this a lot.
Consider DISTINCT ON. Here's the example given in the SELECT reference
manual page:
: DISTINCT ON eliminates rows that match on all the specified expressions,
: keeping only the first row of each set of duplicates. The DISTINCT ON
: expressions are interpreted using the same rules as for ORDER BY items;
: see below. Note that "the first row" of each set is unpredictable unless
: ORDER BY is used to ensure that the desired row appears first. For
: example,
:
: SELECT DISTINCT ON (location) location, time, report
: FROM weatherReports
: ORDER BY location, time DESC;
:
: retrieves the most recent weather report for each location. But if we
: had not used ORDER BY to force descending order of time values for each
: location, we'd have gotten a report of unpredictable age for each
: location.
regards, tom lane
