PostgreSQL: Define decimal variable based on another variable

user_12900562 0
Viewed 32

I have a trigger that in the DECLARE section I want to define a Decimal variable based on another integer variable like this:

CREATE OR REPLACE FUNCTION my_function() RETURNS TRIGGER LANGUAGE PLPGSQL AS 
$$
DECLARE
    amount_tick CONSTANT INTEGER := scale(amount_tick::REAL::DECIMAL) FROM general_market WHERE id = NEW.market_id;
    amount_filled DECIMAL(10, amount_tick);
BEGIN
    RETURN NEW;
END;
$$;

but I got this error:

invalid input syntax for type integer: "amount_tick"
LINE X:     amount_filled DECIMAL(10, amount_tick);

how can I define a decimal variable with another integer variable ?

postgresql
1 Answers

There is no point in setting type modifiers on a decimal variable, except to have values rounded properly.

The cause for the error is that you can only use constants as type modifiers

So you either use an explicit cast in dynamic SQL:

EXECUTE format(
           'SELECT CAST(%L AS DECIMAL(10, %s)',
           some_value,
           amount_tick
        ) INTO amount_filled;

or you use round:

amount_filled := round(some_value, amount_tick);
``|
user_64643080
Related