1

I work with an ancient inventory control application on Linux. It can only print to text files to communicate with other software. When I need to update a table in our PostgreSQL database I have been using psql -f text_file (where text_file is the file of SQL commands generated by our old application) This works fine but is rather slow as the psql session terminates after completing each text file. I was wondering if there is method of invoking a psql session as a background process that waits for input and does not terminate.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
WoodWork
  • 11
  • 1
  • 2

2 Answers2

3

If you want to run a bunch of sql files against your database, one after the other, I would start up psql as interactive console:

psql mydatabase

And execute one file after the other with the \i command:

\i text_file

If you want to script / automate things, a fifo might be what you need. I wrote more about that here.

Or you may be interested in a coprocess like Craig Ringer describes here.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Just curious, why is that preferable to just catting all of the files at once to `psql -f -`? – Tim Pote May 05 '12 at 15:01
  • 1
    @TimPote: May be preferable if you want to check on the result of each file before you proceed or if the files have to be executed in a certain order or if not all of them are available at the same time or if the same file (with changed content) is used repeatedly. I wrote "one after the other" in my intro to refer to that. – Erwin Brandstetter May 05 '12 at 15:05
  • Hi Erwin, thank you very much for this - it has solved my initial problem and been an education. – WoodWork May 15 '12 at 08:33
1

I'm not exactly sure what you're wanting. If you just have a bunch of files that need processing, use:

cat *.sql | psql -f -

If you are just wanting to continually run psql and have it run any changes in a file, something like this may work:

(   
while sleep 5; do
  print "\i sql_file.sql;"
  cat /dev/null > sql_file.sql
done
) | psql --file -

I'm not sure how advisable this is, I've never done it myself.

Tim Pote
  • 27,191
  • 6
  • 63
  • 65
  • +1 The `cat` route should be the simplest solution if you just want to execute a bunch of file in no particular order and do not want to check the result in between. – Erwin Brandstetter May 05 '12 at 15:10
  • Thanks Tim, the allows me to genereate the SQL statements in the application and then pipe them out to the pseudo printer psql -f - this significantly reduces overhead. – WoodWork May 15 '12 at 08:17