Re: Found a bug in the procedural languages code relating to LIMIT 1

<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