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

Try a function like this:

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.  It
clearly does not return multiple values; it has a LIMIT 1.  So I think
this is a bug.  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.

The workaround that I found is to do this in plpgsql.  I can do it
like this:

DECLARE result INT4;
SELECT shoesize INTO result ...
RETURN result;
END;

but I should be able to do this in sql, not plpgsql.

Thanks for any help

On 11 Jan 2001 drevil@sidereal.kz wrote:

> Try a function like this:
>
> CREATE FUNCTION foo(...) RETURNS INT4 AS '
> SELECT shoesize FROM customers ORDER BY time LIMIT 1
> ' LANGUAGE 'sql';

What you describe does sound like a bug to me (maybe in the documentation
though).

But this query should be the same and should return only one result:

SELECT shoesize FROM customers WHERE time = min(time)
--
Tod McQuilin

<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

> > 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.

It's 7.0.3.  Mail me an example of something which works and I'll try
it out.

> Consider DISTINCT ON.  Here's the example given in the SELECT reference
> manual page:

Is that the optimal way to do this?  What the man page described is
exactly what I'm trying to do.

Thanks