Creazione di TLE estensioni per SQL

È possibile installare tutte le estensioni con cui si crea TLE in qualsiasi su cui è installata l'estensione. SQL pg_tle L'pg_tleestensione è limitata al database SQL Postgre in cui è installata. Le estensioni che crei utilizzando TLE hanno come ambito lo stesso database.

Usa le varie pgtle funzioni per installare il codice che compone l'TLEestensione. Le seguenti funzioni di Trusted Language Extensions richiedono tutte il ruolo pgtle_admin.

• pgtle.install_extension

• pgtle.install_update_path

• pgtle.register_feature

• pgtle.register_feature_if_not_exists

• pgtle.set_default_version

• pgtle.uninstall_extension(name)

• pgtle.uninstall_extension(name, version)

• pgtle.uninstall_extension_if_exists

• pgtle.uninstall_update_path

• pgtle.uninstall_update_path_if_exists

• pgtle.unregister_feature

• pgtle.unregister_feature_if_exists

Esempio: creazione di un'estensione linguistica affidabile utilizzando SQL

L'esempio seguente mostra come creare un'TLEestensione denominata pg_distance che contiene alcune SQL funzioni per il calcolo delle distanze utilizzando formule diverse. Nell'elenco, puoi trovare la funzione per il calcolo della distanza di Manhattan e la funzione per il calcolo della distanza euclidea. Per ulteriori informazioni sulla differenza tra queste formule, consulta Taxicab geometry (Geometria del taxi) e Euclidean geometry (Geometria euclidea) in Wikipedia.

È possibile utilizzare questo esempio nel proprio per l'istanza Postgre SQL DB se l'pg_tleestensione è configurata come descritto in. Configurazione di Trusted Language Extensions nel SQL

Nota

È necessario disporre dei privilegi del ruolo pgtle_admin per seguire questa procedura.

Per creare l'estensione di esempio TLE

I passaggi seguenti utilizzano un database di esempio denominato labdb. Questo database è di proprietà dell'utente postgres principale. Il ruolo postgres dispone anche delle autorizzazioni del ruolo pgtle_admin.

1. Utilizzalo psql per connetterti DB. RDSper l'istanza DB di SQL Postgre.

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

2. Crea un'TLEestensione denominata pg_distance copiando il codice seguente e incollandolo nella console di sessione. psql

   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_$
   );

   Viene visualizzato l'output riportato di seguito.

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

   Gli artefatti che costituiscono l'estensione pg_distance sono ora installati nel database. Questi artefatti includono il file di controllo e il codice dell'estensione, che devono essere presenti in modo che l'estensione possa essere creata utilizzando il comando CREATE EXTENSION. In altre parole, è comunque necessario creare l'estensione per rendere le funzioni disponibili agli utenti del database.

3. Per creare l'estensione, usa il comando CREATE EXTENSION come per qualsiasi altra estensione. Come per altre estensioni, l'utente del database deve disporre delle autorizzazioni CREATE nel database.

   CREATE EXTENSION pg_distance;

4. Per testare l'pg_distanceTLEestensione, puoi usarla per calcolare la distanza da Manhattan tra quattro punti.

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

   Per calcolare la distanza euclidea tra lo stesso set di punti, puoi usare quanto segue.

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

L'estensione pg_distance carica le funzioni nel database e le rende disponibili a tutti gli utenti con le autorizzazioni per il database.

Modificare l'estensione TLE

Per migliorare le prestazioni delle query per le funzioni incluse in questa TLE estensione, aggiungi i seguenti due SQL attributi Postgre alle relative specifiche.

• IMMUTABLE: l'attributo IMMUTABLE garantisce che l'ottimizzatore di query possa utilizzare le ottimizzazioni per migliorare i tempi di risposta delle query. Per ulteriori informazioni, consulta le categorie di volatilità delle funzioni nella documentazione di Postgre. SQL

• PARALLEL SAFE— L'PARALLEL SAFEattributo è un altro attributo che consente a Postgre SQL di eseguire la funzione in modalità parallela. Per ulteriori informazioni, consulta la documentazione CREATEFUNCTIONdi SQL Postgre.

Nell'esempio seguente, puoi vedere come viene utilizzata la pgtle.install_update_path funzione per aggiungere questi attributi a ciascuna funzione per creare una versione 0.2 dell'pg_distanceTLEestensione. Per ulteriori informazioni su questa funzione, consulta pgtle.install_update_path. È necessario avere il ruolo pgtle_admin necessario per eseguire questa operazione.

Per aggiornare un'TLEestensione esistente e specificare la versione predefinita

1. Connect per l'istanza Postgre SQL DB utilizzando psql un altro strumento client, ad esempio. pgAdmin

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

2. Modifica l'TLEestensione esistente copiando il codice seguente e incollandolo nella console di sessione. psql

   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_$
   );

   Viene visualizzata una risposta simile alla seguente.

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

   È possibile impostare questa versione dell'estensione come versione predefinita, in modo che gli utenti del database non debbano specificare una versione quando creano o aggiornano l'estensione nel database.

3. Per specificare che la versione modificata (versione 0.2) dell'TLEestensione è la versione predefinita, utilizzate la pgtle.set_default_version funzione come illustrato nell'esempio seguente.

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

   Per ulteriori informazioni su questa funzione, consulta pgtle.set_default_version.

4. Una volta installato il codice, è possibile aggiornare l'TLEestensione installata nel modo consueto, utilizzando il ALTER EXTENSION ... UPDATE comando, come illustrato di seguito:

   ALTER EXTENSION pg_distance UPDATE;