Scripting PostgreSQL storage functions in third-party languages

In PostgreSQL, all the storage function requirements can be implemented using Plpgsql. It also supports the use of third-party languages to write, which depends on which aspect of your proficiency.
However, it is important to note that plpgsql efficiency is more efficient than other third-party languages.
For example, a simple storage function that inserts 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 $;

Now it's time to insert the 100W record, which takes about 27 seconds.

t_girl=# Select Insert_plpgsql (1000000); Insert_plpgsql----------------(1 row) time:27286.668 ms

We use Python instead to implement
Before writing the Python script, make sure the system is loaded with 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)

Here is the function body:

CREATE OR REPLACE FUNCTION ytt.insert_py (f_num integer) RETURNS void LANGUAGE Plpythonuas $ytt $import datetimeimport Rando Mi = 0while i < f_num:    V_rank = Random.randrange (0,100)    v_log_time = Datetime.datetime.now ()-Datetime.timede LTA (Days=random.randrange (0,50))    query0 = "INSERT into YTT.T1 (rank,log_time) VALUES (" + str (v_rank) + ", '" + STR (v_l Og_time) + "')"    Plpy.execute (query0)    i + = 1$ytt$;

Clears the table T1.
Also insert 100W record, time difference is obvious, the program written in Python is 3 times times slower than the language inside 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 the insert ... values instead. ()... () way.
Here is the function body:

CREATE OR REPLACE FUNCTION ytt.insert_multi_py (f_num integer, f_values integer) RETURNS text LANGUAGE Plpythonuas $ytt $imp Ort Datetimeimport Randomi = 0j = 0query0 = "INSERT into YTT.T1 (rank,log_time) values" DATA0 = "if (f_num/f_values) *f_val UEs < 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 $;

Clears the table T1.
Continue inserting 100W data, at which time the insertion time coincides with the original plpgsql.

t_girl=# Select Insert_multi_py (1000000,20);    Insert_multi_py