How to get value from array with index in INSERT statement inside postgresql procedure?-CodePudding

CREATE TABLE public.tbl_test 
(
    pk_test_id BIGSERIAL PRIMARY KEY,
    dbl_amount DOUBLE PRECISION,
    dbl_usd_amount DOUBLE PRECISION 
);

Procedure:

CREATE OR REPLACE PROCEDURE public.sp_insert_or_update_test(IN jsnData jsonb)
LANGUAGE plpgsql
AS $BODY$
    DECLARE
    BEGIN
            INSERT INTO public.tbl_test (
                dbl_amount,
                dbl_usd_amount)
             VALUES(
                CAST(jsnData->>'dbl_amount' AS DOUBLE PRECISION[]) ->> 0,  
                CAST(jsnData->>'dbl_amount' AS DOUBLE PRECISION[]) ->> 1
            );

        RETURN;
    END;
$BODY$;

USAGE:

CALL sp_insert_or_update_test('{ "dbl_amount": [1, 2] }'::jsonb);

Issue:

Here in the INSERT statement, I am trying to insert 2 values from array using array-index. But it's not working.

CAST(jsnData->>'dbl_amount' AS DOUBLE PRECISION[]) ->> 0

Expecting a solution (Fn/Operator) to get value from array with index in INSERT statement inside postgresql procedure.

CodePudding user response：

You need to extract the dbl_amount key as a JSONB, not as a text, so the first operator should be ->. And then you need to cast the result of the final ->> operator, not the intermediate JSONB value. And the result should be a double precision not an array of double precision

CAST( (jsnData -> 'dbl_amount' ->> 0) AS DOUBLE PRECISION),