How to insert multiple rows using a function in PostgreSQL

Question

I want to insert more than one row in a table with function in PostgreSQL.

This is my table

CREATE TABLE mahasiswa
(
  nim CHAR(10),
  nama VACHAR(40)
  CONSTRAINT pk_nim PRIMARY KEY (nim)
)
;

and this is the function I created

CREATE FUNCTION insertdata(CHAR(10),varchar(40)) 
       RETURNS VOID AS 
       $$ 
         INSERT INTO mahasiswa VALUES ($1,$2); 
       $$ 
       LANGUAGE 'sql';

When I call the function like this

SELECT insertdata ('1234567890','Nahrun'),
('0987654321','Hartono');

only one row is inserted.

How can I modify my function to insert more than one row at a time?

That's not possible. First because your function only has two parameters and secondly because your `INSERT` statement is written in such a way that it only inserts a single row. — , Jun 21 '14 at 18:47
Can you give me an example to insert multiple rows using a function in PostgreSQL — nahrun, Jun 21 '14 at 19:22

score 13 · Accepted Answer · edited Jun 20 '20 at 09:12

The function you have should rather be:

CREATE FUNCTION insertdata(varchar(10),varchar(40)) 
  RETURNS VOID AS 
$$ 
   INSERT INTO mahasiswa(col_name1, col_name2)
   VALUES ($1,$2); 
$$ 
LANGUAGE sql STRICT;

Don't quote the language name. It's an identifier.
Always provide a target list with persisted statements. Else, if you later change the table definition, the function can behave in unexpected ways.
Never use char(n), unless you know what you are doing. I'd just use text.

To insert multiple rows, you can take an array of composite type or two arrays with the same number of elements to unnest in parallel. Demonstrating the latter:

CREATE FUNCTION insertdata(_arr1 text[], _arr2 text[]) 
  RETURNS VOID AS 
$$ 
   INSERT INTO mahasiswa(col_name1, col_name2)
   SELECT unnest(_arr1), unnest(_arr2);
$$ 
LANGUAGE sql STRICT;

Call:

SELECT insertdata ('{1234567890,0987654321}', '{Nahrun,Hartono}');

I would rather use a plpgsql function and check that the number of elements is the same in both arrays to prevent mistakes. Use array_length(arr1, 1) ...

Postgres 9.4 or later ...

... introduced a new variant of unnest that accepts multiple arrays in parallel - without the quirks of the above hack (never defaults to a CROSS JOIN)

   INSERT INTO mahasiswa(col_name1, col_name2)
   SELECT * FROM unnest(_arr1, _arr2);  -- must be in FROM list

Unnest multiple arrays in parallel

Note: In the last PG 9.4 INSERT example when used with multiple arrays, a `SELECT * FROM unnest(...` is required or pg complains with an unmatched function error. — hooblei, Jan 06 '16 at 15:07
@hooblei: Thanks, fixed. The explanation is in the appended link, I still had the syntax error. — Erwin Brandstetter, Jan 06 '16 at 20:48

How to insert multiple rows using a function in PostgreSQL

1 Answers1

Postgres 9.4 or later ...

Linked