Reusing pure Python functions between PL/Python fu

问题:

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?

回答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.

回答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.