Compile PostgreSQL storage functions in a third-party Language

In PostgreSQL, all storage function requirements can be implemented using PLPGSQL. At the same time, it also supports writing in a third-party language. This depends on your skills.
Note that PLPGSQL is more efficient than other third-party languages.
For example, a simple storage function for inserting a table:

CREATE OR REPLACE FUNCTION ytt.insert_plpgsql(f_num integer) RETURNS void LANGUAGE plpgsqlAS $ytt$declare i int := 0;              v_rank int := 0;      v_log_time timestamp;beginwhile i < f_num loop    v_rank = ceil(random()*100);    v_log_time = now() - '1 day'::interval*ceil(random()*50);    insert into t1 (rank,log_time) values (v_rank,v_log_time);    i = i + 1;end loop;end;$ytt$;

It takes about 27 seconds to insert records.

t_girl=# select insert_plpgsql(1000000); insert_plpgsql ---------------- (1 row)Time: 27286.668 ms

We use python for implementation.
Before writing a python script, make sure that the system has loaded the plpythonu extension.

t_girl=# \dx plpythonu                        List of installed extensions   Name    | Version |   Schema   |               Description                -----------+---------+------------+------------------------------------------ plpythonu | 1.0     | pg_catalog | PL/PythonU untrusted procedural language(1 row)

The following is the function body:

CREATE OR REPLACE FUNCTION ytt.insert_py(f_num integer) RETURNS void LANGUAGE plpythonuAS $ytt$import datetimeimport randomi = 0while i < f_num:    v_rank = random.randrange(0,100)    v_log_time = datetime.datetime.now() - datetime.timedelta(days=random.randrange(0,50))    query0 = "insert into ytt.t1 (rank,log_time) values (" + str(v_rank) + ",'" + str(v_log_time)+ "')"    plpy.execute(query0)    i += 1$ytt$;

Clear table t1.
Insert records. At this time, the difference in time is obvious. The program write efficiency in python is three times slower than that in the database.

t_girl=# select insert_py(1000000); insert_py ----------- (1 row)Time: 86061.558 ms

You can modify the above python program to make it more efficient and close to the system. We use insert... values... () instead.
The following is the function body:

CREATE OR REPLACE FUNCTION ytt.insert_multi_py(f_num integer, f_values integer) RETURNS text LANGUAGE plpythonuAS $ytt$import datetimeimport randomi = 0j = 0query0 = "insert into ytt.t1(rank,log_time) values "data0 = ''if (f_num/f_values)*f_values < f_num:    return 'Parameters should be times relation.(f_num:1000,f_values:10)'else:    while i < int(f_num/f_values):        j = 0        while j < f_values:            v_rank = random.randrange(0,100)            v_log_time = datetime.datetime.now() - datetime.timedelta(days=random.randrange(0,50))            data0 = data0 + ",(" + str(v_rank) + ",'" + str(v_log_time)+ "')"            j += 1        result0 = query0 + data0[1:len(data0)]        plpy.execute(result0)        data0 = ''        i += 1    return 'Inserting ' + str(f_num) + ' rows'$ytt$;

Clear table t1.
Continue to insert data records. At this time, the insertion time is the same as that of the original PLPGSQL.

t_girl=# select insert_multi_py(1000000,20);    insert_multi_py     ------------------------ Inserting 1000000 rows(1 row)Time: 27587.715 mst_girl=#