Postgresql function should return within threshhold-CodePudding

I would like a function which returns 0 if the result of the calculation in the function is 0 (or NULL). If the result is larger than 0 and below 0.1 then function should return 0.1 - otherwise the result should just be the result of the calculation. Help is very much appreciated. I have this function:

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;

CodePudding user response：

When you create the function, use lang name 'plpgsql'

https://www.postgresql.org/docs/current/sql-createfunction.html

Then user a conditional to check the value in your output variable:

https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-CONDITIONALS

Returning from a function is covered here:

https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING

CodePudding user response：

Unless I misunderstand you, you just need to change your final line that says RETURN output; to this:

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

I did, however notice you did not account for negative values. In the statement above, it will return any negative as-is.