On Sat, Sep 6, 2014 at 11:43 AM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
On 09/06/2014 10:34 AM, Abelard Hoffman wrote:
On Sat, Sep 6, 2014 at 7:28 AM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx<mailto:adrian.klaver@aklaver.com>> wrote:
On 09/06/2014 12:32 AM, Abelard Hoffman wrote:
[snip]
So, my question is, what's the simplest way to generate tab-escaped
TSV-formatted reports with the first line containing the list of
column
names?
create table tsv_test (id int, fld_1 varchar);
insert into tsv_test values (1, 'test value');
insert into tsv_test values (2, 'test value');
insert into tsv_test values (3, 'test value');
\copy tsv_test to 'data.tsv' with csv header delimiter ' ';
aklaver@panda:~> cat data.tsv
id fld_1
1 "test value"
2 "test value"
3 "test value"
Thanks, Adrian. That works, but since we're using quotes to embed the
delimiter, we lose the simplicity of TSV. I can't just do a split on
/\t/ to get the fields and then unescape the values. At that point it's
probably simpler to just switch to standard CSV.I guess it depends on what you are using.
Using your example, the output I'd prefer is:
id fld_1
1 test\tvalue
2 test\tvalue
3 test\tvalue
In Python:
[snip]
Yeah, I can parse CSV easily enough. My boss wants TSV though (I could parse CSV and split out TSV, of course). Even then, having to take the report query (which can be big), strip all the newlines and insert it into a \copy command is kind of a PITA.
I wrote this function, which does what I want. I can call this on each column that might include tabs and then safely generate TSV using just psql:
CREATE OR REPLACE FUNCTION public.tsv_escape(text) RETURNS text AS $$
my %replace = (
"\t" => 't',
"\r" => 'r',
"\n" => 'n',
'\\' => '\\',
);
$_[0] =~ s/(.)/exists $replace{ $1 } ? "\\$replace{$1}" : $1/gse;
return $_[0];
$$ LANGUAGE 'plperl';
Anyone see a way to rewrite that as an 'sql' function rather than plperl?
I also took a look at the psql source. It doesn't look like it would be very hard to add some backslash escaping logic as an option. Am I the only one that would use that? For reports, everyone else mostly uses other tools? I'd like to stay away from GUI-tools, if possible.
Thanks.
