Importing Python file from PostgreSQL stored procedure [duplicate]

Question

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.

Community · Accepted Answer · 2017-05-23 11:43:47Z

3

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

How to import a module given the full path?

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.

edited May 23, 2017 at 11:43

CommunityBot

11 silver badge

answered Jun 21, 2015 at 23:30

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

petermlm Over a year ago

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!

Craig Ringer Over a year ago

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.

petermlm Over a year ago

Ah ok, I thought this question would get deleted or something. Sorry for the confusion, and again, thanks.

CoryatJohn · Accepted Answer · 2015-06-21 22:23:34Z

0

Have you seen this from the documentation?

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

answered Jun 21, 2015 at 22:23

CoryatJohn

3241 silver badge6 bronze badges

2 Comments

petermlm Over a year ago

Well, yes I have looked into the documentation, but didn't find a solution there.

Craig Ringer Over a year ago

Where in the plpython docs does it refer to reading external files to load Python code? I don't think you fully read the question.

Collectives™ on Stack Overflow

Importing Python file from PostgreSQL stored procedure [duplicate]

2 Answers 2

3 Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Linked

Related