PLPGSQL function returns ERROR: integer out of range even if value is correct-CodePudding

I'm experiencing strange behaviour on two Postgres functions returning a single bigint value. The first one returns the correct value while the second one throws an error:

ERROR: integer out of range

The only difference is that the first one returns a constant time value in ns, while the second one calculates it based on constant values, for convenience, given in s:

This one works fine:

CREATE OR REPLACE FUNCTION c_getTime1()
RETURNS bigint AS $$
BEGIN
    RETURN 12000000000; --12s in ns
END; $$
LANGUAGE plpgsql IMMUTABLE parallel safe;

This one throws an error.

CREATE OR REPLACE FUNCTION c_getTime2()
RETURNS bigint AS $$
BEGIN
    RETURN 12*1000*1000*1000; --12s in ns
END; $$
LANGUAGE plpgsql IMMUTABLE parallel safe;

Just wondering, is there any big difference? Should I somehow type-cast the second function's return? Both functions are called using direct way:

select c_getTime1();
select c_getTime2();

The Postgresql database version is 13.3.

Thanks in advance for any suggestions.

CodePudding user response：

select pg_typeof(100); return integer.
RETURN 12*1000*1000*1000; will first compute as integer then cast to bigint. https://www.postgresql.org/docs/current/datatype-numeric.html first compute then found out ERROR: 22003: integer out of range so you should change to

RETURN 12 ::bigint * 1000 ::bigint * 1000::bigint * 1000::bigint;