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