Capture the Postgresql function return string within Python console

  postgresql, python

I have a postgresql function that returns a string as follows:

CREATE OR REPLACE FUNCTION script.fn_indent()
RETURNS character varying
LANGUAGE 'plpgsql'
------
----function body to perform data insertion job---
results:='0 - Success';
return results
exception when others then
  get stacked diagnostics
      v_state   = returned_sqlstate,
      v_msg     = message_text,
      v_detail  = pg_exception_detail,
      v_hint    = pg_exception_hint,
      v_context = pg_exception_context;
  raise notice 'Transaction was rolled back';
  raise notice '% %', SQLERRM, SQLSTATE;
  results:=v_state||'-'||v_msg||v_msg||'-'||v_detail ||'-'||v_hint ||'-'||v_context;
return results;

Now I am trying to run the above function from python using psycopg2.

conn = psycopg2.connect({connection string})
curr = conn.cursor
try: 
   curr.execute("SELECT * FROM script.fn_indent())
   conn.commit()
except (Exception, psycopg2.DatabaseError) as error:
    err = str(error)
    conn.rollback()
    curr.close()
    print(err)

conn.close()

The above code is running fine. But I want to capture the return string from script.fn_indent() and show the same to python console.

How to do it? I do not have any clue on this.

Source: Python Questions

LEAVE A COMMENT