CODEWITHSUNDEEP
postgresql
Adam Benson
Adam Benson

Reputation: 8522

How to insert array of complex types into a table

I have a complex type defined as:

create type TP_IdAndVer AS
(
    id  uuid,
    ver integer
);

And I'd like to insert an array of them into a temporary table (so I can join on a composite key).

This is what I have so far but it doesn't work:

DO $$

declare fred TP_IdAndVer[];

BEGIN

drop table if exists tmpTable;

fred := array[ ('034892e4-6137-440c-bb62-b609b60575aa'::uuid, 1), ('034892e4-6137-440c-bb62-b609b60575aa'::uuid, 2) ];

create temporary table temptbl_ids_and_vers(id uuid, ver integer)
on commit drop;

-- I want to insert each row of fred as a row into my temp table. But how??
insert into temptbl_ids_and_vers(id, ver) values (fred);

CREATE TEMPORARY TABLE tmpTable AS
select * from temptbl_ids_and_vers;

END $$;

select  *
from    tmpTable;

I get this error:

ERROR:  INSERT has more target columns than expressions
LINE 1: insert into temptbl_ids_and_vers(id, ver) values (fred)
                                             ^
QUERY:  insert into temptbl_ids_and_vers(id, ver) values (fred)
CONTEXT:  PL/pgSQL function inline_code_block line 15 at SQL statement

********** Error **********

ERROR: INSERT has more target columns than expressions
SQL state: 42601
Context: PL/pgSQL function inline_code_block line 15 at SQL statement

If someone could point me to the right syntax to use it would be a big help. I guess I could loop through each element of the array inserting each row individually, but that feels a bit hacky.

Alternatively, is there a way of passing a table to a stored proc? This would get around what I'm trying to achieve at the moment since I could just a pass a table in without needing to worry about arrays.

Thanks,

Adam.

Upvotes: 1

Views: 1060

Answers (2)

Usagi Miyamoto
Usagi Miyamoto

Reputation: 6289

INSERT INTO temptbl_ids_and_vers(id, ver)
SELECT *
FROM UNNEST(fred) AS q(id, ver)

Upvotes: 1

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125204

insert into temptbl_ids_and_vers(id, ver)
select id, ver
from unnest(fred) u

Upvotes: 0

Related Questions