8

Is there any way to capture shell output (in a psql variable) and/or the exit code of running a shell command from inside psql using \!? The documentation provides no details.

Sim
  • 13,147
  • 9
  • 66
  • 95

3 Answers3

2

Using \! with \o..

You can combine the two like this,

  • \! echo "SELECT 1;" > bar (runs the command echo "SELECT 1;" redirects output of SELECT 1; to bar)
  • \o bar (runs the commands in bar)
Evan Carroll
  • 78,363
  • 46
  • 261
  • 468
1

Seems to be impossible for \!,

but one can set a variable from external command output, 

testdb=> \set content `cat my_file.txt`

and then using this var in sql expression like this:

testdb=> INSERT INTO my_table VALUES (:'content');
Pavel T
  • 403
  • 4
  • 12
-1

You can use \o <filename> option to specify the output file or use COPY command to solve your problem.

Dmitry S
  • 4,990
  • 2
  • 24
  • 32
  • I'm not sure how `\o` helps. If I am running a shell command, I can always redirect its output to a file. Also not sure how `COPY` helps. I'm not trying to load data. – Sim Aug 24 '15 at 22:49
  • well, I guess it depends what you are trying to achieve, for bulk ddl updates I used `\o` a [lot](https://github.com/dsavinkov/shell/blob/master/update_all_postgres_schemas.sh). I guess it's not your case. – Dmitry S Aug 24 '15 at 23:04
  • [this](http://stackoverflow.com/questions/12535766/store-postgresql-query-result-to-shell-or-postgresql-variable) might be what you are looking for – Dmitry S Aug 24 '15 at 23:11
  • 1
    The SO question you point to is about capturing SQL output _from_ psql. I want to capture shell output and exit code _in_ psql. – Sim Aug 25 '15 at 01:08
  • Well, according to the [documentation](http://www.postgresql.org/docs/9.4/static/app-psql.html) you can achieve what you want by capturing in shell exit status and using meta-commands. Why can't you use exit codes ? @Sim – Dmitry S Aug 28 '15 at 01:18
  • 1
    I can capture the shell exit status of `psql` but I see no documentation for how to capture the exit status of whatever is run via `\!`. Do you? – Sim Aug 29 '15 at 00:28