Documentazione di PostgreSQL 9.0 > Programmazione del server > PL/pgSQL - Linguaggio procedurale SQL > Porting da PL/SQL di Oracle
PrecedenteConsigli per lo sviluppo in PL/pgSQLPL/Tcl - Tcl Procedural LanguageSuccessivo

39.12. Porting da PL/SQL di Oracle

Questa sezione spiega le differenze tra il linguaggio PL/pgSQL di PostgreSQL™ e il linguaggio PL/SQL di Oracle, per aiutare di sviluppatori che portano applicazioni da Oracle® a PostgreSQL™.

PL/pgSQL è simile a PL/SQL in molti aspetti. Esso è un linguaggio strutturato a blocchi, imperativo, e tutte le variabili devono essere dichiarate. Assegnamenti, cicli e condizioni sono simili. Le differenze principali che si dovrebbero tenere in mente quando si fa il porting da PL/SQL a PL/pgSQL sono:

  • Se un nome usato in un comando SQL può essere sia il nome di una colonna o di una tabella sia un riferimento a una variabile della funzione, PL/SQL lo tratta come nome di una colonna. Questo corrisponde al comportamento plpgsql.variable_conflict = use_column di PL/pgSQL, che non è il modo predefinito, come spiegato in Sezione 39.10.1, «Sostituzione di variabili». Spesso è meglio evitare queste ambiguità fin da subito, ma se si deve portare una grande quantità di codice che usa questo comportamento, impostare variable_conflict potrebbe essere la migliore soluzione.

  • In PostgreSQL™ il corpo della funzione deve essere scritto come una stringa letterale. Perciò è necessario usare la quotazione con dollari o fare l'escape dei singoli apici nel corpo della funzione. (Si veda Sezione 39.11.1, «Handling of Quotation Marks».)

  • Invece di pacchetti, usare gli schemi per organizzare in gruppi le funzioni.

  • Dato che non ci sono pacchetti, non ci sono nemmeno variabili a livello di pacchetto. Questo è abbastanza irritante. È possibile invece mantenere gli stati della sessione in tabelle temporanee.

  • I cicli FOR di interi con REVERSE funzionano diversamente: PL/SQL conta all'indietro dal secondo numero al primo, mentre PL/pgSQL conta all'indietro dal primo numero al secondo, richiedendo che i limiti del ciclo siano invertiti quando si fa il porting. Questa incompatibilità è spiacevole ma probabilmente non sarà cambiata. (Si veda Sezione 39.6.3.5, «FOR (variante intera)».)

  • Anche i cicli FOR sulle query (all'infuori che i cursori) funzionano diversamente: la variabile/i deve essere stata dichiarata, mentre PL/SQL li dichiara sempre implicitamente. Un vantaggio di questo è che i valori della variabile sono comunque accessibili dopo l'uscita dal ciclo.

  • Ci sono varie differenze di notazione per l'uso di variabili cursore.

39.12.1. Esempi di porting

Esempio 39.6, «Portare una funzione semplice da PL/SQL a PL/pgSQL» mostra come portare una funzione semplice da PL/SQL a PL/pgSQL.

Esempio 39.6. Portare una funzione semplice da PL/SQL a PL/pgSQL

Ecco una funzione OraclePL/SQL:

CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar,
                                                  v_version varchar)
RETURN varchar IS
BEgin
    IF v_version IS NULL THEN
        RETURN v_name;
    END IF;
    RETURN v_name || '/' || v_version;
END;
/
show errors;

Guardiamo questa funzione per vedere le differenze comparate a PL/pgSQL:

  • La parola chiave RETURN nel prototipo della funzione (non nel corpo della funzione) diventa RETURNS in PostgreSQL™. Inoltre, IS diventa AS, e c'è bisogno di aggiungere una clausola LANGUAGE dato che PL/pgSQL non è l'unico linguaggio possibile per la funzione.

  • In PostgreSQL™, il corpo della funzione viene cosiderato essere una stringa letterale, così è necessario usare le virgolette o la quotazione con dollari attorno ad essa. Questo sostituisce la / terminante nell'approccio Oracle.

  • Il comando show errors non esiste in PostgreSQL™, e non è necessario dato che gli errori sono riportati automaticamente.

