I have a function that takes 3 values:
partitionId varchar(48)='tenant';
jobId varchar(40)='job1';
taskIds varchar[] := '{job1.1, job1.5, job1.3}';
I would like to have those inserted in a table as:
tenant job1 job1.1 2020-10-09 11:38:03
tenant job1 job1.5 2020-10-09 11:38:03
tenant job1 job1.3 2020-10-09 11:38:03
I know that I could use a loop on the array then insert one by one, but I believe it's not the most efficient so, I am trying to insert in one go. Not sure if I should use an intermediary table for that or if there's a more direct solution.
I tried:
DO
$do$
DECLARE
partitionId varchar(48)='tenant';
jobId varchar(40)='job1';
taskIds varchar[] := '{job1.1, job1.5, job1.3}';
begin
insert into completed_subtask_report values (partitionId, jobId, taskIds, current_timestamp );
END
$do$
But that would insert the array as:
tenant job1 {job1.1,job1.5,job1.3} 2020-10-09 12:44:09
Anyone knows how to do that?
Use unnest:
insert into completed_subtask_report ( ... column names go here ...)
select 'tenant', 'job1', x.task, current_timestamp
from unnest('{job1.1, job1.5, job1.3}') as x(task)
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments