将SELECT语句中的多行与另一列中的数组一起插入

Question

当我运行以下查询时，您好：

SELECT fundid 
FROM funds
WHERE reportingfrequency = 'Daily'
AND primarynavcostlocation = 'Wroclaw'
AND primaryaccountmgr1 = 'wx

我得到：

"fundid"
11111
22222

因此，现在我要做的是将这两个fundid行与一个数组一起插入，以便数据看起来如下所示：

"fundid" | "listdate"
11111    | {2021-01-02, 2021-01-03}
22222    | {2021-01-02, 2021-01-03}

我试着欺骗查询，以获得一个有效的插入，如-

INSERT into nav_due_dates (fund_id, listdate)
VALUES(
(
SELECT fundid 
FROM funds
WHERE reportingfrequency = 'Daily'
AND primarynavcostlocation = 'Wroclaw'
AND primaryaccountmgr1 = 'wxy'
),
(
SELECT array_agg(weekdays::date)
FROM generate_series(date'2021-01-01', date'2021-12-31', interval '1' day) as t(weekdays)
LEFT JOIN holidays.poland(2021, 2021) f ON (weekdays = f.datestamp)
WHERE f.datestamp IS NULL 
AND extract(dow from weekdays) BETWEEN 1 AND 5
)
);

它返回：

ERROR: more than one row returned by a subquery used as an expression

在进行了一些挖掘后，我也尝试了以下操作-

INSERT INTO nav_due_dates (fund_id, listdate)
SELECT fundid 
WHERE reportingfrequency = 'Daily'
AND primarynavcostlocation = 'Wroclaw'
AND primaryaccountmgr1 = 'wxy', 
(
SELECT array_agg(weekdays::date)
FROM generate_series(date'2021-01-01', date'2021-12-31', interval '1' day) as t(weekdays)
LEFT JOIN holidays.poland(2021, 2021) f ON (weekdays = f.datestamp)
WHERE f.datestamp IS NULL 
AND extract(dow from weekdays) BETWEEN 1 AND 5
)
FROM funds;

但是我似乎无法解决语法错误，例如删除,或填充listdate列的数组select语句周围的括号。

Answer 1

我想你想这样做：

insert into nav_due_dates (fund_id, listdate)
select fundid, dd 
from 
(
SELECT fundid 
FROM funds
WHERE reportingfrequency = 'Daily'
AND primarynavcostlocation = 'Wroclaw'
AND primaryaccountmgr1 = 'wxy'
) t1
cross join 
(
SELECT array_agg(weekdays::date) dd
FROM generate_series(date'2021-01-01', date'2021-12-31', interval '1' day) as t(weekdays)
LEFT JOIN holidays.poland(2021, 2021) f ON (weekdays = f.datestamp)
WHERE f.datestamp IS NULL 
AND extract(dow from weekdays) BETWEEN 1 AND 5
) t2

Answer 2

所以我设法让它工作起来，这完全是因为FROM部分的排序。

INSERT INTO nav_due_dates (fund_id, listdate)
SELECT fundid,
(
SELECT array_agg(weekdays::date)
FROM generate_series(date'2021-01-01', date'2021-12-31', interval '1' day) as t(weekdays)
LEFT JOIN holidays.poland(2021, 2021) f ON (weekdays = f.datestamp)
WHERE f.datestamp IS NULL 
AND extract(dow from weekdays) BETWEEN 1 AND 5
)
FROM funds
WHERE reportingfrequency = 'Daily'
AND primarynavcostlocation = 'Wroclaw'
AND primaryaccountmgr1 = 'wxy';