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

Question

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)

Unrelated but: storing the age of a person is really bad design. You should store the date of birth - the age can easily be calculated from that. — user330315
– user330315, Commented Sep 18, 2018 at 11:59
Also: if you create a table employee you automatically have a type with the same name. There is no need to create a separate type. You can use a table's type as a parameter type in Postgres — user330315
– user330315, Commented Sep 18, 2018 at 12:00
Oh really so I could just delete the type (which has the same name as the table) and all should work? I'll try that - thanks. — FBryant87
– FBryant87, Commented Sep 18, 2018 at 13:02
Correct. You don't need a type that is identical to your table definition — user330315
– user330315, Commented Sep 18, 2018 at 13:05

score 1 · Accepted Answer · 2018-09-18 13:14:30Z

1

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);

edited Sep 18, 2018 at 13:14

answered Sep 18, 2018 at 11:58

user330315

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

2 Comments

FBryant87 Over a year ago

Thank you, is there a way I can access type properties (or columns) once unnested? (for example I may need to check conflicts on employee.employee_id, and then update certain fields, i.e. employee.name)

user330315 Over a year ago

@FBryant87: the unnested array will automatically give you access to the columns. See my edit

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related