postgres extension - ghdrako/doc_snipets GitHub Wiki

A PostgreSQL extension is a set of SQL objects that you can add to PostgreSQL catalogs. Installing and enabling an extension can be done at run-time, making deploying extensions as simple as typing a single SQL command. Any SQL object can be part of an extension in example (Stored procedures ,Data type, Operator, operator class, operator family,Index access method)

Managing extensions

Every extension is managed at a database level, meaning that every database that needs an extension must manage such an extension life cycle. In other words, there is no per-cluster way of managing an extension and applying it to every database within the cluster. Extensions are mainly managed by three SQL statements: CREATE EXTENSION, DROP EXTENSION, and ALTER EXTENSION, to respectively install an extension in a database, remove the extension from the database, and modify extension attributes or upgrade them.

Creating own extension

Extension in Zig:

Extension in Rust:

CREATE EXTENSION [ IF NOT EXISTS ] extension_name  
   [ WITH ] [ SCHEMA schema_name ]  
            [ VERSION version ]  
            [ CASCADE ]

If the extension depends on any other extension, the CASCADE option allows the system to automatically execute a recursive CREATE EXTENSION for the dependency. You can decide which schema the extension objects must be placed into, and of course, that makes sense only for such extensions that can be relocated.

You will need to prepare the following files:

  • Makefile, if you need to “build” your files, which is mostly necessary when writing an extension in C
  • Control file, to describe the extension properties
  • SQL script that is played to install the extension objects, such as tables, views, functions, stored procedures, operators, data types, etc.
  • SQL upgrade scripts to go from one version to the next

Install

t is possible to check whether an extension has already been made available to your PostgreSQL instance with the following SQL query:

table pg_available_extensions;

To make pg_trm installable in PostgreSQL we have to install the proper contrib package

$ sudo apt-get install postgresql-contrib-14

create extension pg_trgm;

list the object contained in the pg_trgm extension

 \dx+ pg_trgm

list version extension installed in a database

SELECT name, version  FROM pg_available_extension_versions  WHERE name = 'pg_stat_statements';

Viewing installed extensions

\dx

SELECT x.extname, x.extversion, n.nspname  
  FROM pg_extension x 
  JOIN pg_namespace n  ON n.oid = x.extnamespace;

Altering an existing extension

The ALTER EXTENSION statement is very rich and complex and allows you to fully modify an existing extension. The statement allows four main changes to an existing extension:

  • Upgrading the extension to a new version
  • Setting the schema of a relocatable extension
  • Adding a database object to the extension
  • Removing a database object from the extension

upgrading

CREATE EXTENSION  pg_stat_statements WITH VERSION '1.6';
ALTER EXTENSION pg_stat_statements  UPDATE TO '1.10';
\dx pg_stat_statements

Moving a relocatable extension from one schema to another

ALTER EXTENSION pg_stat_statements SET SCHEMA my_schema;
dx pg_stat_statements

That will move all the extension objects into the my_schema schema, which has to exist before the extension is relocated.

example script configures the pg_stat_statements extension

#!/bin/bash
# set PostgreSQL environment: PGHOME and PGDATA
# Check if pg_stat_statements extension is available
extstat=$(psql -t -A -c "select name from pg_available_extensions where name='pg_stat_statements';")
if [ "$extstat" != "pg_stat_statements" ]; then
  echo "Install Contrib package"
else
  # Create the pg_stat_statements extension
  psql -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"
  # Add the pg_stat_statements extension settings to postgresql.conf
  echo "shared_preload_libraries = 'pg_stat_statements'" >> $PGDATA/postgresql.conf
  echo "pg_stat_statements.max = 10000" >> $PGDATA/postgresql.conf
  echo "pg_stat_statements.track = all" >> $PGDATA/postgresql.conf
  # Restart PostgreSQL to apply changes
  pg_ctl restart -D $PGDATA
  # Load sample data
  pgbench -i postgres
  psql -c "select query, total_exec_time,rows from pg_stat_statements
  limit 2;"
fi