Postgresql函数应在阈值内返回

Question

如果函数中的计算结果为0(或NULL(，我想要一个返回0的函数。如果结果大于0且低于0.1，则函数应返回0.1，否则结果应仅为计算结果。非常感谢您的帮助。我有这个功能：

DECLARE output REAL;
BEGIN
select 
case 
when (select count(caa1.id) from classified_author_assoc caa1 where caa1.publication_id = p.id and caa1.remote_origin = 'TRUE') > 0 and p.peer_review = 'TRUE' then /* Samarbejde */
case 
when p.type_classification_id in (57360320, 57360322, 57360324, 57360326) /* Tidsskriftsartikel, Konferenceartikel i tidsskrift, Letter, Review */ then 1*1.25*((select count(caa1.id) from classified_author_assoc caa1 where caa1.publication_id = p.id and caa1.remote_origin = 'FALSE')::real / (select count(caa2.id) from classified_author_assoc caa2 where caa2.publication_id = p.id)) 
when p.type_classification_id in (57360348, 57360352, 57360350) /* Bidrag til bog, Bidrag til rapport, Konferenceartikel i proceeding */
then 
case 
when bs.id is null then 0.5*1.25*((select count(caa1.id) from classified_author_assoc caa1 where caa1.publication_id = p.id and caa1.remote_origin = 'FALSE')::real / (select count(caa2.id) from classified_author_assoc caa2 where caa2.publication_id = p.id))
when bs.id is not null then 1*1.25*((select count(caa1.id) from classified_author_assoc caa1 where caa1.publication_id = p.id and caa1.remote_origin = 'FALSE')::real / (select count(caa2.id) from classified_author_assoc caa2 where caa2.publication_id = p.id))                                         
end
when p.type_classification_id in (57360336, 57360340) /* Bog, Rapport */ then 5*1.25*((select count(caa1.id) from classified_author_assoc caa1 where caa1.publication_id = p.id and caa1.remote_origin = 'FALSE')::real / (select count(caa2.id) from classified_author_assoc caa2 where caa2.publication_id = p.id))
when p.type_classification_id in (57360338, 57360330) /*Antologi, Leder*/ then 0.5*1.25*((select count(caa1.id) from classified_author_assoc caa1 where caa1.publication_id = p.id and caa1.remote_origin = 'FALSE')::real / (select count(caa2.id) from classified_author_assoc caa2 where caa2.publication_id = p.id))
end
when (select count(caa1.id) from classified_author_assoc caa1 where caa1.publication_id = p.id and caa1.remote_origin = 'TRUE') > 0 and p.peer_review is null then /* Samarbejde */
case
when p.type_classification_id in (57360342) /* Doktorafhandling */ then 5*1.25*((select count(caa1.id) from classified_author_assoc caa1 where caa1.publication_id = p.id and caa1.remote_origin = 'FALSE')::real / (select count(caa2.id) from classified_author_assoc caa2 where caa2.publication_id = p.id))
when p.type_classification_id in (57360390) /* Patent */ then 2*1.25*((select count(caa1.id) from classified_author_assoc caa1 where caa1.publication_id = p.id and caa1.remote_origin = 'FALSE')::real / (select count(caa2.id) from classified_author_assoc caa2 where caa2.publication_id = p.id))
when p.type_classification_id in (415542856) /* Preprint */ then 0.5*1.25*((select count(caa1.id) from classified_author_assoc caa1 where caa1.publication_id = p.id and caa1.remote_origin = 'FALSE')::real / (select count(caa2.id) from classified_author_assoc caa2 where caa2.publication_id = p.id))
end
when (select count(caa1.id) from classified_author_assoc caa1 where caa1.publication_id = p.id and caa1.remote_origin = 'TRUE') = 0 and p.peer_review = 'TRUE' then /*Ikke Samarbejde */
case 
when p.type_classification_id in (57360320, 57360322, 57360324, 57360326) /* Tidsskriftsartikel, Konferenceartikel i tidsskrift, Letter, Review */ then 1*((select count(caa1.id) from classified_author_assoc caa1 where caa1.publication_id = p.id and caa1.remote_origin = 'FALSE')::real / (select count(caa2.id) from classified_author_assoc caa2 where caa2.publication_id = p.id))
when p.type_classification_id in (57360348, 57360352, 57360350) /* Bidrag til bog, Bidrag til rapport, Konferenceartikel i proceeding */
then 
case 
when bs.id is null then 0.5*((select count(caa1.id) from classified_author_assoc caa1 where caa1.publication_id = p.id and caa1.remote_origin = 'FALSE')::real / (select count(caa2.id) from classified_author_assoc caa2 where caa2.publication_id = p.id))
when bs.id is not null then 1*((select count(caa1.id) from classified_author_assoc caa1 where caa1.publication_id = p.id and caa1.remote_origin = 'FALSE')::real / (select count(caa2.id) from classified_author_assoc caa2 where caa2.publication_id = p.id))                                          
end
when p.type_classification_id in (57360336, 57360340) /* Bog, Rapport */ then 5*((select count(caa1.id) from classified_author_assoc caa1 where caa1.publication_id = p.id and caa1.remote_origin = 'FALSE')::real / (select count(caa2.id) from classified_author_assoc caa2 where caa2.publication_id = p.id))
when p.type_classification_id in (57360338, 57360330) /*Antologi, Leder*/ then 0.5*((select count(caa1.id) from classified_author_assoc caa1 where caa1.publication_id = p.id and caa1.remote_origin = 'FALSE')::real / (select count(caa2.id) from classified_author_assoc caa2 where caa2.publication_id = p.id))
end
when (select count(caa1.id) from classified_author_assoc caa1 where caa1.publication_id = p.id and caa1.remote_origin = 'TRUE') = 0 and p.peer_review is null then /*Ikke Samarbejde */
case
when p.type_classification_id in (57360342) /* Doktorafhandling */ then 5*((select count(caa1.id) from classified_author_assoc caa1 where caa1.publication_id = p.id and caa1.remote_origin = 'FALSE')::real / (select count(caa2.id) from classified_author_assoc caa2 where caa2.publication_id = p.id))
when p.type_classification_id in (57360390) /* Patent */ then 2*((select count(caa1.id) from classified_author_assoc caa1 where caa1.publication_id = p.id and caa1.remote_origin = 'FALSE')::real / (select count(caa2.id) from classified_author_assoc caa2 where caa2.publication_id = p.id))
when p.type_classification_id in (415542856) /* Preprint */ then 0.5*((select count(caa1.id) from classified_author_assoc caa1 where caa1.publication_id = p.id and caa1.remote_origin = 'FALSE')::real / (select count(caa2.id) from classified_author_assoc caa2 where caa2.publication_id = p.id))
end
else
0
end
from 
publication p join publication_status ps on (p.id = ps.publication_id) left join book_series bs on (bs.publication_id = p.id)
where p.id = $1 
INTO output;
RETURN output;       
END;

Answer 1

除非我误解了你，否则你只需要把RETURN output;的最后一行改为：

return case
when output is null or output = 0 then 0
when output > 0 and output < 0.1 then 0.1
else output end;

我确实注意到了，但是你没有考虑负值。在上面的语句中，它将按原样返回任何负数。