How can i call json parameter In PostgreSQL Procedure-CodePudding

Postgressql - 14 How can i call json parameter In PostgreSQL Stored Procedure when i do so i am getting thease error

-- Table

CREATE TABLE tbl_user (
        pk_user_id BIGSERIAL PRIMARY KEY,

        vhr_name VARCHAR(100) NOT NULL,
        vhr_password VARCHAR(500) NULL,
    
        sin_record_status SMALLINT DEFAULT 1
);

-- PROCEDURE

CREATE OR REPLACE PROCEDURE sp_add_users(arrJsnUsers JSON[]) AS $$
DECLARE
    intSpStatus INT;
BEGIN
    BEGIN
        INSERT INTO tbl_user
            (vhr_name, vhr_password, sin_record_status)
        VALUES
            (arrJsnUsers.strName, arrJsnUsers.strPassword, arrJsnUsers.intRecordStatus);

        COMMIT;
        intSpStatus = 1;
        RAISE INFO '% User Created...', arrJsnUsers.strName;
        
    EXCEPTION WHEN others THEN
        ROLLBACK;
        intSpStatus = -1;
        RAISE EXCEPTION '% User Insertion Faild...', arrJsnUsers.strName;
    END;
END;
$$ LANGUAGE plpgsql;

-- PROCEDURE Call

CALL sp_add_users('[{"strName":"ASD", "strPassword":"asd123", "intRecordStatus":1}, {"strName":"XYZ", "strPassword":"XYZ123", "intRecordStatus":1}]'::JSON[]);

--Error

ERROR:  malformed array literal: "[{"strName":"ASD", "strPassword":"asd123", "intRecordStatus":1}, {"strName":"XYZ", "strPassword":"XYZ123", "intRecordStatus":1}]"
LINE 1: CALL sp_add_users('[{"strName":"ASD", "strPassword":"asd123"...
                      ^
DETAIL:  "[" must introduce explicitly-specified array dimensions.
SQL state: 22P02
Character: 19

CodePudding user response：

You might be confusing Postgres arrays with JSON arrays. If you want to use JSON arrays, the correct data type is json, not json[].

select '[{"foo":"bar"}]'::json

If you really want a Postgres array of JSON objects, the correct way to initialize is:

select array['{"foo":"bar"}']::json[]