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?

Pensile

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.

Halve

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.

Steinke

yeah, that looks to be the only option unless I want to mess with OID hacks. – Pensile