macOS: add PYTHONPATH to PostgreSQL brew service

Source: Changing the Homebrew Apache PATH variable.

Let's suppose we installed Python requests module.
On my system, requests has been installed in /usr/local/lib/python2.7/site-packages/.
Then, trying to import it from postgres like in

1 2 3 4 5 6 7 8

create or replace function tutu(o_status out int) as $$ import sys, os import requests, json plpy.info(sys.version) plpy.info(sys.path) return (500) $$ language plpython2u security definer;

will cause

select * from tutu()

to output

Query 1 ERROR: ERROR:  ImportError: No module named requests

By default, postgres will look in /Library/Python/2.7/site-packages/.
We then need to update PYTHONPATH.
With brew starting postgres, we will update the postgres plist file.

[jerome@jeroboam] > vi /usr/local/opt/postgresql\@9.4/homebrew.mxcl.postgresql\@9.4.plist

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

<plist version="1.0"> <dict>   <key>KeepAlive</key>   <true>   <key>Label</key>   <string>homebrew.mxcl.postgresql@9.4</string>   <key>EnvironmentVariables</key>   <dict>     <key>PYTHONPATH</key>     <string>/usr/local/lib/python2.7/site-packages</string>   </dict>   <key>ProgramArguments</key>   <array>     <string>/usr/local/opt/postgresql@9.4/bin/postgres</string>     <string>-D</string>     <string>/usr/local/var/postgresql@9.4</string>   </array>   <key>RunAtLoad</key>   <true>   <key>WorkingDirectory</key>   <string>/usr/local</string>   <key>StandardErrorPath</key>   <string>/usr/local/var/log/postgresql@9.4.log</string> </true></true></dict> </plist>

Then

[jerome@jeroboam] > brew services stop postgresql@9.4
[jerome@jeroboam] > brew services start postgresql@9.4

Then

select * from tutu()

outputs

1 2 3

INFO:  2.7.16 (default, Dec 13 2019, 18:00:32) [GCC 4.2.1 Compatible Apple LLVM 11.0.0 (clang-1100.0.32.4) (-macos10.15-objc-s INFO:  ['/usr/local/lib/python2.7/site-packages', '/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python27.zip', '/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7', '/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/plat-darwin', '/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/plat-mac', '/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/plat-mac/lib-scriptpackages', '/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/lib-tk', '/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/lib-old', '/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/lib-dynload', '/Users/jerome/Library/Python/2.7/lib/python/site-packages', '/Library/Python/2.7/site-packages', '/System/Library/Frameworks/Python.framework/Versions/2.7/Extras/lib/python', '/System/Library/Frameworks/Python.framework/Versions/2.7/Extras/lib/python/PyObjC']