全选一列（而不是四列）。函数format（）将帮助您更清楚地记录下来。用
regexp_replace (str, '(\d\d\d\d-\d\d-\d\d)T', '\1 ', 'g')
更正日期格式，并
replace (str, e' \n ', '')
跳过换行符。
使用COPY命令可以简化问题：
COPY (
with f_1 as(
SELECT id, json_agg(file1.*) AS tag
FROM forum.file1
GROUP BY id
), f_2 as (
SELECT id, json_agg(file2.*) AS tag
FROM forum.file2
GROUP BY id
), f_3 as (
SELECT id, json_agg(file3.*) AS tag
FROM forum.file3
GROUP BY id
)
SELECT
replace(
regexp_replace(
format('{"id":%s,"file1":%s,"file2":%s,"file3":%s}',
a.id, a.tag, b.tag, c.tag),
'(\d\d\d\d-\d\d-\d\d)T', '\1 ', 'g'),
e' \n ', '')
FROM f_1 AS a, f_2 AS b, f_3 AS c
WHERE b.id = a.id AND c.id = a.id
) TO '/full/path/to/your/file';
要在每行数据前添加命令行，可以使用带有返回两行功能的技巧。格式化的某些部分可以偶尔移至该功能。
create or replace function format_data_line(command text, data_str text)
returns setof text language plpgsql as $$
begin
return next command;
return next
replace(
regexp_replace(data_str,
'(\d\d\d\d-\d\d-\d\d)T', '\1 ', 'g'),
e' \n ', '');
end $$;
COPY (
with f_1 as(
SELECT id, json_agg(file1.*) AS tag
FROM forum.file1
GROUP BY id
), f_2 as (
SELECT id, json_agg(file2.*) AS tag
FROM forum.file2
GROUP BY id
), f_3 as (
SELECT id, json_agg(file3.*) AS tag
FROM forum.file3
GROUP BY id
)
SELECT
format_data_line(
'my command',
format('{"id":%s,"file1":%s,"file2":%s,"file3":%s}',
a.id, a.tag, b.tag, c.tag))
FROM f_1 AS a, f_2 AS b, f_3 AS c
WHERE b.id = a.id AND c.id = a.id
) TO '/full/path/to/your/file';
602392714
清零编程群