How insert a array into a table in postgresql function?

Question

I received a jsonb in a function and I need insert in distincs rows

{
"client":"12345678",
"documents": ["0ca15b8f-c7b3-4a36-931b-e3faf6eaa571",
            "597c18e5-10d4-4656-a256-108e9674c3aa",
            "68056dfc-9799-4baf-b26b-4336586a5a2e"]
}

Currently I'm testing with:

CREATE OR REPLACE FUNCTION tribal.fn_add_documents(spec jsonb)
 RETURNS json
 LANGUAGE plpgsql
AS $function$
    begin
        INSERT INTO documents (id_document) 
            SELECT * FROM json_array_elements(spec->'documents'::json);
        return ('{"code" : "200"}'::jsonb);
    END;
$function$;

I hope a result like

client	id_document
12345678	0ca15b8f-c7b3-4a36-931b-e3faf6eaa571
12345678	597c18e5-10d4-4656-a256-108e9674c3aa
12345678	68056dfc-9799-4baf-b26b-4336586a5a2e

id_document is a UUID type

Jim Jones · Accepted Answer · 2021-11-22 16:43:56Z

1

Welcome to SO.

I am not sure I got your question right, but if you just want to unnest the jsonb array and repeat the client id just do the following

SELECT 
  spec->>'client' AS client,
  (jsonb_array_elements(spec->'documents')->>0)::uuid AS id_document

Demo: db<>fiddle

WITH j (spec) AS ( VALUES
  ('{"client":"12345678",
  "documents": ["0ca15b8f-c7b3-4a36-931b-e3faf6eaa571",
            "597c18e5-10d4-4656-a256-108e9674c3aa",
            "68056dfc-9799-4baf-b26b-4336586a5a2e"]
  }'::jsonb)
)
SELECT 
  spec->>'client' AS client,
  (jsonb_array_elements(spec->'documents')->>0)::uuid AS id_document
FROM j;

  client  |             id_document              
----------+--------------------------------------
 12345678 | 0ca15b8f-c7b3-4a36-931b-e3faf6eaa571
 12345678 | 597c18e5-10d4-4656-a256-108e9674c3aa
 12345678 | 68056dfc-9799-4baf-b26b-4336586a5a2e

As you can see, there is no need to write a PL/pgSQL function for that, but in case must, this is how it would look like:

CREATE OR REPLACE FUNCTION tribal.fn_add_documents(spec jsonb)
 RETURNS json
 LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO documents (client,id_document) 
  SELECT spec->>'client',
  (jsonb_array_elements(spec->'documents')->>0)::uuid;
RETURN('{"code" : "200"}'::jsonb);
END; $$;

answered Nov 22, 2021 at 16:43

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

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

4 Comments

HendrisVs Over a year ago

I tried with your code and I don't received any error, but data don't save. It's necesary a "confirm"? For instance: in Python after set a "insert into" is necessary a "conn.commit". Thanks!

Jim Jones Over a year ago

@HendrisVs how are you calling the function?

HendrisVs Over a year ago

Sorry for previous question. The code works ok, thanks JIm!!

Jim Jones Over a year ago

@HendrisVs nice it works. If it answers your question, consider accepting the and upvoting the answer, so that other users also get to profit from it. cheers

Collectives™ on Stack Overflow

How insert a array into a table in postgresql function?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related