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

Question

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.

Why pass two double values hidden in a JSON value? Why not declare the procedure with two parameters directly? — user330315
– user330315, Commented Nov 9, 2022 at 6:36
In Question example, only provided 1 array. In my real use case, I have about 90 similar amount arrays. — JUNAID M
– JUNAID M, Commented Nov 9, 2022 at 7:07

user330315 · Accepted Answer · 2022-11-09 06:35:52Z

1

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),

answered Nov 9, 2022 at 6:35

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related