Using gp_url_tools in Yandex MPP Analytics for PostgreSQL

The gp_url_tools extension enables you to encode URLs/URIs into a format supported by web browsers and to decode them back to the original format:

• encode_url(text): Encoding a URL.
• decode_url(text): Decoding a URL.
• encode_uri(text): Encoding a URI.
• decode_uri(text): Decoding a URI.

Installing the gp_url_tools extension in a Greenplum® clusterInstalling the gp_url_tools extension in a Greenplum® cluster

1. Connect to the database as the owner or a user with the CREATE permission in the database and run this command:

   CREATE EXTENSION gp_url_tools;
   

   Installing the extension creates a schema named url_tools_schema and adds encoding and decoding functions to it.

2. Make sure that the extension is installed:

   SELECT extname FROM pg_extension;
   

Use casesUse cases

1. Create a table named companies:

   CREATE TABLE companies (
       id int NOT NULL,
       name varchar (50),
       site text,
       contact text,
       PRIMARY KEY (id)
   );
   

2. Insert data into the table:

   INSERT INTO companies VALUES
       (1, ‘Sever-1', 'http://север-1.рф/новости', 'mailto:офис@север-1.рф'),
       (2, ‘East Coast', 'east-coast.ru/about', 'mailto:sale@east-cost.ru');
   

3. Encode the website and email addresses:

   UPDATE companies SET 
       site = url_tools_schema.encode_url(site),
       contact = url_tools_schema.encode_uri(contact);
   

4. Check the result:

   SELECT name, site, contact FROM companies;
   

5. Decode the website and email addresses:

   UPDATE companies SET 
       site = url_tools_schema.decode_url(site),
       contact = url_tools_schema.decode_uri(contact);
   

6. Check the result:

   SELECT name, site, contact FROM companies;
   

Greenplum® and Greenplum Database® are registered trademarks or trademarks of Broadcom Inc. in the United States and/or other countries.