Cette section explicite les différences entre le PL/pgSQL de PostgreSQL™ et le langage PL/SQL d'Oracle, afin d'aider les développeurs qui portent des applications d'Oracle® vers PostgreSQL™.
PL/pgSQL est similaire à PL/SQL sur de nombreux aspects. C'est un langage itératif structuré en blocs et toutes les variables doivent être déclarées. Les assignations, boucles, conditionnelles sont similaires. Les principales différences que vous devez garder à l'esprit quand vous portez de PL/SQL vers PL/pgSQL sont:
Vous ne pouvez pas utiliser des noms de paramètres identiques aux colonnes qui sont référencées dans la fonction. Oracle vous permet de le faire si vous qualifiez le nom du paramètre en utilisant nom_fonction.nom_paramètre. Dans PL/pgSQL, vous pouvez éviter un conflit en qualifiant le nom de colonne ou de table.
Il n'y a pas de valeurs par défaut pour les paramètres dans PostgreSQL™.
Vous pouvez surcharger les fonctions dans PostgreSQL™. C'est souvent utilisé pour contourner le manque de paramètres par défaut.
Pas besoin de curseurs dans PL/pgSQL, mettez juste la requête dans l'instruction FOR (voir Exemple 37.6, « Portage d'une fonction qui crée une autre fonction de PL/SQL vers PL/pgSQL »).
Dans PostgreSQL™, le corps de la fonction doit être écrit comme une chaîne litérale. Du coup, vous avez besoin d'utiliser les guillemets dollar ou l'échappement des simples guillemets dans le corps de la fonction. Voir la Section 37.2.1, « Utilisation des guillemets simples (quotes) ».
À la place des paquetages, utilisez des schémas pour organiser vos fonctions en groupes.
Comme il n'y a pas de paquetages, il n'y a pas non plus de variables au niveau paquetage. Ceci est un peu ennuyant. Vous pourriez être capable de conserver un état par session dans les tables temporaires à la place.
L'Exemple 37.5, « Portage d'une fonction simple de PL/SQL vers PL/pgSQL » montre comment porter une simple fonction de PL/SQL vers PL/pgSQL.
Exemple 37.5. Portage d'une fonction simple de PL/SQL vers PL/pgSQL
Voici une fonction en PL/SQL Oracle™ :
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;
Parcourons cette fonction et voyons les différences avec PL/pgSQL :
Le mot clé RETURN dans le prototype de la fonction (pas dans le corps de la fonction) devient RETURNS dans PostgreSQL. De plus, IS devient AS et vous avez besoin d'ajouter une clause LANGUAGE parce que PL/pgSQL n'est pas le seul langage de procédures disponible.
Dans PostgreSQL™, le corps de la fonction est considéré comme une chaîne littérale, donc vous avez besoin d'utiliser les guillemets simples ou les guillemets dollar tout autour. Ceci se substitue au / de fin dans l'approche d'Oracle.
La commande show errors n'existe pas dans PostgreSQL™ et n'est pas nécessaire car les erreurs sont rapportées automatiquement.
Voici de quoi aurait l'air cette fonction portée sous 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;
L'Exemple 37.6, « Portage d'une fonction qui crée une autre fonction de PL/SQL vers PL/pgSQL » montre comment porter une fonction qui crée une autre fonction et comment gérer les problèmes de guillemets résultants.
Exemple 37.6. Portage d'une fonction qui crée une autre fonction de PL/SQL vers PL/pgSQL
La procédure suivante récupère des lignes d'une instruction SELECT et construit une grande fonction dont les résultats sont dans une instruction IF pour favoriser l'efficacité. Notez particulièrement les différences dans le curseur et la boucle FOR.
Voici la version 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;
Voici comment la fonction serait dans PostgreSQL™ :
CREATE OR REPLACE FUNCTION cs_update_referrer_type_proc() RETURNS void AS $func$ DECLARE referrer_key RECORD; -- declare a generic record to be used in a FOR func_body text; func_cmd text; BEGIN func_body := 'BEGIN' ; -- Remarquez comment nous parcourons les résultats d'une requête dans une boucle FOR -- en utilisant la construction FOR <record>. FOR referrer_key IN SELECT * FROM cs_referrer_keys ORDER BY try_order 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;
Notez comment le corps de la fonction est construit séparément et est passé au travers de quote_literal pour doubler tout symbole guillemet qu'il peut contenir. Cette technique est nécessaire parce que nous ne pouvons pas utiliser à coup sûr les guillemets dollar pour définir la nouvelle fonction : nous ne sommes pas sûr de savoir quelle chaîne sera interpolée à partir du champ referrer_key.key_string (nous supposons ici que ce referrer_key.kind vaut à coup sûr host, domain ou url mais referrer_key.key_string pourrait valoir autre chose, il pourrait contenir en particulier des signes dollar). Cette fonction est en fait une amélioration de l'original Oracle parce qu'il ne génèrera pas de code cassé quand referrer_key.key_string ou referrer_key.referrer_type contient des guillemets.
L'Exemple 37.7, « Portage d'une procédure avec manipulation de chaînes et paramètres OUT de PL/SQL vers PL/pgSQL » montre comment porter une fonction ayant des paramètres OUT et effectuant des manipulations de chaînes. PostgreSQL™ n'a pas de fonction instr intégrée mais vous pouvez en créer une en utilisant une combinaison d'autres fonctions. Dans la Section 37.11.3, « Annexe », il y a une implémentation PL/pgSQL d'instr que vous pouvez utiliser pour faciliter votre portage.
Exemple 37.7. Portage d'une procédure avec manipulation de chaînes et paramètres OUT de PL/SQL vers PL/pgSQL
La procédure Oracle™ suivante est utilisée pour analyser une URL et renvoyer plusieurs éléments (hôte, chemin et requête). Les fonctions PL/pgSQL ne peuvent renvoyer qu'une seule valeur.
Voici la version Oracle :
CREATE OR REPLACE PROCEDURE cs_parse_url( v_url IN VARCHAR, v_host OUT VARCHAR, -- Celle-ci sera passée en retour v_path OUT VARCHAR, -- Celle-là aussi v_query OUT VARCHAR) -- Et celle-là 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;
Voici une traduction possible en 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;
Cette fonction pourrait être utilisée ainsi :
SELECT * FROM cs_parse_url('http://foobar.com/query.cgi?baz');
L'Exemple 37.8, « Portage d'une procédure de PL/SQL vers PL/pgSQL » montre comment porter une procédure qui utilise de nombreuses fonctionnalités spécifiques à Oracle.
Exemple 37.8. Portage d'une procédure de PL/SQL vers PL/pgSQL
La version Oracle :
CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id IN INTEGER) IS a_running_job_count INTEGER; PRAGMA AUTONOMOUS_TRANSACTION; 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 COMMIT; -- free lock 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; -- ne vous inquietez pas si cela existe déjà END; COMMIT; END; / show errors
Les procédures comme celles-ci peuvent être aisément converties en fonctions PostgreSQL™ renvoyant un void. Cette procédure en particulier est intéressante parce qu'elle peut nous apprendre diverses choses :
Il n'y a pas d'instruction PRAGMA dans PostgreSQL™. |
|
Si vous faites un LOCK TABLE dans PL/pgSQL, le verrou ne sera pas libéré jusqu'à ce que la transaction appelante soit terminée. |
|
Vous ne pouvez pas lancer un COMMIT dans une fonction PL/pgSQL. La fonction est lancée à l'intérieur d'une transaction externe et, du coup, un COMMIT impliquerait simplement la fin de l'exécution de la fonction. Néanmoins, dans ce cas particulier, ce n'est de toute façon pas nécessaire parce que le verrou obtenu par LOCK TABLE sera libéré lors de la levée de l'erreur. |
Voici comment nous pourrions porter cette procédure vers PL/pgSQL :
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'; 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 -- ne vous inquietez pas si cela existe déjà END; END; $$ LANGUAGE plpgsql;
La syntaxe de RAISE est considérablement différente de l'instruction Oracle similaire. |
|
Les noms d'exceptions supportées par PL/pgSQL sont différents de ceux d'Oracle. L'ensemble de noms d'exceptions intégré est plus important (voir l'Annexe A, Codes d'erreurs de PostgreSQL™). Il n'existe actuellement pas de façon de déclarer des noms d'exceptions définis par l'utilisateur. |
La principale différence fonctionnelle entre cette procédure et l'équivalent Oracle est que le verrou exclusif sur la table cs_jobs sera détenu jusqu'à la fin de la transaction appelante. De plus, si l'appelant annule plus tard (par exemple à cause d'une erreur), les effets de cette procédure seront annulés.
Cette section explique quelques autres choses à surveiller quand on effectue un portage de fonctions PL/SQL Oracle vers PostgreSQL.
Dans PL/pgSQL, quand une exception est récupérée par une clause EXCEPTION, toutes les modifications de la base de données depuis le bloc BEGIN sont automatiquement annulées. C'est-à-dire que le comportement est identique à celui obtenu à partir d'Oracle avec
BEGIN SAVEPOINT s1; ... code ici ... EXCEPTION WHEN ... THEN ROLLBACK TO s1; ... code ici ... WHEN ... THEN ROLLBACK TO s1; ... code ici ... END;
Si vous traduisez une procédure d'Oracle qui utilise SAVEPOINT et ROLLBACK TO dans ce style, votre tâche est facile : omettez SAVEPOINT et ROLLBACK TO . Si vous avez une procédure qui utilise SAVEPOINT et ROLLBACK TO d'une façon différente, alors un peu de réflexion supplémentaire sera nécessaire.
La version PL/pgSQL d' EXECUTE fonctionne de façon similaire à la version PL/SQL mais vous devez vous rappeler d'utiliser quote_literal et quote_ident comme décrit dans la Section 37.6.5, « Exécuter des commandes dynamiques ». Les constructions de type EXECUTE 'SELECT * FROM $1'; ne fonctionneront pas de façon fiable à moins d'utiliser ces fonctions.
PostgreSQL™ vous donne deux modificateurs de création de fonctions pour optimiser l'exécution : la « volatilité » (la fonction renvoie toujours le même résultat quand on lui donne les mêmes arguments) et la « rigueur » (une fonction renvoie NULL si tous ses arguments sont NULL). Consultez la page de référence de CREATE FUNCTION pour les détails.
Pour faire usage de ces attributs d'optimisation, votre instruction CREATE FUNCTION devrait ressembler à ceci :
CREATE FUNCTION foo(...) RETURNS integer AS $$ ... $$ LANGUAGE plpgsql STRICT IMMUTABLE;
Cette section contient le code d'un ensemble de fonctions instr compatible Oracle que vous pouvez utiliser pour simplifier vos efforts de portage.
-- -- fonctions instr qui reproduisent la contrepartie Oracle -- Syntaxe: instr(string1, string2, [n], [m]) où [] signifie paramètre optionnel. -- -- Cherche string1 en commençant par le n-ième caractère pour la m-ième occurrence -- de string2. Si n est négatif, cherche en sens inverse. Si m n'est pas fourni -- suppose 1 (la recherche commence au premier caractère). -- 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_nombre 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_nombre := occur_nombre + 1; IF occur_nombre = occur_index THEN RETURN beg; END IF; END IF; beg := beg - 1; END LOOP; RETURN 0; END IF; END; $$ LANGUAGE plpgsql STRICT IMMUTABLE;