Here is the fix:
$ cvs diff -ur 1.171 sp.txt Index: sp.txt =================================================================== RCS file: /home/repositories/freshports-1/database-schema/sp.txt,v retrieving revision 1.171 retrieving revision 1.172 diff -u -r1.171 -r1.172 --- sp.txt 24 Sep 2011 19:28:29 -0000 1.171 +++ sp.txt 26 Jun 2012 12:29:25 -0000 1.172 @@ -2662,19 +2662,16 @@ IF l_VulnCount > 0 THEN - SELECT count(distinct vuxml_id) INTO l_VulnCurrent - FROM commit_log_ports_vuxml CLPV, commit_log_ports CLP, ports P, ports_vulnerable PV + FROM commit_log_ports_vuxml CLPV, commit_log_ports CLP, ports P WHERE CLP.commit_log_id = CLPV.commit_log_id AND CLPV.port_id = CLP.port_id AND P.id = CLP.port_id AND P.version = CLP.port_version AND P.revision = CLP.port_revision AND CLP.port_epoch = P.portepoch - AND PV.port_id = P.id - AND P.id = p_PortID - GROUP BY P.id; + AND P.id = p_PortID; IF l_VulnCurrent IS NULL THEN l_VulnCurrent := 0;
The solution: do not use the ports_vulnerable table. This is the very table we are populating with this function. It will not contain any useful information.
At the same time, remove the GROUP BY because it is not needed. This last item was pointed out by hhaamu. It was also hhaamu who responded to my request for people to write in with what they thought was the problem.
Well done. Thank you.