1

The following function returns two generic numerics, not the numeric(20,10) I think I'm specifying in the function return definition. This is unexpected (by me). Is there a way to ensure the function does the "coercion" to numeric(20,10) so outside code doesn't have to cast?

drop function test_numeric;
CREATE OR REPLACE FUNCTION test_numeric ()
RETURNS TABLE(m numeric(20,10), n numeric(20,10)) AS $$
-- RETURNS TABLE(m numeric, n numeric) AS $$
BEGIN
    RETURN QUERY
    select
        -- returned as written (with more than 10 decimal places)
        88888888.88888888888888888 as mm,
        -- truncated as expected
        88888888.88888888888888888::numeric(20,10) as nn;
END;
$$ LANGUAGE plpgsql;

[Edit based on feedback in answers] Here's a fiddle to show how the function signature does not enforce the return type (hoped for). M is the point. Even though the signature says we're returning a numeric(20,10) a generic numeric gets returned. The problem is when subsequent code expects a numeric(20,10) but the type is numeric (comes in when doing a create or update view/table etc). What's most unexpected is that the function signature basically gets ignored, or overridden by the code, to the next user down the road sees the function signature, expects a 20/10 and gets something totally different.

Fiddle

Another fiddle showing it doesn't matter at all what scale or precision is specified to be returned; any numeric scale/precision can be returned and will remain the scale or precision it was inside the function body.

Fiddle

[Further Edit] Apologies for beating the horse and demonstrating my misunderstanding of the Numeric type perhaps. But if the scale and precision matters when changing this view, it's hard to understand why it wouldn't matter when defining the table the function will return. I could maybe understand if numeric(20,10) was a subtype of numeric and could be passed if I'd specified numeric to be returned. Just never heard of something less specific passing for something specified to be more specific (in a strongly typed language).

Fiddle

2 Answers 2

1

RETURNS TABLE(m numeric(20,10), n numeric(20,10)) does not enforce the data types of the returned columns; it's simply a declaration, metadata intended to inform the callers what the function should return. The declaration cannot not require anything, it's a "promise" that is to be fulfilled by the function implementation. The function code itself is responsible for what it returns. In other words, you must use explicit type casts, as necessary, in the function body.

If the function doesn't fulfil the promise, it's an implementation bug.


P.S. Looking at the source code of coerce_type, it simply compares type OIDs, so any numeric is compatible with any other numeric:


    if (targetTypeId == inputTypeId ||
        node == NULL)
    {
        /* no conversion needed */
        return node;
    }
0
0

You have to set the numeric digits, to the wanted result, as log as you write only numeric, you will get only this, a postgre will not mind as he can convert it

CREATE OR REPLACE FUNCTION test_numeric ()
-- RETURNS TABLE(m numeric(20,10), n numeric(20,10)) AS $$
RETURNS TABLE(m numeric, n numeric(20,10)) AS $$
BEGIN
    RETURN QUERY
    select
        -- returned as written (with more than 10 decimal places)
        88888888.88888888888888888 as mm,
        -- truncated as expected
        88888888.88888888888888888::numeric(20,10) as nn;
END;
$$ LANGUAGE plpgsql;
create or replace view v_test as
select
    9.9::numeric(20,10) as x,
    n as y
from test_numeric();
SELECT * FROM v_test
           x |                   y
-----------: | ------------------:
9.9000000000 | 88888888.8888888889

db<>fiddle here

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.