pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedNumeric Type Precision Not Respected in Function or Procedure Arguments
3+ messages / 3 participants
[nested] [flat]
* Numeric Type Precision Not Respected in Function or Procedure Arguments
@ 2025-09-22 19:39 Aaron Ackerman <aackerman@goodmorning.com>
0 siblings, 2 replies; 3+ messages in thread
From: Aaron Ackerman @ 2025-09-22 19:39 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
When a NUMERIC type is used as a function or procedure argument, the
value does not actually follow the user-defined precision, such as
NUMERIC(10,4). It does not round off the value to the specified scale
or check for an overflow error. Return type of function also does not
respect precision. This does not apply to the declared variables,
which have the expected behaviour. Tables, custom types, and explicit
casting also all have the expected behaviour for me, rounding the
value off and checking for overflow.
Version: PostgreSQL 16.2 (Ubuntu 16.2-1.pgdg22.04+1) on
x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04)
11.4.0, 64-bit
```
CREATE OR REPLACE FUNCTION numericsTestingFunction(
input NUMERIC(4,3))
RETURNS NUMERIC(4,3)
LANGUAGE plpgsql STABLE
AS $$
BEGIN
RETURN input;
END; $$;
-- Returns 98765.123456789, but expected error
SELECT * FROM numericsTestingFunction(98765.123456789);
-- Returns 5.123456789, but expected 5.123
SELECT * FROM numericsTestingFunction(5.123456789);
CREATE OR REPLACE PROCEDURE numericsTestingProcedure(
input NUMERIC(4,3))
LANGUAGE plpgsql
AS $$
DECLARE declared NUMERIC(4,3);
BEGIN
SELECT input INTO declared;
RAISE NOTICE 'Input value: %, Into declared: %', input, declared;
END; $$;
-- Raises: 'Input value: 5.123456789, Into declared: 5.123'
-- Expected: 'Input value: 5.1234, Into declared: 5.123'
CALL numericsTestingProcedure(5.123456789);
DROP FUNCTION numericsTestingFunction;
DROP PROCEDURE numericsTestingProcedure;
```
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Numeric Type Precision Not Respected in Function or Procedure Arguments
@ 2025-09-22 21:31 Tom Lane <tgl@sss.pgh.pa.us>
parent: Aaron Ackerman <aackerman@goodmorning.com>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2025-09-22 21:31 UTC (permalink / raw)
To: Aaron Ackerman <aackerman@goodmorning.com>; +Cc: pgsql-bugs@lists.postgresql.org
Aaron Ackerman <aackerman@goodmorning.com> writes:
> When a NUMERIC type is used as a function or procedure argument, the
> value does not actually follow the user-defined precision, such as
> NUMERIC(10,4). It does not round off the value to the specified scale
> or check for an overflow error. Return type of function also does not
> respect precision.
This is documented. See for example in [1]:
The full SQL type syntax is allowed for declaring a function's
arguments and return value. However, parenthesized type modifiers
(e.g., the precision field for type numeric) are discarded by
CREATE FUNCTION. Thus for example CREATE FUNCTION foo
(varchar(10)) ... is exactly the same as CREATE FUNCTION foo
(varchar) ....
Perhaps we should reject type modifiers in CREATE FUNCTION,
since people do get confused about this. (The alternative of
actually doing something with them is in aint-gonna-happen
territory, I think.)
regards, tom lane
[1] https://www.postgresql.org/docs/current/sql-createfunction.html
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Numeric Type Precision Not Respected in Function or Procedure Arguments
@ 2025-09-22 21:33 David G. Johnston <david.g.johnston@gmail.com>
parent: Aaron Ackerman <aackerman@goodmorning.com>
1 sibling, 0 replies; 3+ messages in thread
From: David G. Johnston @ 2025-09-22 21:33 UTC (permalink / raw)
To: Aaron Ackerman <aackerman@goodmorning.com>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Monday, September 22, 2025, Aaron Ackerman <aackerman@goodmorning.com>
wrote:
> When a NUMERIC type is used as a function or procedure argument, the
> value does not actually follow the user-defined precision, such as
> NUMERIC(10,4).
>
This is a known limitation - the “typmod” (type modifier) is not stored in
the metadata for a function’s interface.
Pretty sure it’s documented but not able to go hunting for it at the moment.
David J.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-09-22 21:33 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-09-22 19:39 Numeric Type Precision Not Respected in Function or Procedure Arguments Aaron Ackerman <aackerman@goodmorning.com>
2025-09-22 21:31 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-09-22 21:33 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox