PostgreSQL on Docker:如何在"plpython3u"下安装和运行python依赖

Question

有了Dockerfile，我可以完美地运行plpython3u

FROM postgres:11.3
RUN apt-get update && apt-get install -y postgresql-plpython3-11

CREATE OR REPLACE FUNCTION return_version()
RETURNS VARCHAR
AS $$
import sys
return sys.version
$$ LANGUAGE plpython3u;

3.5.3(默认，2020年7月9日13:00:10([GCC 6.3.0 20170516]`

但我不能使用依赖项，例如requests

[38000] ERROR: ImportError: No module named 'requests' 
Where: Traceback (most recent call last): PL/Python function "return_version", line 3, 
in <module> import requests PL/Python function "return_version"

第一次尝试进行call

CREATE OR REPLACE FUNCTION return_pip()
RETURNS VARCHAR AS $$
from subprocess import call
return call(["pip", "install", "requests"])
$$ LANGUAGE plpython3u STABLE ;

没有希望

[38000] ERROR: FileNotFoundError: [Errno 2] No such file or directory: 'pip' Where: Traceback (most recent call last): PL/Python function "return_ls", line 3, in <module> return call(["pip", "install", "requuest"]) PL/Python function "return_ls", line 246, in c ...

第二次尝试

CREATE OR REPLACE FUNCTION return_version()
RETURNS VARCHAR
AS $$
import sys
from subprocess import call
return call(["pip3", "install", "requests"])
import requests
res = requests.get('https://google.com')
print(res.text)
return res.text
$$ LANGUAGE plpython3u;

收到错误

SELECT return_version()
[2020-09-29 10:59:44] [38000] ERROR: FileNotFoundError: [Errno 2] No such file or directory: 'pip3'
[2020-09-29 10:59:44] Where: Traceback (most recent call last):
[2020-09-29 10:59:44] PL/Python function "return_version", line 4, in <module>
[2020-09-29 10:59:44] return call(["pip3", "install", "requests"])
[2020-09-29 10:59:44] PL/Python function "return_version", line 246, in call
[2020-09-29 10:59:44] PL/Python function "return_version", line 675, in __init__
[2020-09-29 10:59:44] PL/Python function "return_version", line 1281, in _execute_child
[2020-09-29 10:59:44] PL/Python function "return_version"

我有类似的人问同样的问题，但没有回答

问题：
如何在plpython3u下安装和运行python依赖项？

Answer 1

感谢@AdrianKlaver的回答

按此操作。我可以得到response

1. 我选择这个答案作为我的形象
2. 在SQL shell中执行CREATE EXTENSION plpython3u;
3. 在操作系统(又名容器(中运行pip3 install requests
4. create函数

   CREATE OR REPLACE FUNCTION return_version()
   RETURNS VARCHAR
   AS $$
   import requests
   res = requests.get('https://google.com')
   return str(res.text)
   $$ LANGUAGE plpython3u;
   

5. SELECT return_version();检查结果