Thread: bug.. ?

A person recent pointed this out to me..

seems a bit funny, because limit 1 pretty much
say's it't not gonna return multiple values.

jeff


> This doesn't work:
>
> CREATE FUNCTION vuln_port(int4) RETURNS int4 AS 'SELECT port FROM
> i_host_vuln WHERE vuln = $1 GROUP BY port ORDER BY count(port) DESC
> LIMIT 1' LANGUAGE 'sql';
>
> The result:
> ERROR: function declared to return int4 returns multiple values in final
> retrieve
>
>
> I'm running 7.0.2.


Jeff MacDonald,

-----------------------------------------------------
PostgreSQL Inc        | Hub.Org Networking Services
jeff@pgsql.com        | jeff@hub.org
www.pgsql.com        | www.hub.org
1-902-542-0713        | 1-902-542-3657
-----------------------------------------------------
Facsimile : 1 902 542 5386
IRC Nick  : bignose
PGP Public Key : http://bignose.hub.org/public.txt

Jeff MacDonald <jeff@hub.org> writes:
> A person recent pointed this out to me..
> seems a bit funny, because limit 1 pretty much
> say's it't not gonna return multiple values.

>> CREATE FUNCTION vuln_port(int4) RETURNS int4 AS 'SELECT port FROM
>> i_host_vuln WHERE vuln = $1 GROUP BY port ORDER BY count(port) DESC
>> LIMIT 1' LANGUAGE 'sql';
>> 
>> The result:
>> ERROR: function declared to return int4 returns multiple values in final
>> retrieve

It's a combination of a bug and a poorly worded error message.  It is
complaining not about rows, but about the extra column needed to do the
ORDER BY.  The error check forgets to ignore this 'hidden' column.

Fixed some time ago for 7.1...
        regards, tom lane