我通过终端中的psql对远程postgresql数据库运行以下查询:
\copy (SELECT cylinders.reduction, cylinders.name, print_suppliers.name FROM cylinders, jobs, print_suppliers WHERE cylinders.id = jobs.cylinder_id AND print_suppliers.id = cylinders.print_supplier_id AND jobs.id = 015983) TO '/Users/alex/result_sql.csv' WITH CSV HEADER
并将正确的csv文件保存到我的hd上。但是,我需要在本地保存一个xml文件,并尝试执行以下操作:
\copy SELECT query_to_xml('SELECT cylinders.reduction, cylinders.name, print_suppliers.name FROM cylinders, jobs, print_suppliers WHERE cylinders.id = jobs.cylinder_id AND print_suppliers.id = cylinders.print_supplier_id AND jobs.id = 015983', true, true, '') TO '/Users/alex/result_sql.xml'
但我有个错误:
\复制:“查询到xml”时出现分析错误。没有副本…给,我是
获得完美的xml输出。
有没有办法将终端的xml输出本地保存到我的计算机上?
谢谢,亚历克斯
最佳答案
复制后需要额外的括号才能从查询复制,而不是从表复制。比如这里:
\copy (
SELECT query_to_xml(
'SELECT cylinders.reduction, cylinders.name, print_suppliers.name
FROM cylinders, jobs, print_suppliers
WHERE cylinders.id = jobs.cylinder_id AND print_suppliers.id = cylinders.print_supplier_id AND jobs.id = 015983'
, true, true, ''
)
)
TO '/Users/alex/result_sql.xml'