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. — user330315
– user330315, Commented Jun 21, 2014 at 18:47
Can you give me an example to insert multiple rows using a function in PostgreSQL — nahrun
– nahrun, Commented Jun 21, 2014 at 19:22

Community · Accepted Answer · 2020-06-20 09:12:55Z

13

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

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jun 22, 2014 at 11:15

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

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

2 Comments

hooblei Over a year ago

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 Over a year ago

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

Collectives™ on Stack Overflow

How to insert multiple rows using a function in PostgreSQL

1 Answer 1

Postgres 9.4 or later ...

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Postgres 9.4 or later ...

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related