2

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

2
  • 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 6 '20 at 14:57
  • according to 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 6 '20 at 15:35
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.