Search code examples
pythonpostgresqlplpython

From a python module, called from a pl/python function, how to get the return value?


Is there any reason this function call would not return 'result'?

CREATE OR REPLACE FUNCTION myfunction (input int, OUT result int) AS $$

result = mymodule.object(input,plpy)
plpy.info(" ========= EXTRA-module result: ===",result)

$$ LANGUAGE plpythonu;

=== Content of mymodule ============

def object(input,plpy):
  import StringIO
  try:
   plan = plpy.prepare("INSERT INTO file VALUES (nextval('primary_sequence'),$1) RETURNING primary_key", ["integer"] )
  except:
   plpy.error(traceback.format_exc())

  try:
   rv = plpy.execute(plan, [ input ])
   result = rv[0]["primary_key"]
   plpy.info(" ========= INTRA-module result: ===",result)
   return result
  except:
   plpy.error(traceback.format_exc())

Solution

  • @ed. Didn't actually need the RETURNS syntax instead of OUT, but your suggestion put me onto the answer. And yes, I feel like a real dummy. This is the beauty of having others review one's work.

    with the return result added, things work out nicely. Key assumption I'd made here was that the result = syntax did not actually finish the return within the scope of the calling function. Doh!

    CREATE OR REPLACE FUNCTION myfunction (input int, OUT result int) AS $$
    
    result = mymodule.object(input,plpy)
    plpy.info(" ========= EXTRA-module result: ===",result)
    # This was the key bit:
    return result
    
    $$ LANGUAGE plpythonu;