Calling function passing JSONB as parameter - Postgres

Question

Below is the function which I have written

     CREATE OR REPLACE FUNCTION upd.insert_testdata(spec jsonb[])
     RETURNS void
     LANGUAGE 'plpgsql' AS $BODY$
            
     BEGIN
    --Consider all columns in specialist table as character varying and code column as integer.
        insert into upd.specialist (to_cd, empid, code, booking_status, availability)
           select j.spec->>'to_cd', 
                  j.spec->>'empid', 
                  j.spec->>'code', 
                  j.spec->>'booking_status',
                  j.spec->>'availability' 
           from jsonb_populate_record(spec) j;
        
                
     END;
     $BODY$;

I am trying to call function by following command

    SELECT upd.insert_testdata(
               '{
                "to_cd":"NFG",
                "empid":"test",
                "code":123,
                "booking_status":"Y", 
                "availability":"MTWTFSS"
                 }'::jsonb[]
             );

But I am getting error as Malformed array Literal

DETAIL: Unexpected array element. SQL state: 22P02

Also I would like to know how to insert multiple records/pass multiple rows in single json variable

score 1 · Accepted Answer · 2020-08-03 15:25:32Z

1

Don't pass an array of json values, pass a json array:

CREATE OR REPLACE FUNCTION upd.insert_testdata( spec jsonb)
     RETURNS void
LANGUAGE plpgsql
AS $BODY$
begin
  --Consider all columns in specialist table as character varying and code column as integer.
  insert into upd.specialist (to_cd, empid, code, booking_status, availability)
  select j.spec->>'to_cd', 
         j.spec->>'empid', 
         (j.spec->>'code')::int, 
         j.spec->>'booking_status', 
         j.spec->>'availability' 
  from jsonb_array_elements(spec) as j(spec);
end;
$BODY$;

If you don't want to list all keys manually, and if you are 100% certain that the key names always match the column names in the table, you can simplify this a bit using jsonb_populate_record

CREATE OR REPLACE FUNCTION upd.insert_testdata( spec jsonb)
     RETURNS void
LANGUAGE plpgsql
AS $BODY$
begin
  --Consider all columns in specialist table as character varying and code column as integer.
  insert into upd.specialist (to_cd, empid, code, booking_status, availability)
  select (jsonb_populate_record(null::specialist, j.spec)).*
  from jsonb_array_elements(spec) as j(spec);
end;
$BODY$;

Then use it like this:

select upd.insert_testdata('[
                              {"to_cd":"NFG",
                               "empid":"test",
                               "code":123,
                               "booking_status": "Y",
                               "availability":"MTWTFSS"}
                            ]'::jsonb);

If you want to pass multiple elements you can use it like this:

select upd.insert_testdata('[
                              {"to_cd":"NFG",
                               "empid":"test",
                               "code":123,
                               "booking_status": "Y",
                               "availability":"MTWTFSS"},
                              {"to_cd":"CFG",
                               "empid":"test2",
                               "code": 456,
                               "booking_status": "N",
                               "availability":"MT"}
                            ]'::jsonb);

Online example

edited Aug 3, 2020 at 15:25

answered Aug 3, 2020 at 14:52

user330315

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

4 Comments

Nik Over a year ago

ERROR: invalid input syntax for type json LINE 2: ('[ ^ DETAIL: Expected ":", but found ",".

Nik Over a year ago

I am using postgres v9.6

user330315 Over a year ago

Somewhere in your JSON, you didn't use : to separate the key and the value. The code certainly works

Nik Over a year ago

Yes, it's working and last one question shouldn't jsonb_populate_record would have been more suited in this situation...???

Abdusoli · Accepted Answer · 2020-08-03 14:55:47Z

0

You don't really have to send and receive as jsonb. You can send as text and convert to jsonb object inside your function.

CREATE OR REPLACE FUNCTION upd.insert_testdata(spec text)
RETURNS void LANGUAGE 'plpgsql'
AS $BODY$
declare
  your_json jsonb;
begin
  your_json := spec::jsonb;

And you send jsonb as text.

select upd.insert_testdata(
    '{
        "to_cd":"NFG",
        "empid":"test",
        "code":123,
        "booking_status":"Y", 
        "availability":"MTWTFSS"
     }'
)

edited Aug 3, 2020 at 14:55

answered Aug 3, 2020 at 14:48

Abdusoli

6591 gold badge10 silver badges29 bronze badges

1 Comment

Nik Over a year ago

My basic purpose is inserting data in table. How will this insert data into table?

Collectives™ on Stack Overflow

Calling function passing JSONB as parameter - Postgres

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related