0

The goal is to execute a script hi.sh after an assertion in SQL script.

hi.sh contains:

#!/bin/bash

echo 'Hello World'

I'm trying to execute hi.sh within DO:

DO $$
BEGIN
   ASSERT EXISTS (SELECT * FROM pg_catalog.pg_tables WHERE schemaname = 'public'), '"Public" schema is empty';
   \! ./hi.sh
END$$;

It should raise error '"Public" schema is empty' and not execute hi.sh but once there are entries in public schema, hi.sh must be executed.

Running the sql script raises error:

psql:assert.sql:5: ERROR:  syntax error at or near "\"
LINE 4:    \! ./hi.sh
           ^

However, it will execute shell script once '\!' line is outside DO, like this:

\! ./hi.sh

How can we create a simple SQL script for executing hi.sh based on assertion?

nnarefun
  • 89
  • 1
  • 7
  • `\!` is not SQL! It's a client command! – F. Hauri - Give Up GitHub Jun 25 '21 at 15:48
  • Yes, scripts are executed with: psql -d postgres - f script.sql – nnarefun Jun 26 '21 at 18:29
  • I think it's a ***wrong good idea*** to run script from sql client. You'd better create a script that run psql as backgrounded task, interact with them and run subshell when needed. See my function *newSqlConnector* in [my *shell_connector.sh*](https://f-hauri.ch/vrac/shell_connector.sh.txt), and [my answer to *How do I set a variable to the output of a command*](https://stackoverflow.com/a/41236640/1765658). – F. Hauri - Give Up GitHub Jun 27 '21 at 07:00
  • Thanks, I have solved like so before, was looking for a simplified solution. So the answer is that it is not possible to execute bash scripts with psql client. – nnarefun Jun 28 '21 at 06:20
  • Yes, it is! But in the other way! Have a look at my *shell connector*! Install `sqlite3` then run this as a *demo*. Look into script to find correct syntax for addressing `psql`, Your script had to *source* them, inititate `newSqlConnector`, then interaction with `psql` is ready. – F. Hauri - Give Up GitHub Jun 28 '21 at 12:46
  • ... And yes, I think the way you previously use is still possible, but discouraged... – F. Hauri - Give Up GitHub Jun 28 '21 at 12:49

0 Answers0