Così è come apparirebbe la funzione quando portata a PostgreSQL™:

CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar,
                                                  v_version varchar)
RETURNS varchar AS $$
BEgin
    IF v_version IS NULL THEN
        RETURN v_name;
    END IF;
    RETURN v_name || '/' || v_version;
END;
$$ LANGUAGE plpgsql;


Esempio 39.7, «Portare una funzione che crea un'altra funzione da PL/SQL a PL/pgSQL» mostra come portare una funzione che crea un'altra funzione e come gestire i conseguenti problemi di quotazione.

Esempio 39.7. Portare una funzione che crea un'altra funzione da PL/SQL a PL/pgSQL

La procedura seguente prende righe da un'istruzione SELECT e costruisce una grande funzione con i risultati in istruzioni IF, nell'interesse dell'efficienza.

Questa è la versione Oracle:

CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc IS
    CURSOR referrer_keys IS
        SELECT * FROM cs_referrer_keys
        ORDER BY try_order;
    func_cmd VARCHAR(4000);
BEGIN
    func_cmd := 'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host IN VARCHAR,
                 v_domain IN VARCHAR, v_url IN VARCHAR) RETURN VARCHAR IS BEgin';

    FOR referrer_key IN referrer_keys LOOP
        func_cmd := func_cmd ||
          ' IF v_' || referrer_key.kind
          || ' LIKE ''' || referrer_key.key_string
          || ''' THEN RETURN ''' || referrer_key.referrer_type
          || '''; END IF;';
    END LOOP;

    func_cmd := func_cmd || ' RETURN NULL; END;';

    EXECUTE IMMEDIATE func_cmd;
END;
/
show errors;

Ecco come questa funzione diventerebbe in PostgreSQL™:

CREATE OR REPLACE FUNCTION cs_update_referrer_type_proc() RETURNS void AS $func$
DECLARE
    referrer_keys CURSOR IS
        SELECT * FROM cs_referrer_keys
        ORDER BY try_order;
    func_body text;
    func_cmd text;
BEgin
    func_body := 'BEgin';

    FOR referrer_key IN referrer_keys LOOP
        func_body := func_body ||
          ' IF v_' || referrer_key.kind
          || ' LIKE ' || quote_literal(referrer_key.key_string)
          || ' THEN RETURN ' || quote_literal(referrer_key.referrer_type)
          || '; END IF;' ;
    END LOOP;

    func_body := func_body || ' RETURN NULL; END;';

    func_cmd :=
      'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host varchar,
                                                        v_domain varchar,
                                                        v_url varchar)
        RETURNS varchar AS '
      || quote_literal(func_body)
      || ' LANGUAGE plpgsql;' ;

    EXECUTE func_cmd;
END;
$func$ LANGUAGE plpgsql;

Notare come corpo della funzione sia costruito separatamente e passato attraverso quote_literal per raddoppiare qualsiasi virgoletta. Questa tecnica è necessario perchè non è possibile usare la quotazione con dollari in sicurezza per definire la nuova funzione: non sappiamo con sicurezza quali stringhe saranno interpolate dal campo referrer_key.key_string. (Si sta assumendo che referrer_key.kind possa essere creduto come essere sempre host, domain, o url, ma referrer_key.key_string potrebbe essere qualsiasi cosa, in particolare potrebbe contenere segni dollaro). Questa funzione attualmente è effettivamente un miglioramento rispetto a Oracle, dato che non genererà codice rotto quando referrer_key.key_string o referrer_key.referrer_type contengono virgolette.


Esempio 39.8, «Portare una procedura con manipolazione di stringhe e parametri OUT da PL/SQL a PL/pgSQL» mostra come portare una funzione con parametri OUT e manipolazione di stringhe. PostgreSQL™ non ha una funzione instr incorporata, ma si può crearla usando una combinazione di altre funzioni. In Sezione 39.12.3, «Appendice» c'è un'implementazione PL/pgSQL di instr che si più usare per rendere più facile il porting.

Esempio 39.8. Portare una procedura con manipolazione di stringhe e parametri OUT da PL/SQL a PL/pgSQL

La procedura Oracle™ PL/SQL seguente viene usata per fare il parsing di un URL e restituire diversi elementi (host, path e query).

Questa è la versione Oracle:

CREATE OR REPLACE PROCEDURE cs_parse_url(
    v_url IN VARCHAR,
    v_host OUT VARCHAR,  -- This will be passed back
    v_path OUT VARCHAR,  -- This one too
    v_query OUT VARCHAR) -- And this one
IS
    a_pos1 INTEGER;
    a_pos2 INTEGER;
BEGIN
    v_host := NULL;
    v_path := NULL;
    v_query := NULL;
    a_pos1 := instr(v_url, '//');

    IF a_pos1 = 0 THEN
        RETURN;
    END IF;
    a_pos2 := instr(v_url, '/', a_pos1 + 2);
    IF a_pos2 = 0 THEN
        v_host := substr(v_url, a_pos1 + 2);
        v_path := '/';
        RETURN;
    END IF;

    v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2);
    a_pos1 := instr(v_url, '?', a_pos2 + 1);

    IF a_pos1 = 0 THEN
        v_path := substr(v_url, a_pos2);
        RETURN;
    END IF;

    v_path := substr(v_url, a_pos2, a_pos1 - a_pos2);
    v_query := substr(v_url, a_pos1 + 1);
END;
/
show errors;

Ecco una possibile traduzione in PL/pgSQL:

CREATE OR REPLACE FUNCTION cs_parse_url(
    v_url IN VARCHAR,
    v_host OUT VARCHAR,  -- This will be passed back
    v_path OUT VARCHAR,  -- This one too
    v_query OUT VARCHAR) -- And this one
AS $$
DECLARE
    a_pos1 INTEGER;
    a_pos2 INTEGER;
BEgin
    v_host := NULL;
    v_path := NULL;
    v_query := NULL;
    a_pos1 := instr(v_url, '//');

    IF a_pos1 = 0 THEN
        RETURN;
    END IF;
    a_pos2 := instr(v_url, '/', a_pos1 + 2);
    IF a_pos2 = 0 THEN
        v_host := substr(v_url, a_pos1 + 2);
        v_path := '/';
        RETURN;
    END IF;

    v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2);
    a_pos1 := instr(v_url, '?', a_pos2 + 1);

    IF a_pos1 = 0 THEN
        v_path := substr(v_url, a_pos2);
        RETURN;
    END IF;

    v_path := substr(v_url, a_pos2, a_pos1 - a_pos2);
    v_query := substr(v_url, a_pos1 + 1);
END;
$$ LANGUAGE plpgsql;

Questa procedura può essere usata in questo modo:

SELECT * FROM cs_parse_url('http://foobar.com/query.cgi?baz');


Esempio 39.9, «Portare una procedura da PL/SQL a PL/pgSQL» mostra come portare una procedura che usa numerose caratteristiche che sono specifiche di Oracle.

Esempio 39.9. Portare una procedura da PL/SQL a PL/pgSQL

La versione Oracle:

CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id IN INTEGER) IS
    a_running_job_count INTEGER;
    PRAGMA AUTONOMOUS_TRANSACTION;1
BEgin
    LOCK TABLE cs_jobs IN EXCLUSIVE MODE;2

    SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL;

    IF a_running_job_count > 0 THEN
        COMMIT; -- free lock3
        raise_application_error(-20000,
                 'Unable to create a new job: a job is currently running.');
    END IF;

    DELETE FROM cs_active_job;
    INSERT INTO cs_active_job(job_id) VALUES (v_job_id);

    BEgin
        INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, sysdate);
    EXCEPTION
        WHEN dup_val_on_index THEN NULL; -- don't worry if it already exists
    END;
    COMMIT;
END;
/
show errors

Procedure come questa possono essere facilmente convertire in funzioni PostgreSQL™ che ritornano void. Questa procedura in particole è interessante perchè insegna alcune cose:

1

In PostgreSQL™ non c'è l'istruzione PRAGMA.

2

Se si fa una LOCK TABLE in PL/pgSQL, il lock non sarà rilasciato finchè la transazione chiamante non è finita.

3

Non è possibile specificare COMMIT in una funzione PL/pgSQL. La funzione è in esecuzione all'interno di qualche altra transazione e quindi COMMIT implicherebbe la terminazione dell'esecuzione della funzione. Comunque, in questo caso particolare non è necessaria, dato che il lock ottenuto da LOCK TABLE sarà rilasciato quando si genere un errore.

È possibile portare questa procedura a PL/pgSQL in questo modo:

CREATE OR REPLACE FUNCTION cs_create_job(v_job_id integer) RETURNS void AS $$
DECLARE
    a_running_job_count integer;
BEgin
    LOCK TABLE cs_jobs IN EXCLUSIVE MODE;

    SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL;

    IF a_running_job_count > 0 THEN
        RAISE EXCEPTION 'Unable to create a new job: a job is currently running';1
    END IF;

    DELETE FROM cs_active_job;
    INSERT INTO cs_active_job(job_id) VALUES (v_job_id);

    BEgin
        INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, now());
    EXCEPTION
        WHEN unique_violation THEN 2
            -- don't worry if it already exists
    END;
END;
$$ LANGUAGE plpgsql;

1

La sintassi di RAISE è considerevolmente diversa dall'istruzione Oracle, anche se il caso base RAISE exception_name funziona similmente.

2

I nomi delle eccezioni supportate da PL/pgSQL sono diversi da quelli di Oracle. L'insieme dei nomi di eccezione incorporate è molto più grande (si veda Appendice A, Codici di errore di PostgreSQL). Attualmente non c'è un modo per dichiarare nomi di eccezioni definite dall'utente, anche se è possibile lanciare valori SQLSTATE scelti dall'utente.

La principale differenza funzionale tra questa procedura e l'equivalente Oracle è che il lock esclusivo sulla tabella cs_jobs sarà preso finchè la transazione chiamante si completa. Inoltre, se il chiamante più tardi fallsice (per esempio a caus di un errore), gli effetti di questa procedura saranno sottoposti a rollback.


39.12.2. Altre cose da tenere di conto

Questa sezione spiega alcune altre cose da tenere di conto quando si portano funzioni PL/SQL di Oracle a PostgreSQL™.

39.12.2.1. Rollback implicito dopo eccezioni

In PL/pgSQL, quando un'eccezione viene catturata con una clausola EXCEPTION, tutti i cambiamenti al database dal blocco BEGIN vengono automaticamente sottoposti a rollback. Cioè, il comportamento è equivalente a quello che si otterrebbe in Oracle con:

BEgin
    SAVEPOINT s1;
    ... code here ...
EXCEPTION
    WHEN ... THEN
        ROLLBACK TO s1;
        ... code here ...
    WHEN ... THEN
        ROLLBACK TO s1;
        ... code here ...
END;

Se si sta traducendo una procedura Oracle che usa SAVEPOINT e ROLLBACK TO in questo stile, il compito è semplice: omettere semplicemente SAVEPOINT e ROLLBACK TO. Se si ha una procedura che usa SAVEPOINT e ROLLBACK TO in maniera differente allora sarà richiesto qualche effettivo pensiero.

39.12.2.2. EXECUTE

La versione PL/pgSQL di EXECUTE funziona similarmente alla versione PL/SQL, ma si deve ricordare di usare quote_literal e quote_ident come descritto in Sezione 39.5.4, «Eseguire comandi dinamici». Costrutti del tipo EXECUTE 'SELECT * FROM $1'; non funzioneranno correttamente finchè si usano queste fuzioni.

39.12.2.3. Ottimizzare funzioni PL/pgSQL

PostgreSQL™ fornisce due modificatori di creazione di funzioni per ottimizzare l'esecuzione: «volatility» (se la funzione restituisce sempre lo stesso risultato quando forniti gli stessi argomenti) e «strictness» (nel caso la funzione restituisca null se qualsiasi argomento è null). Consultare la pagina di riferimento CREATE FUNCTION(7) per dettagli.

Quando si fa uso di questi attributi di ottimizzazione, l'istruzione CREATE FUNCTION potrebbe somigliare a qualcosa del genere:

CREATE FUNCTION foo(...) RETURNS integer AS $$
...
$$ LANGUAGE plpgsql STRICT IMMUTABLE;

39.12.3. Appendice

Questa sezione contiene il codice per un insieme di funzioni compatibili Oracle instr che si possono usare per semplificare gli sforzi di porting.

--
-- funzioni instr che imitano la controparte Oracle
-- Sintassi: instr(string1, string2, [n], [m]) dove [] denota parametri opzionali.
--
-- Le ricerche di string1 cominciano dal n-esimo carattere per la m-esima occorrenza
-- di string2.  Se n è negativo, cerca all'indietro.  Se m non è passato,
-- assume 1 (la ricerca comincia dal primo carattere).
--

CREATE FUNCTION instr(varchar, varchar) RETURNS integer AS $$
DECLARE
    pos integer;
BEgin
    pos:= instr($1, $2, 1);
    RETURN pos;
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;


CREATE FUNCTION instr(string varchar, string_to_search varchar, beg_index integer)
RETURNS integer AS $$
DECLARE
    pos integer NOT NULL DEFAULT 0;
    temp_str varchar;
    beg integer;
    length integer;
    ss_length integer;
BEgin
    IF beg_index > 0 THEN
        temp_str := substring(string FROM beg_index);
        pos := position(string_to_search IN temp_str);

        IF pos = 0 THEN
            RETURN 0;
        ELSE
            RETURN pos + beg_index - 1;
        END IF;
    ELSE
        ss_length := char_length(string_to_search);
        length := char_length(string);
        beg := length + beg_index - ss_length + 2;

        WHILE beg > 0 LOOP
            temp_str := substring(string FROM beg FOR ss_length);
            pos := position(string_to_search IN temp_str);

            IF pos > 0 THEN
                RETURN beg;
            END IF;

            beg := beg - 1;
        END LOOP;

        RETURN 0;
    END IF;
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;


CREATE FUNCTION instr(string varchar, string_to_search varchar,
                      beg_index integer, occur_index integer)
RETURNS integer AS $$
DECLARE
    pos integer NOT NULL DEFAULT 0;
    occur_number integer NOT NULL DEFAULT 0;
    temp_str varchar;
    beg integer;
    i integer;
    length integer;
    ss_length integer;
BEgin
    IF beg_index > 0 THEN
        beg := beg_index;
        temp_str := substring(string FROM beg_index);

        FOR i IN 1..occur_index LOOP
            pos := position(string_to_search IN temp_str);

            IF i = 1 THEN
                beg := beg + pos - 1;
            ELSE
                beg := beg + pos;
            END IF;

            temp_str := substring(string FROM beg + 1);
        END LOOP;

        IF pos = 0 THEN
            RETURN 0;
        ELSE
            RETURN beg;
        END IF;
    ELSE
        ss_length := char_length(string_to_search);
        length := char_length(string);
        beg := length + beg_index - ss_length + 2;

        WHILE beg > 0 LOOP
            temp_str := substring(string FROM beg FOR ss_length);
            pos := position(string_to_search IN temp_str);

            IF pos > 0 THEN
                occur_number := occur_number + 1;

                IF occur_number = occur_index THEN
                    RETURN beg;
                END IF;
            END IF;

            beg := beg - 1;
        END LOOP;

        RETURN 0;
    END IF;
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;
Documentazione di PostgreSQL 9.0 > Programmazione del server > PL/pgSQL - Linguaggio procedurale SQL > Porting da PL/SQL di Oracle
PrecedenteConsigli per lo sviluppo in PL/pgSQLPL/Tcl - Tcl Procedural LanguageSuccessivo