Introducing support for Pgrouting and PLV8 extensions in PostgreSQL Flexible Server

[Gennadyk]

Azure Database for PostgreSQL - Flexible Server supports over 50 PostgreSQL extensions.  We are also constantly adding new extensions to expand on the functionality provided by the PostgreSQL engine. This is why we are announcing support for two more popular Postgres extensions in Flex Server - pgroutng and plv8.

While many developers are aware of PostGIS as geo routing extension for Postgres , not many aware of pgrouting, which can be thought about as extension for PostGIS. What pgRouting does is extend PostGIS's functionality by providing you with several functions based on popular network algorithms to solve problems like the shortest path and the traveling salesperson problem. To use pgRouting postGIS needs to be installed as well. 

With extension allow-list setup by server parameters, you can specify which specific extensions can be installed on a PostgreSQL DB instance. 

To use pgRouting in Flexible Server you will first need to allow-list it: 

Using the Azure portal:

1. Select your Azure Database for PostgreSQL - Flexible Server.
2. On the sidebar, select Server Parameters.
3. Search for the azure.extensions parameter.
4. Select pgrouting extension to allow-list.

After that is done, and we added this extension to shared_preload_libraries we can install the extensions:

 CREATE EXTENSION postgis;
CREATE EXTENSION pgrouting;

Now you can use pgrouting functions like Shortest Path Dijkstra method to find a shortest route or pgr_TSP to use travelling salesperson problem (TSP)  algorithm.  Pgrouting docs are available here. 

PLV8 is a shared library that provides a PostgreSQL procedural language powered by V8 Javascript Engine. With this program you can write in Javascript your function that is callable from SQL. As with pgrouting you will need to allow-list and add this extension to shared_preload_libraries before installing it. 

CREATE EXTENSION plv8;

Enterprises, small and medium businesses, as well as ISVs can now accelerate the development and deployments of their LOB applications on the Postgres Flexible service. This helps shorten the time to market.

Plv8 is a trusted extension.  That is concept introduced fully in PostgreSQL 13. Why are some extensions are marked as trusted and some are not? Trusted concept allows for a superuser to specify extensions that a user can install in their database so long as they have a CREATE privilege. 

Creating a function using PL/v8 looks like any other PostgreSQL function, with the exception of a language specifier change. Take the (basic) example below: taking in two arrays we are returning combined values as JSON string:

CREATE OR REPLACE FUNCTION plv8_test(keys text[], vals text[])
RETURNS text AS $$
  var o = {};
  for(var i=0; i<keys.length; i++){
    o[keys[i]] = vals[i];
  }
  return JSON.stringify(o);
$$ LANGUAGE plv8 IMMUTABLE STRICT;

SELECT plv8_test(ARRAY['name', 'age'], ARRAY['Tom', '29']);
         plv8_test
---------------------------
 {"name":"Tom","age":"29"}

For more on what can be done with PLV8 see docs here. 

We would encourage you to try and leverage both pgrouting and plv8 extension in Azure Database for PostgreSQL - Flexible Server to unlock new scenarios and NoSQL capabilities of PostgreSQL.  Learn more about Azure Database for PostgreSQL - Flexible Server in the overview and supported extensions.

 Additional information on topics discussed above can be found in following documents:

1. PostgreSQL extensions
2. Pgrouting project 

We’re always eager to get your feedback, so please reach out via email to Ask Azure DB for PostgreSQL.