3

Is the way to get same output as in terminal with ob-doc-sql.

Executing the sql-query in org will process results into org-table ignoring other than table information. It doesn't fare well with multiple query in the block as show below will squash both into single table.

#+BEGIN_SRC sql :engine postgresql :database postgres
  SELECT 'hello';
  SELECT 'hello', 'world';
#+END_SRC

#+RESULTS:
| ?column? |          |
|----------+----------|
| hello    |          |
| ?column? | ?column? |
| hello    | world    |

header argument :results with output or verbatim value produce correct structure but table formatting is stripped out. But stil doesn't produce any terminal like output as shown below.

#+RESULTS:
: ?column?
: hello world
: ?column?  ?column?
: hello  world

Althouh expected output can be produced via shell execution,

#+BEGIN_SRC sh :results output
  psql postgres -X -c "SELECT 'hello'" -c "SELECT 'hello', 'world';"
#+END_SRC

#+RESULTS:
#+begin_example
 ?column? 
----------
 hello
(1 row)

 ?column? | ?column? 
----------+----------
 hello    | world
(1 row)

#+end_example
rho
  • 243
  • 2
  • 10
  • Nice question! Does `ob-doc-sql` provide a `:session` header? Sometimes adding session will alter the results. Also did you try adding `:results verbatim` to the block? – Melioratus Apr 06 '20 at 14:57
  • according to https://orgmode.org/worg/org-contrib/babel/languages/ob-doc-sql.html there is no session support. adding `verbatim` has similar result as `output` – rho Apr 06 '20 at 15:35

1 Answers1

2

ob-sql passes the -A or --no-align option to psql that

Switches to unaligned output mode. (The default output mode is aligned.) This is equivalent to \pset format unaligned.

ob-sql's :cmdline line doesn't help, because it's passed to psql after all other parameters, and psql does everything in order:

When either -c or -f is specified, psql does not read commands from standard input; instead it terminates after processing all the -c and -f options in sequence.

Even if we add :cmdline "-c '\\pset format aligned'" it won't affect the result.

So to get the output you want apply :results verbatim and then call \a inside your code blocks:

#+BEGIN_SRC sql :engine postgres :database postgres :results verbatim
\a
\pset footer 1
SELECT 'hello';
SELECT 'hello', 'world';
#+END_SRC

#+RESULTS:
#+begin_example
SET
 ?column?
----------
 hello
(1 row)

 ?column? | ?column?
----------+----------
 hello    | world
(1 row)

#+end_example

\pset footer 1 brings (1 row) messages back, as ob-sql turns them off too with -P footer=off.

I think it should also be possible to copy the org-babel-execute:sql function to your config and change it however you like. I don't see an easier way to customize this behavior.

skovorodkin
  • 163
  • 8