I want to pass scale value for NUMERIC
datatype dynamically:
DO
$$
DECLARE
v_roundfact INTEGER;
BEGIN
v_roundfact := 3;
CREATE TEMP TABLE TempTable ON COMMIT DROP AS
SELECT BALANCE::Numeric(18,v_roundfact) AS BALANCE --like this
from accountbalance;
END;
$$
but getting error as below...
invalid input syntax for integer: "v_roundfact"
LINE 2: SELECT BALANCE::Numeric(18,v_roundfact) AS BALANCE
QUERY: CREATE TEMP TABLE TempTable ON COMMIT DROP AS
SELECT BALANCE::Numeric(18,v_roundfact) AS BALANCE
from accountbalance
CONTEXT: PL/pgSQL function inline_code_block line 7 at SQL statement
I tested with round()
but the column data type will be numeric without any precision and scale:
BEGIN;--ROLLBACK COMMIT
DO
$$
DECLARE
v_roundfact INTEGER;
BEGIN
v_roundfact := 3;
CREATE TEMP TABLE TempTable ON COMMIT DROP AS
SELECT ROUND(BALANCE,v_roundfact) AS BALANCE
from accountbalance;
END;
$$