Is it possible to embedded execute() in a custom function for CALL statement?

[jimmymcheung]

I tried to execute a procedure by cursor.execute() inside a custom function, but it was not succeeded. Running other kind of statement do works, running exact same script that the custom function does in a python console also works. I have go through a number of discussion and googled, but doesn't seem to fix.

Below is an example code:

import psycopg2
from configparser import ConfigParser


def config(filename, section):
    # create a parser
    parser = ConfigParser()
    # read config file
    parser.read(filename)

    # get section
    db = {}
    if parser.has_section(section):
        params = parser.items(section)
        for param in params:
            db[param[0]] = param[1]
    else:
        raise Exception('Section {0} not found in the {1} file'.format(section, filename))

    return db


def cus_func(opr, config='database.ini', sec='postgresql'):
    try:
        params = config(filename=config, section=sec)
        conn = psycopg2.connect(**params)
        cur = conn.cursor()
        cur.execute(query=opr)
        cur.close()
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
    finally:
        if conn is not None:
            conn.close()


cus_func("CALL pg_procedue_one('12994');")

and the procedure (to be called by `pg_procedue_one(IN id text):

BEGIN

-- some comments

INSERT INTO id_search (str, date) VALUES
	(id, current_date);
-- ON CONFLICT (str) DO UPDATE
-- SET date = current_date;
COMMIT;

END;

If run from above python script, a commit error will be raised with following msg CONTEXT: PL/pgSQL function pg_procedue_one(text) line 15 at COMMIT. However, if running same code in this function (see below), it will be successfully inserted to the database. SELECT from the database will show the entry '12994' with the current date.

from code_dir import sample_script # import above script
import psycopg2

params = config(filename=config, section=sec)
conn = psycopg2.connect(**params)
cur = conn.cursor()
cur.execute(query=opr)

[dvarrazzo]

If the function called does transaction control, it cannot run in a transaction.

Psycopg, by default, starts a transaction automatically. If you want to change this behaviour, you should use an autocommit connection.

[jimmymcheung]

Thanks, I found I wrote commit() in one of the finally: and on the other one I missed it. That seems to be the reason not appearing in the db