Thread: \copy multiline
Hi, I use \copy to output tables into CSV files: \copy (SELECT ...) TO 'a.csv' CSV but for long and complex SELECT statements, it is cumbersome and confusing to write everything in a single line, and multiline statements don't seem to be accepted. Is there an alternative, or am I missing an continuation-character/option/variable that would allow multiline statements in this case? Cheers, -- Seb
On Wed, 2012-11-28 at 21:21 -0600, Seb wrote: > Hi, > > I use \copy to output tables into CSV files: > > \copy (SELECT ...) TO 'a.csv' CSV > > but for long and complex SELECT statements, it is cumbersome and > confusing to write everything in a single line, and multiline statements > don't seem to be accepted. Is there an alternative, or am I missing an > continuation-character/option/variable that would allow multiline > statements in this case? > A simple way to workaround this issue is to create a view with your query and use the view in the \copy meta-command of psql. Of course, it means you need to have the permission to create views in the database. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com
On 11/29/2012 02:33 AM, Guillaume Lelarge wrote: > On Wed, 2012-11-28 at 21:21 -0600, Seb wrote: >> Hi, >> >> I use \copy to output tables into CSV files: >> >> \copy (SELECT ...) TO 'a.csv' CSV >> >> but for long and complex SELECT statements, it is cumbersome and >> confusing to write everything in a single line, and multiline statements >> don't seem to be accepted. Is there an alternative, or am I missing an >> continuation-character/option/variable that would allow multiline >> statements in this case? >> > A simple way to workaround this issue is to create a view with your > query and use the view in the \copy meta-command of psql. Of course, it > means you need to have the permission to create views in the database. > > Or maybe a function returning a table or set of records. Might be slightly more flexible than the view.
On Thu, 29 Nov 2012 08:01:31 +0000, Ben Morrow <ben@morrow.me.uk> wrote: > Quoth spluque@gmail.com (Seb): >> I use \copy to output tables into CSV files: >> \copy (SELECT ...) TO 'a.csv' CSV >> but for long and complex SELECT statements, it is cumbersome and >> confusing to write everything in a single line, and multiline >> statements don't seem to be accepted. Is there an alternative, or am >> I missing an continuation-character/option/variable that would allow >> multiline statements in this case? > CREATE TEMPORARY VIEW? Of course, that's perfect. Thanks! -- Seb
On Thu, 29 Nov 2012 10:33:37 +0100, Guillaume Lelarge <guillaume@lelarge.info> wrote: > On Wed, 2012-11-28 at 21:21 -0600, Seb wrote: >> Hi, >> I use \copy to output tables into CSV files: >> \copy (SELECT ...) TO 'a.csv' CSV >> but for long and complex SELECT statements, it is cumbersome and >> confusing to write everything in a single line, and multiline >> statements don't seem to be accepted. Is there an alternative, or am >> I missing an continuation-character/option/variable that would allow >> multiline statements in this case? > A simple way to workaround this issue is to create a view with your > query and use the view in the \copy meta-command of psql. Of course, > it means you need to have the permission to create views in the > database. Thanks. Someone also suggested creating a temporary view, which helps keep the schema sane and clean. Cheers, -- Seb