Inside PostgreSQL Extensions

Extensions and SQL objects

Any SQL object can be part of an extension, and here’s a short list of common objects found in popular extensions:

• Stored procedures
• Data type
• Operator, operator class, operator family
• Index access method

Example: Installing the pg_trgm extension

As an example, we install the pg_trgm contrib extension and have a look at what it contains:

create extension pg_trgm;

Now the extension is enabled in our database, and it’s possible to list the object contained in the pg_trgm extension thanks to the psql command \dx+ pg_trgm. Here’s the output of the command:

                                  Objects in extension "pg_trgm"
                                        Object description                                        
══════════════════════════════════════════════════════════════════════════════════════════════════
 function gin_extract_query_trgm(text,internal,smallint,internal,internal,internal,internal)
 function gin_extract_value_trgm(text,internal)
 function gin_trgm_consistent(internal,smallint,text,integer,internal,internal,internal,internal)
 function gin_trgm_triconsistent(internal,smallint,text,integer,internal,internal,internal)
 function gtrgm_compress(internal)
 function gtrgm_consistent(internal,text,smallint,oid,internal)
 function gtrgm_decompress(internal)
 function gtrgm_distance(internal,text,smallint,oid,internal)
 function gtrgm_in(cstring)
 function gtrgm_out(gtrgm)
 function gtrgm_penalty(internal,internal,internal)
 function gtrgm_picksplit(internal,internal)
 function gtrgm_same(gtrgm,gtrgm,internal)
 function gtrgm_union(internal,internal)
 function set_limit(real)
 function show_limit()
 function show_trgm(text)
 function similarity(text,text)
 function similarity_dist(text,text)
 function similarity_op(text,text)
 function word_similarity(text,text)
 function word_similarity_commutator_op(text,text)
 function word_similarity_dist_commutator_op(text,text)
 function word_similarity_dist_op(text,text)
 function word_similarity_op(text,text)
 operator %(text,text)
 operator %>(text,text)
 operator <%(text,text)
 operator <->(text,text)
 operator <->>(text,text)
 operator <<->(text,text)
 operator class gin_trgm_ops for access method gin
 operator class gist_trgm_ops for access method gist
 operator family gin_trgm_ops for access method gin
 operator family gist_trgm_ops for access method gist
 type gtrgm
(36 rows)

The functions listed here are stored procedures, and in this extension, they happen to be written in C. Then, we see several new operators, such as %, which implements a similarity test.

Glue objects

The operator class and operator family entries can be considered glue objects. They register index access methods covering the operators provided in the PostgreSQL catalogs so that the planner is capable of deciding to use a new index.

Determination

The extension implements a new data type that is also implemented in C and installed at runtime without having to recompile the PostgreSQL server, or even restart it in this case.

Try it yourself

The service for PostgreSQL has been started in the playground. We’re using opendata database. We’ll perform two tasks in the following playground.

1. Create the extension using the following syntax: