import psycopg2
import time
import re


DB = "user=hr password=hr host=dev01.site port=9999 dbname=hr"


def syncdb():
    connection = psycopg2.connect(DB)
    connection.autocommit = False
    cursor = connection.cursor()

    cursor.execute("""

        drop table if exists test_call_writing_function;
        create table test_call_writing_function(
          id serial not null,
          value double precision not null,
          type character(255) not null
        );

        drop function if exists writing_function(double precision, character);

        create or replace function writing_function(p_value double precision, p_type character) returns integer as
        $BODY$
            insert into test_call_writing_function (value, type)
            values(p_value, p_type)
            returning id;
        $BODY$ LANGUAGE sql volatile;
    """)

    cursor.close()
    connection.commit()
    connection.autocommit = True
    connection.close()


def create_use_procedure(value, type):
    connection = psycopg2.connect(DB)
    connection.autocommit = False
    cursor = connection.cursor()

    cursor.execute("BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;")

    cursor.execute("select writing_function(%s, %s)", (value, type))
    entity_id = cursor.fetchone()[0]

    cursor.execute("""
        select id, value
        from test_call_writing_function
        where id = %s
    """, (entity_id, ))
    entity = cursor.fetchone()

    cursor.close()
    connection.commit()
    connection.autocommit = True
    connection.close()
    return None if entity is None else entity[0]


def create_use_insert(value, type):
    connection = psycopg2.connect(DB)
    connection.autocommit = False
    cursor = connection.cursor()

    cursor.execute("BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;")

    cursor.execute("""
        insert into test_call_writing_function(value, type) values (%s, %s) returning id;
    """, (value, type))
    entity_id = cursor.fetchone()[0]

    cursor.execute("""
        select id, value
        from test_call_writing_function
        where id = %s
    """, (entity_id, ))
    entity = cursor.fetchone()

    cursor.close()
    connection.commit()
    connection.autocommit = True
    connection.close()
    return None if entity is None else entity[0]


if __name__ == '__main__':
    syncdb()
    for i in range(100):
        ex = create_use_insert(time.time(), "insert") is not None
        im = create_use_procedure(time.time(), "proc") is not None
        print("%s: explicit: %s\timplicit:%s" % ("OK" if ex == im else "FAIL", ex, im))