PostgreSQL存储过程中如何在INSERT语句通过索引获取数组值？

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

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

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

上述INSERT语句尝试通过数组索引提取值插入表，但写法错误导致无法运行，出错代码行：

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

找到PostgreSQL存储过程INSERT语句中，通过索引获取数组值的有效方法（函数/操作符）。

解决方案 #

错误原因 #

PostgreSQL原生数组不能用->>操作符访问索引，该操作符仅适用于JSON/JSONB类型。同时要注意：PostgreSQL原生数组是1-based索引（从1开始计数），而JSON数组是0-based索引（从0开始）。

方案一：转原生数组后访问 #

将JSON数组转为PostgreSQL原生DOUBLE PRECISION[]类型，用方括号[]访问索引，注意调整索引值：

CREATE OR REPLACE PROCEDURE public.sp_insert_or_update_test(IN jsnData jsonb)
LANGUAGE plpgsql
AS $BODY$
BEGIN
    INSERT INTO public.tbl_test (dbl_amount, dbl_usd_amount)
    VALUES(
        -- 原生数组索引从1开始，对应原JSON数组的第0位
        (CAST(jsnData->>'dbl_amount' AS DOUBLE PRECISION[]))[1],
        (CAST(jsnData->>'dbl_amount' AS DOUBLE PRECISION[]))[2]
    );
END;
$BODY$;

方案二：直接操作JSONB数组（更高效） #

无需转原生数组，直接用JSONB的索引操作符->获取数组元素，再转为目标类型：

CREATE OR REPLACE PROCEDURE public.sp_insert_or_update_test(IN jsnData jsonb)
LANGUAGE plpgsql
AS $BODY$
BEGIN
    INSERT INTO public.tbl_test (dbl_amount, dbl_usd_amount)
    VALUES(
        -- JSON数组索引从0开始，直接取对应元素后转类型
        (jsnData->'dbl_amount'->0)::DOUBLE PRECISION,
        (jsnData->'dbl_amount'->1)::DOUBLE PRECISION
    );
END;
$BODY$;

验证调用 #

执行原调用语句即可正常插入数据：

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

查询验证：

SELECT * FROM public.tbl_test;

会得到结果：

内容的提问来源于stack exchange，提问作者JUNAID M