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?
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