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?

Solution

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

Comments

[hooblei]

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.

[Erwin Brandstetter]

@hooblei: Thanks, fixed. The explanation is in the appended link, I still had the syntax error.