CODEWITHSUNDEEP
postgresqlplpgsql
FBryant87
FBryant87

Reputation: 4614

Insert into a PostgreSql table from a custom-declared-type array

I create a custom type like so:

CREATE TYPE employee AS (employee_id INTEGER, name VARCHAR(200), age INTEGER);

I then make a function which receives an array of this type as a parameter: 

CREATE FUNCTION insert_office(v_office_id INTEGER, v_office_name VARCHAR(400), v_employees employee[])
RETURNS void
AS $BODY$

INSERT INTO office (office_id, office_name)
VALUES(v_office_id, v_office_name)

--here I need to insert the array of employees (v_employees) into the employees table

$BODY$
LANGUAGE SQL;

Given that the employees table is set up to match the properties of the employee type:

CREATE TABLE employee (employee_id INTEGER, name VARCHAR(200), age INTEGER)

How can I simply transfer this array of type employee to the employee table? Despite several attempts, I can't get the syntax right. (PostgreSql 9.6)

Upvotes: 0

Views: 47

Answers (1)

user330315
user330315

Reputation:

You need to unnest the elements of the array:

insert into employees (employee_id, name, age)
select employee_id, name, age
from unnest(v_employees);

Upvotes: 1

Related Questions