0

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;
$$
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.