For the purposes of this demo, I am going to add two extensions. The first is ‘earthdistance‘, which can calculate distances between two points of latitude and longitude on the Earth’s surface. It does have a dependency on another extension however. This is the ‘cube‘ extension which is a data type for multidimensional cubes. Let’s begin by first listing all available extensions. Then we will install the ‘earthdistance‘ and ‘cube‘ extensions before finally using their functions to calculate a distance between two points on the Earth’s surface.
dbaas=> SELECT * from pg_available_extensions; name | default_version | installed_version | comment --------------------+-----------------+-------------------+----------------------------------------------------------------------------------------------- refint | 1.0 | | functions for implementing referential integrity (obsolete) lo | 1.1 | | Large Object maintenance pgautofailover | 2.0 | | pg_auto_failover pg_surgery | 1.0 | | extension to perform surgery on a damaged relation moddatetime | 1.0 | | functions for tracking last modification time pg_walinspect | 1.0 | | functions to inspect contents of PostgreSQL Write-Ahead Log dblink | 1.2 | | connect to other PostgreSQL databases from within a database intagg | 1.1 | | integer aggregator and enumerator (obsolete) tsm_system_time | 1.0 | | TABLESAMPLE method which accepts time in milliseconds as a limit bloom | 1.0 | | bloom access method - signature file based index tablefunc | 1.0 | | functions that manipulate whole tables, including crosstab pgstattuple | 1.5 | | show tuple-level statistics earthdistance | 1.1 | | calculate great-circle distances on the surface of the Earth file_fdw | 1.0 | | foreign-data wrapper for flat file access isn | 1.2 | | data types for international product numbering standards pgaudit | 1.7 | | provides auditing functionality xml2 | 1.1 | | XPath querying and XSLT btree_gist | 1.7 | | support for indexing common datatypes in GiST pg_prewarm | 1.2 | | prewarm relation data tsm_system_rows | 1.0 | | TABLESAMPLE method which accepts number of rows as a limit citext | 1.6 | | data type for case-insensitive character strings unaccent | 1.1 | | text search dictionary that removes accents dict_int | 1.0 | | text search dictionary template for integers btree_gin | 1.3 | | support for indexing common datatypes in GIN intarray | 1.5 | | functions, operators, and index support for 1-D arrays of integers insert_username | 1.0 | | functions for tracking who changed a table sslinfo | 1.2 | | information about SSL certificates adminpack | 2.1 | | administrative functions for PostgreSQL orafce | 3.25 | | Functions and operators that emulate a subset of functions and packages from the Oracle RDBMS hstore | 1.8 | | data type for storing sets of (key, value) pairs pgcrypto | 1.3 | | cryptographic functions cube | 1.5 | | data type for multidimensional cubes postgres_fdw | 1.1 | | foreign-data wrapper for remote PostgreSQL servers dict_xsyn | 1.0 | | text search dictionary template for extended synonym processing seg | 1.4 | | data type for representing line segments or floating-point intervals autoinc | 1.0 | | functions for autoincrementing fields amcheck | 1.3 | | functions for verifying relation integrity old_snapshot | 1.0 | | utilities in support of old_snapshot_threshold pg_freespacemap | 1.2 | | examine the free space map (FSM) pageinspect | 1.11 | | inspect the contents of database pages at a low level ltree | 1.2 | | data type for hierarchical tree-like structures pg_visibility | 1.2 | | examine the visibility map (VM) and page-level visibility info pg_buffercache | 1.3 | | examine the shared buffer cache plpgsql | 1.0 | 1.0 | PL/pgSQL procedural language pg_stat_statements | 1.10 | 1.10 | track planning and execution statistics of all SQL statements executed tcn | 1.0 | | Triggered change notifications fuzzystrmatch | 1.1 | | determine similarities and distance between strings pg_trgm | 1.6 | | text similarity measurement and index searching based on trigrams pgrowlocks | 1.2 | | show row-level locking information uuid-ossp | 1.1 | | generate universally unique identifiers (UUIDs) (50 rows)
The extensions that we are interested in are earthdistance and cube, as highlighted in blue above. We use the “dms_create_extension” UDF to install them. Note that if we try to add the earthdistance extension prior to adding cube, we get the following dependency failure:
dbaas=> select dms_create_extension('earthdistance'); NOTICE: Running create extension sql: CREATE EXTENSION IF NOT EXISTS earthdistance ERROR: required extension "cube" is not installed HINT: Use CREATE EXTENSION ... CASCADE to install required extensions too. CONTEXT: SQL statement "CREATE EXTENSION IF NOT EXISTS earthdistance" PL/pgSQL function dms_create_extension(text) line 6 at EXECUTE
To add these extensions to our database, run these commands in the following order.
dbaas=> select dms_create_extension('cube'); NOTICE: Running create extension sql: CREATE EXTENSION IF NOT EXISTS cube dms_create_extension ---------------------- (1 row) dbaas=> select dms_create_extension('earthdistance'); NOTICE: Running create extension sql: CREATE EXTENSION IF NOT EXISTS earthdistance dms_create_extension ---------------------- (1 row)
And that is how simple it is to install PostgreSQL extensions in a database that has been provisioned by DSM as the ‘dbaas’ user. Again, ‘dbaas’ is not a superuser like ‘postgres’, which is a design decision based on security concerns.
You can now examine the installed extensions by using the \dx command.
dbaas=> \dx List of installed extensions Name | Version | Schema | Description --------------------+---------+------------+------------------------------------------------------------------------ cube | 1.5 | public | data type for multidimensional cubes earthdistance | 1.1 | public | calculate great-circle distances on the surface of the Earth plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language (3 rows)
There are also UDFs to update and drop any extensions that have been added to a database.
We can now test the functions. As per the official documentation for the earthdistance extension, the function ll_to_earth() returns the location of a point on the surface of the Earth given its latitude and longitude in degrees. The function earth_distance() returns the great circle distance between two points on the surface of the Earth. So, for example, if I wanted to find the distance between my home town in Ireland to say, Salto in Brazil, I would make a call similar to the following:
dbaas=> select earth_distance(ll_to_earth(51.95, -7.85056),ll_to_earth(-23.20083, -47.28694)) as distance_in_meters; distance_in_meters -------------------- 9204656.163251864 (1 row)
The value returned is in meters. If I wanted to display kilometers or miles, I would simply add a divide by 1000 or divide by 1609.344 accordingly to the result.
dbaas=> select earth_distance(ll_to_earth(51.95, -7.85056),ll_to_earth(-23.20083, -47.28694)) / 1000 as distance_in_kms; distance_in_kms ------------------- 9204.656163251864 (1 row) dbaas=> select earth_distance(ll_to_earth(51.95, -7.85056),ll_to_earth(-23.20083, -47.28694)) / 1609.344 as distance_in_miles; distance_in_miles ------------------- 5719.508174294535 (1 row) dbaas=>
And there we have it. This is a very simple example, but hopefully it demonstrates how easy it is to add PostgreSQL extensions to a PostgreSQL database that has been provisioned via VMware Data Services Manager. There is no need to grant superuser privileges to users to install extensions, which could be a security concern. It can be achieved through user-defined functions that we have specifically added to the PostgreSQL database template. A much safer approach.