Thread: Markdown format output for psql, design notes

    Lætitia Avrot wrote:

> # The result I want
> From points 3 and 4, here is what I'd like to see :
>
> | Header 1 | Header 2 | Header 3 |
> |----------|----------|----------|
> | content | content | content |
> | content | content | content |
> (2 rows)

What would it look like when a field or a header is made of multiple
lines?

Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

On 28/11/2018 09:59, Lætitia Avrot wrote:
> First, thanks to have read that whole mail and sorry I didn't mean to
> make it so long...
> Then I'd like to know ***what you think about what I'm about to do***
> before heading in a wrong direction.

I'm a little bit reluctant for us to write and maintain more and more
format styles, especially one as subjective and varied as markdown.  I
imagine we will constantly be bombarded with "this isn't quite right" or
"this isn't compatible with github".

What I personally use is the excellent pandoc tool (https://pandoc.org/)
which can convert formats we already output into a multitude of other
formats.

psql -qHc "values (E'hello\nworld', 42), ('single line', 5), ('another',
null)" | pandoc -f html -t markdown

  -----------------------
  column1         column2
  ------------- ---------
  hello\               42
  world

  single line           5

  another                
  -----------------------

(3 rows)\

This handles both column alignment and the multiline issue Daniel raised.
-- 
Vik Fearing                                          +33 6 46 75 15 36
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

    Lætitia Avrot wrote:

> I suppose you mean in the standard output when the screen is too short to
> print the whole line ?
> Because if the output is redirected to a file (with `\o myfile` for
> example), the line end naturally when the row ends.

No I meant independently of the screen, if there's an LF character
in a cell. Or a '|' character, since that's the same problem: an
element of structure happening to be in the contents.
The specs mentioned upthread don't seem to give any indication
about that being supported.

Say we have:
 SELECT E'foo\nbar' as "Header1", 'foo|bar' as "Header2"

If the markdown output was produced for the sole purpose of being
converted to HTML in the end, which is often the case, it would work
to use HTML entities in the output, for instance:

Header1|Header2
---|---
foo<br>bar|foo|bar

This piece seems to be properly processed and rendered by markdown
processors I can try (pandoc, grip, github).

But then we'd also need to convert < > and & in the original contents
to the equivalent HTML entities, and that would really be
markdown-for-html instead of just markdown, I guess.


Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

On 29/11/2018 08:26, Lætitia Avrot wrote:

> # What stays in my mind
> 
> * It's pretty difficult to handle line breaks
> * Markdown is not standardised and several flavours exist for table
> implementation (so why favor one over the others?)
> 
> # The question I'd like to ask you
> So now, I think we need to ask that fundamental question :
> 
> ***Is it worth it ?***

And my answer to that is:

No.  Markdown isn't standardized enough to support and please everyone.
-- 
Vik Fearing                                          +33 6 46 75 15 36
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

    Lætitia Avrot wrote:

> But as Vik said earlier, maybe it's not worth it to provide a markdown
> output as pandoc can generate the markdown from the HTML output.
> And if you need the markdown output to generate HTML why don't you use the
> HTML output ?

The round-trip through pandoc does not do any miracle.
The end result is readable to the human eye but structurally
broken. If converted back to html, it's no longer a table.

Anyway I tend to agree with Vik on this:
 "Markdown isn't standardized enough to support and please everyone."

BTW github has independently started to support '|' in the cells
by accepting the quoted version '\|' :
https://help.github.com/articles/organizing-information-with-tables/

Now that we have csv as an output format, we can suggest
custom csv-to-markdown converters to produce markdown
rather than implementing one particular flavor of markdown
in psql, or several flavors through flags. The popular script
languages have solid CSV parsers that make this relatively easy
and safe.

Personally I'd use Perl with something like below, which looks
short/simple enough to be shared on wiki.postgresql.org,
along with versions in other languages.

#!/usr/bin/perl

# Usage
# inside psql:
#  \pset format csv
#  \o |csvtomarkdown >/tmp/output.md
#  SQL commands...
#  \o

# or psql --csv -c "...query..." | csvtomarkdown

use Text::CSV;
use open qw( :std :encoding(UTF-8) );

my $csv = Text::CSV->new({ binary => 1, eol => $/ });

sub do_format {
# customize to your needs
  s/&/&/g;
  s/</</g;
  s/>/>/g;
  s/\n/<br>/g;
  s/\|/|/g;
  return $_;
}

my $header = $csv->getline(STDIN);
for (@{$header}) {
  $_ = do_format($_);
}
print join ('|', @{$header}), "\n";
print join ('|', map { "---" } @{$header}), "\n";

while (my $row = $csv->getline(STDIN)) {
  my @contents = map { do_format($_) } @{$row};
  print join('|', @contents), "\n";
}


Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite