Re: psql and tab-delimited output

On Sat, Sep 6, 2014 at 11:43 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 09/06/2014 10:34 AM, Abelard Hoffman wrote:
>
>> On Sat, Sep 6, 2014 at 7:28 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
>> <mailto:adrian(dot)klaver(at)aklaver(dot)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(at)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.
>>
>
>> Using your example, the output I'd prefer is:
>>
>> id fld_1
>> 1 test\tvalue
>> 2 test\tvalue
>> 3 test\tvalue
>>
>
> I guess it depends on what you are using.
>
> 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.