Reusing pure Python functions between PL/Python functions
plpython, postgresql, python
Solution
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.
Problem
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?