Creating TLE extensions for Aurora PostgreSQL

To create the example TLE extension

The following steps use an example database named labdb. This database is owned by the postgres primary user. The postgres role also has the permissions of the pgtle_admin role.

1. Use psql to connect to the writer instance of your Aurora PostgreSQL DB cluster.

   psql --host=db-instance-123456789012.aws-region.rds.amazonaws.com
   --port=5432 --username=postgres --password --dbname=labdb

2. Create a TLE extension named pg_distance by copying the following code and pasting it into your psql session console.

   SELECT pgtle.install_extension
   (
    'pg_distance',
    '0.1',
     'Distance functions for two points',
   $_pg_tle_$
       CREATE FUNCTION dist(x1 float8, y1 float8, x2 float8, y2 float8, norm int)
       RETURNS float8
       AS $$
         SELECT (abs(x2 - x1) ^ norm + abs(y2 - y1) ^ norm) ^ (1::float8 / norm);
       $$ LANGUAGE SQL;
   
       CREATE FUNCTION manhattan_dist(x1 float8, y1 float8, x2 float8, y2 float8)
       RETURNS float8
       AS $$
         SELECT dist(x1, y1, x2, y2, 1);
       $$ LANGUAGE SQL;
   
       CREATE FUNCTION euclidean_dist(x1 float8, y1 float8, x2 float8, y2 float8)
       RETURNS float8
       AS $$
         SELECT dist(x1, y1, x2, y2, 2);
       $$ LANGUAGE SQL;
   $_pg_tle_$
   );

   You see the output, such as the following.

   install_extension
   ---------------
    t
   (1 row)

   The artifacts that make up the pg_distance extension are now installed in your database. These artifacts include the control file and the code for the extension, which are items that need to be present so that the extension can be created using the CREATE EXTENSION command. In other words, you still need to create the extension to make its functions available to database users.

3. To create the extension, use the CREATE EXTENSION command as you do for any other extension. As with other extensions, the database user needs to have the CREATE permissions in the database.

   CREATE EXTENSION pg_distance;

4. To test the pg_distance TLE extension, you can use it to calculate the Manhattan distance between four points.

   labdb=> SELECT manhattan_dist(1, 1, 5, 5);
   8

   To calculate the Euclidean distance between the same set of points, you can use the following.

   labdb=> SELECT euclidean_dist(1, 1, 5, 5);
   5.656854249492381

To update an existing TLE extension and specify the default version

1. Connect to the writer instance of your Aurora PostgreSQL DB cluster using psql or another client tool, such as pgAdmin.

   psql --host=db-instance-123456789012.aws-region.rds.amazonaws.com
   --port=5432 --username=postgres --password --dbname=labdb

2. Modify the existing TLE extension by copying the following code and pasting it into your psql session console.

   SELECT pgtle.install_update_path
   (
    'pg_distance',
    '0.1',
    '0.2',
   $_pg_tle_$
       CREATE OR REPLACE FUNCTION dist(x1 float8, y1 float8, x2 float8, y2 float8, norm int)
       RETURNS float8
       AS $$
         SELECT (abs(x2 - x1) ^ norm + abs(y2 - y1) ^ norm) ^ (1::float8 / norm);
       $$ LANGUAGE SQL IMMUTABLE PARALLEL SAFE;
   
       CREATE OR REPLACE FUNCTION manhattan_dist(x1 float8, y1 float8, x2 float8, y2 float8)
       RETURNS float8
       AS $$
         SELECT dist(x1, y1, x2, y2, 1);
       $$ LANGUAGE SQL IMMUTABLE PARALLEL SAFE;
   
       CREATE OR REPLACE FUNCTION euclidean_dist(x1 float8, y1 float8, x2 float8, y2 float8)
       RETURNS float8
       AS $$
         SELECT dist(x1, y1, x2, y2, 2);
       $$ LANGUAGE SQL IMMUTABLE PARALLEL SAFE;
   $_pg_tle_$
   );

   You see a response similar to the following.

   install_update_path
   ---------------------
    t
   (1 row)

   You can make this version of the extension the default version, so that database users don't have to specify a version when they create or update the extension in their database.

3. To specify that the modified version (version 0.2) of your TLE extension is the default version, use the pgtle.set_default_version function as shown in the following example.

   SELECT pgtle.set_default_version('pg_distance', '0.2');

   For more information about this function, see pgtle.set_default_version.

4. With the code in place, you can update the installed TLE extension in the usual way, by using ALTER EXTENSION ... UPDATE command, as shown here:

   ALTER EXTENSION pg_distance UPDATE;