Reusing pure Python functions between PL/Python functions

Question

I would like to declare and share some simple, pure python functions between two or more PL/Python functions. I am using Postgres 9.3.

For example, I have:

 CREATE OR REPLACE FUNCTION get_mod(modifier varchar)
 RETURNS varchar
 AS $$
 def is_float(val):
 try:
 if val:
 float(val)
 return True
 else:
 return False
 except ValueError:
 return False
 if modifier is None:
 return "NOMOD"
 if is_float(modifier):
 return str(float(modifier)*1)
 return modifier
 $$ LANGUAGE plpythonu;

I would like to use function is_float in some other PL/Python function. I understand I could create it as callable PL/Python function, but I find that much clunkier (to execute SQL-based call to PL/Python) than just making a straight call to a pure Python, custom utility function.

Is it possible to create and expose through PL/Python reusable pure Python functions on Postgres?

Answer 1

What I usually do is pass the functions around using GD. The downside is that since GD is a per session object you need to load it each time you start a new session. The way you can approach this is to have a bootstrap function that you run at the beginning of each session that primes the database for further use. Something like:

create or replace function bootstrap() returns void
as
$$
def is_float(val):
 # did some simplifying here, 
 try: 
 float(val) # Take notice that booleans will convert to float successfully
 return True
 except (ValueError, TypeError):
 return False
GD['is_float'] = is_float
$$ language plpythonu;

Now you can modify your original function:

CREATE OR REPLACE FUNCTION get_mod(modifier varchar)
 RETURNS varchar
 AS $$
 # Optionally run bootstrap() here
 plpy.execute("select bootstrap()")
 ###
 if modifier is None:
 return "NOMOD"
 if GD['is_float'](modifier):
 return str(float(modifier)*1)
 return modifier
 $$ LANGUAGE plpythonu;

In order for this to work you'd have to run select bootstrap(); at the start of each session, or as part of the first function you are calling as part of the flow... Or indeed as part of your original function.

Answer 2

One option is to create a module and then import it. You can add its location to PYTHONPATH as described here to ensure the runtime can find it.

• Edmon: yeah, that looks to be the only option unless I want to mess with OID hacks.