Wrong Calculation by User-Defined SQL-Function

Question

I have a function defined by

CREATE OR REPLACE FUNCTION public.div(dividend INTEGER, divisor INTEGER)
    RETURNS INTEGER
    LANGUAGE 'sql'
    IMMUTABLE
    LEAKPROOF
    STRICT
    SECURITY DEFINER
    PARALLEL SAFE
    AS $BODY$
        SELECT ($1 + $2/2) / $2;
    $BODY$;

It should calculate a commercial rounded result. Most of the times, it does the job. I don't know why, but select div(5, 3) gives me the correct answer while it doesn't when one parameter is calculated by an aggregate, e.g. select div(sum(val), 3) from (select 1 as val UNION SELECT 4) list is sufficient to trigger that. How can I fix div? I don't want to cast every input.

BTW, using SELECT (cast($1 as integer) + cast($2 as integer)/2) / cast($2 as integer); as the definition of div didn't help.

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

Allow floats as parameters, then explicitly cast at the calculation, otherwise you have an implied conversion whilst passing the parameter.

CREATE OR REPLACE FUNCTION my_div(dividend FLOAT, divisor FLOAT)
    RETURNS INTEGER
    LANGUAGE 'sql'
    IMMUTABLE
    -- LEAKPROOF -- not allowed at dbfiddle.uk
    STRICT
    SECURITY DEFINER
    PARALLEL SAFE
    AS $BODY$
        SELECT --($1 + $2/2) / $2;
           (cast($1 as integer) + cast($2 as integer)/2) / cast($2 as integer)
    $BODY$;

✓

select my_div(sum(val), 3) 
from (select 1 as val UNION SELECT 4) x

| my_div |
| -----: |
|      2 |

dbfiddle here

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Nov 18, 2017 at 2:43

Paul Maxwell

35.7k4 gold badges39 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

klin · Accepted Answer · 2017-11-18 00:38:37Z

2

Change the name of the function.

The function div(numeric, numeric) is a builtin Postgres function and there is an ambiguity which function you want to call:

select div(5, 3)           -- calls your function public.div(integer, integer)
select div(5::bigint, 3)   -- calls pg_catalog.div(numeric, numeric)

In the second case the arguments have to be resolved and the system function is chosen as first.

Note that the function sum(integer) gives bigint as a result.

answered Nov 18, 2017 at 0:38

klin

123k15 gold badges240 silver badges262 bronze badges

1 Comment

Bolpat Over a year ago

So awful I didn't think about that. Thank you.

Collectives™ on Stack Overflow

Wrong Calculation by User-Defined SQL-Function

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related