Capture the Postgresql function return string within Python console

Question

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. Something like as below:

---above python script---
print (results) <--results is the returning string that comes from fn_indent()

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

I want that the python script shall have print(fn_indent() return string) or similar like this. — pythondumb
– pythondumb, Commented Sep 23, 2020 at 7:15
If the function just returns a string I would try with '''Select fn_indent()''''? — Iñigo González
– Iñigo González, Commented Sep 23, 2020 at 9:14

pythondumb · Accepted Answer · 2020-09-23 10:15:55Z

0

I got the clue from this thread

Refer here

Accordingly, I have modified the code base as follows:

conn = psycopg2.connect({connection string})
curr = conn.cursor
curr.execute("SELECT * FROM script.fn_indent()")
conn.commit()
s = curr.fecthone()
print (s)
conn.close()

edited Sep 23, 2020 at 10:15

answered Sep 23, 2020 at 8:15

pythondumb

1,2591 gold badge16 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Capture the Postgresql function return string within Python console

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related