"Original" Writing 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 voidlanguage plpgsqlas $ytt $declare i int: = 0;v_rank int: = 0;v_log_time Timestamp;beginwhile i < F_numloopv_rank = Ceil (random () *100); v_log_time = Now ()-' 1 day ':: Interva L*ceil (Random () *50); insert into T1 (rank,log_time) values (v_rank,v_log_time); i = i + 1;end loop;end; $ytt $; now to insert 100W Records, It takes about 27 seconds to take time. 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 plpythonulist of installed extensionsname    | 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 voidlanguage plpythonuas $ytt $import datetimeimport Randomi = 0while I < F_num:v_rank = Random.randrange (0,100) v_log_time = Datetime.datetime.now ()-Datetime.timedelta (d Ays=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$;

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 textlanguage 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 = 0while 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 + = 1result0 = Query0 + Data0[1:len (data0)]plpy.execute (result0) data0 = ' i + = 1retu RN ' 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------------------------Inserting 1000000 rows (1 row) time:27587.715 mst_girl=#

This article is from "God, we don't see!" "Blog, be sure to keep this provenance http://yueliangdao0608.blog.51cto.com/397025/1410029