I was today years old when I discovered that PostgreSQL’s COPY command has a PROGRAM ‘whatever shell command’ option.
For the past few years I’ve been using SWI-Prolog as the “receptionist” passing queries to PostgreSQL via its ODBC library, whereas what I really wanted was for the database to call a Prolog script occasionally if an entry needed to be generated.
In my case, it’s for a strategy game system, so the moves get “memoized” and the database only needs to call on my Prolog script to “grow the game tree” when it finds itself in a previously unexplored state.
I’m sure there must be plenty of other use cases, so thought I’d share my new discovery with anyone else struggling to combine PostgreSQL and SWI-Prolog in an application.
Getting this to work also involves knowing PostgreSQL’s PL/pgSQL server-side scripting language (luckily the CS course I did way back in the last century taught Pascal which is a bit similar) to generate a command my SWI-Prolog script can read its input from and then write its result to STDIN for PostgreSQL to proceed with.