postgresql function insert multiple rows which values are represented by array

Question

I have the table:

---------------------------------------
id (integer) | name (character varying)
---------------------------------------

I want to insert multiple rows represented by array of character varying. I have the function code:

create or replace function add_task_state( TEXT ) returns void as $$  
declare
    _str_states character varying[];
begin       
    _str_states = string_to_array($1,',');
    insert into task_states (name) values ???;  
end;
$$
language plpgsql;

How I can do this?

I can use this declaration: create or replace function add_task_state( _states character varying[] ) returns void as $$ — Ir S
– Ir S, Commented Dec 24, 2015 at 8:42

Community · Accepted Answer · 2017-05-23 11:52:04Z

1

I assume that id is auto assigned?

Try this approach:

create or replace function add_task_state( TEXT ) returns void as $$  
begin       
    insert into task_states (name)  (select unnest(string_to_array($1, ','))); 
end;
$$
language plpgsql;

then:

select add_task_state( 'h,c,f' )

will give:

    id name
   ---------
    1   h
    2   c
    3   f

It's very similar to my answer here

edited May 23, 2017 at 11:52

CommunityBot

11 silver badge

answered Dec 24, 2015 at 8:52

Elad

8945 silver badges15 bronze badges

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

1 Comment

Vivek S. Over a year ago

side note : No need to use plpgsql in this case. this is enough

Vivek S. · Accepted Answer · 2020-09-29 09:14:23Z

You can use FOREACH and the function should be

CREATE OR REPLACE FUNCTION add_task_state(TEXT) RETURNS void AS $$
DECLARE
   a text[] := string_to_array($1,',');
   i text;
BEGIN
FOREACH i IN ARRAY a --loop through each element in array `a`
LOOP
  insert into task_states (name) values (i);
END LOOP;
end;
$$ LANGUAGE PLPGSQL;

If your Postgres version is older than 9.1 then you can use

FOR i in 1..array_length(a, 1) LOOP
INSERT INTO task_states (name)
VALUES (a[i]);

END LOOP;

use : select add_task_state('value1,value2,value3')

Collectives™ on Stack Overflow

postgresql function insert multiple rows which values are represented by array

2 Answers 2

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Linked

Related