6

It is possible to have Python code in a PostgreSQL stored procedure. For example:

CREATE FUNCTION someProc()
RETURNS void AS $$
    # Some Python3 code...
$$ LANGUAGE plpython3u;

But how can I include a python file from this code?

The current directory of Python running in PostgreSQL is this:

>>> os.getcwd()
... /var/lib/postgresql/9.3/main

I tryied the following, which worked:

CREATE FUNCTION someProc()
RETURNS void AS $$
    import sys
    sys.path.append("/dir/to/file")
    from python_file import pythonFunction
    # More Python code
$$ LANGUAGE plpython3u;

This doesn't seem very good for many obvious reasons. Is there a better way to import a python file, or just calling a python function from a python file?

Edit: There isn't any specific PostgreSQL method to import a file. But the best way is in the correct answer bellow, which resembles my original solution but it is better.

petermlm
  • 930
  • 4
  • 12
  • 27

2 Answers2

3

This is no different to dynamically loading Python code from a file in standalone Python:

PL/Python3 (untrusted) is just the cpython interpreter running embedded in a PostgreSQL backend process as the same operating system user PostgreSQL its self runs as. With a very few differences - like thread safety - it's just Python.

Community
  • 1
  • 1
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • Thank you, although I wouldn't really consider this a duplicate question because I am coming for a specific PostgreSQL stored procedure method to find that there is none, and the solution is actually a more general Python one. But again, thank you! – petermlm Jun 22 '15 at 08:21
  • 1
    Absolutely - it's helpful to ask, and now this question will point people at the more general answer when they come looking for PL/PgSQL. If I thought it was 100% duplicate I would've just closed it as a dup without posting an answer too. – Craig Ringer Jun 22 '15 at 10:03
  • Ah ok, I thought this question would get deleted or something. Sorry for the confusion, and again, thanks. – petermlm Jun 22 '15 at 10:23
0

Have you seen this from the documentation?

http://www.postgresql.org/docs/9.0/static/plpython.html

CoryatJohn
  • 324
  • 1
  • 6