Esta sección explica las diferencias entre el lenguaje PL/pgSQL de PostgreSQL y el lenguaje PL/SQL de Oracle, para ayudar a los desarrolladores que migran aplicaciones de Oracle® a PostgreSQL.
PL/pgSQL es similar a PL/SQL en muchos aspectos. Es un lenguaje imperativo, estructurado en bloques, y todas las variables deben ser declaradas. Las asignaciones, bucles y condicionales son similares. Las principales diferencias que debes tener en cuenta al migrar de PL/SQL a PL/pgSQL son:
Si un nombre utilizado en un comando SQL puede ser tanto el nombre de una columna de una
tabla utilizada en el comando como una referencia a una variable de la función,
PL/SQL lo trata como un nombre de columna.
Por defecto, PL/pgSQL lanzará un error
quejándose de que el nombre es ambiguo. Puedes especificar
plpgsql.variable_conflict = use_column
para cambiar este comportamiento y coincidir con PL/SQL,
como se explica en Section 41.11.1.
A menudo es mejor evitar tales ambigüedades en primer lugar,
pero si tienes que migrar una gran cantidad de código que depende de
este comportamiento, configurar variable_conflict puede ser la
mejor solución.
En PostgreSQL, el cuerpo de la función debe escribirse como un literal de cadena. Por lo tanto, debes usar comillas de dólar (dollar quoting) o escapar las comillas simples en el cuerpo de la función. (Consulta Section 41.12.1.)
Los nombres de los tipos de datos a menudo necesitan traducción. Por ejemplo, en Oracle los
valores de cadena se declaran comúnmente como de tipo varchar2, que
es un tipo no estándar de SQL. En PostgreSQL,
usa en su lugar el tipo varchar o text. Del mismo modo, reemplaza
el tipo number por numeric, o usa algún otro tipo de datos numérico
si hay uno más apropiado.
En lugar de paquetes, usa esquemas para organizar tus funciones en grupos.
Dado que no hay paquetes, tampoco hay variables a nivel de paquete. Esto es algo molesto. Puedes mantener el estado por sesión en tablas temporales en su lugar.
Los bucles FOR enteros con REVERSE funcionan
de manera diferente: PL/SQL cuenta hacia abajo desde el segundo
número hasta el primero, mientras que PL/pgSQL cuenta hacia abajo
desde el primero hasta el segundo, lo que requiere que se intercambien los límites del bucle
al migrar. Esta incompatibilidad es desafortunada
pero es poco probable que se cambie. (Consulta Section 41.6.5.5.)
Los bucles FOR sobre consultas (distintos de los cursores) también funcionan
de manera diferente: las variables de destino deben haber sido declaradas,
mientras que PL/SQL siempre las declara implícitamente.
Una ventaja de esto es que los valores de las variables siguen siendo accesibles
después de salir del bucle.
Existen varias diferencias de notación para el uso de variables de cursor.
El Example 41.9 muestra cómo migrar una función simple de PL/SQL a PL/pgSQL.
Example 41.9. Migración de una función simple de PL/SQL a PL/pgSQL
Aquí tienes una función PL/SQL de Oracle:
CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar2,
v_version varchar2)
RETURN varchar2 IS
BEGIN
IF v_version IS NULL THEN
RETURN v_name;
END IF;
RETURN v_name || '/' || v_version;
END;
/
show errors;
Repasemos esta función y veamos las diferencias en comparación con PL/pgSQL:
El nombre del tipo varchar2 tiene que cambiarse a varchar
o text. En los ejemplos de esta sección,
usaremos varchar, pero text suele ser una mejor opción si
no necesitas límites específicos de longitud de cadena.
La palabra clave RETURN en el prototipo
de la función (no en el cuerpo de la función) se convierte en
RETURNS en
PostgreSQL.
Además, IS se convierte en AS, y debes
agregar una cláusula LANGUAGE porque PL/pgSQL
no es el único lenguaje de funciones posible.
En PostgreSQL, el cuerpo de la función se considera
un literal de cadena, por lo que debes usar comillas o comillas de dólar
alrededor de él. Esto sustituye la barra / final
del enfoque de Oracle.
El comando show errors no existe en
PostgreSQL, y no es necesario ya que los errores se
reportan automáticamente.
Así es como se vería esta función al ser migrada 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;
El Example 41.10 muestra cómo migrar una función que crea otra función y cómo manejar los problemas de entrecomillado resultantes.
Example 41.10. Migración de una función que crea otra función de PL/SQL a PL/pgSQL
El siguiente procedimiento extrae filas de una
sentencia SELECT y construye una función grande
con los resultados en sentencias IF, por razones
de eficiencia.
Esta es la versión de 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 VARCHAR2,
v_domain IN VARCHAR2, v_url IN VARCHAR2) RETURN VARCHAR2 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;
Aquí tienes cómo quedaría esta función en PostgreSQL:
CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc() 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;
Observa cómo el cuerpo de la función se construye por separado y se pasa
a través de quote_literal para duplicar las comillas que contenga. Esta
técnica es necesaria porque no podemos usar de forma segura las comillas de dólar para
definir la nueva función: no sabemos con certeza qué cadenas se
interpolarán desde el campo referrer_key.key_string.
(Asumimos aquí que se puede confiar en que referrer_key.kind siempre
será host, domain o
url, pero referrer_key.key_string podría ser
cualquier cosa; en particular, podría contener signos de dólar). Esta función
es en realidad una mejora con respecto a la original de Oracle, porque no
generará código roto cuando referrer_key.key_string o
referrer_key.referrer_type contengan comillas.
El Example 41.11 muestra cómo migrar una función
con parámetros OUT y manipulación de cadenas.
PostgreSQL no tiene una función
instr incorporada, pero puedes crear una
combinando otras funciones. En el Section 41.13.3 hay una
implementación en PL/pgSQL de
instr que puedes usar para facilitar tu migración.
Example 41.11. Migración de un procedimiento con manipulación de cadenas y
parámetros OUT de PL/SQL a
PL/pgSQL
El siguiente procedimiento de Oracle PL/SQL se utiliza para analizar una URL y devolver varios elementos (host, ruta y consulta).
Esta es la versión de Oracle:
CREATE OR REPLACE PROCEDURE cs_parse_url(
v_url IN VARCHAR2,
v_host OUT VARCHAR2, -- Esto se devolverá
v_path OUT VARCHAR2, -- Esto también
v_query OUT VARCHAR2) -- Y esto también
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;
Aquí tienes una posible traducción a PL/pgSQL:
CREATE OR REPLACE FUNCTION cs_parse_url(
v_url IN VARCHAR,
v_host OUT VARCHAR, -- Esto se devolverá
v_path OUT VARCHAR, -- Esto también
v_query OUT VARCHAR) -- Y esto también
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;
Esta función se podría utilizar así:
SELECT * FROM cs_parse_url('http://foobar.com/query.cgi?baz');
El Example 41.12 muestra cómo migrar un procedimiento que utiliza numerosas características específicas de Oracle.
Example 41.12. Migración de un procedimiento de PL/SQL a PL/pgSQL
La versión de Oracle:
CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id IN INTEGER) IS
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
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, now());
EXCEPTION
WHEN dup_val_on_index THEN NULL; -- don't worry if it already exists
END;
COMMIT;
END;
/
show errors
Así es como podríamos migrar este procedimiento a PL/pgSQL:
CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id integer) 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
COMMIT; -- free lock
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)
-- no preocuparse si ya existe
END;
COMMIT;
END;
$$ LANGUAGE plpgsql;
La sintaxis de | |
Los nombres de excepción soportados por PL/pgSQL son diferentes de los de Oracle. El conjunto de nombres de excepción incorporados es mucho mayor (consulta Appendix A). Actualmente no hay una forma de declarar nombres de excepción definidos por el usuario, aunque en su lugar puedes lanzar valores SQLSTATE elegidos por el usuario. |
Esta sección explica algunas otras cosas a tener en cuenta al migrar funciones de Oracle PL/SQL a PostgreSQL.
En PL/pgSQL, cuando una excepción es capturada por una
cláusula EXCEPTION, todos los cambios en la base de datos desde el
BEGIN del bloque se revierten automáticamente. Es decir, el comportamiento
es equivalente a lo que obtendrías en Oracle con:
BEGIN
SAVEPOINT s1;
... código aquí ...
EXCEPTION
WHEN ... THEN
ROLLBACK TO s1;
... código aquí ...
WHEN ... THEN
ROLLBACK TO s1;
... código aquí ...
END;
Si estás traduciendo un procedimiento de Oracle que utiliza
SAVEPOINT y ROLLBACK TO de este estilo,
tu tarea es fácil: simplemente omite el SAVEPOINT y el
ROLLBACK TO. Si tienes un procedimiento que utiliza
SAVEPOINT y ROLLBACK TO de una manera diferente,
entonces se requerirá un análisis más detallado.
EXECUTE #
La versión de PL/pgSQL de
EXECUTE funciona de manera similar a la de
PL/SQL, pero debes recordar usar
quote_literal y
quote_ident como se describe en Section 41.5.4. Las construcciones del
tipo EXECUTE 'SELECT * FROM $1'; no funcionarán
de manera confiable a menos que uses estas funciones.
PostgreSQL te ofrece dos modificadores de creación de funciones para optimizar la ejecución: “volatilidad” (si la función siempre devuelve el mismo resultado cuando se le dan los mismos argumentos) y “estrictez” (si la función devuelve null si algún argumento es null). Consulta la página de referencia de CREATE FUNCTION para obtener más detalles.
Al hacer uso de estos atributos de optimización, tu
sentencia CREATE FUNCTION podría verse
así:
CREATE FUNCTION foo(...) RETURNS integer AS $$ ... $$ LANGUAGE plpgsql STRICT IMMUTABLE;
Esta sección contiene el código para un conjunto de funciones
instr compatibles con Oracle que puedes utilizar para simplificar
tus esfuerzos de migración.
--
-- funciones instr que imitan a su contraparte de Oracle
-- Sintaxis: instr(cadena1, cadena2 [, n [, m]])
-- donde [] denota parámetros opcionales.
--
-- Busca cadena2 en cadena1, comenzando en el n-ésimo carácter, para la m-ésima
-- aparición de cadena2. Si n es negativo, busca hacia atrás, comenzando en el
-- carácter abs(n) desde el final de cadena1.
-- Si no se pasa n, se asume 1 (la búsqueda comienza en el primer carácter).
-- Si no se pasa m, se asume 1 (encuentra la primera aparición).
-- Devuelve el índice inicial de cadena2 en cadena1, o 0 si no se encuentra cadena2.
--
CREATE FUNCTION instr(varchar, varchar) RETURNS integer AS $$
BEGIN
RETURN instr($1, $2, 1);
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;
CREATE FUNCTION instr(string varchar, string_to_search_for 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_for IN temp_str);
IF pos = 0 THEN
RETURN 0;
ELSE
RETURN pos + beg_index - 1;
END IF;
ELSIF beg_index < 0 THEN
ss_length := char_length(string_to_search_for);
length := char_length(string);
beg := length + 1 + beg_index;
WHILE beg > 0 LOOP
temp_str := substring(string FROM beg FOR ss_length);
IF string_to_search_for = temp_str THEN
RETURN beg;
END IF;
beg := beg - 1;
END LOOP;
RETURN 0;
ELSE
RETURN 0;
END IF;
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;
CREATE FUNCTION instr(string varchar, string_to_search_for 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 occur_index <= 0 THEN
RAISE 'argument ''%'' is out of range', occur_index
USING ERRCODE = '22003';
END IF;
IF beg_index > 0 THEN
beg := beg_index - 1;
FOR i IN 1..occur_index LOOP
temp_str := substring(string FROM beg + 1);
pos := position(string_to_search_for IN temp_str);
IF pos = 0 THEN
RETURN 0;
END IF;
beg := beg + pos;
END LOOP;
RETURN beg;
ELSIF beg_index < 0 THEN
ss_length := char_length(string_to_search_for);
length := char_length(string);
beg := length + 1 + beg_index;
WHILE beg > 0 LOOP
temp_str := substring(string FROM beg FOR ss_length);
IF string_to_search_for = temp_str 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;
ELSE
RETURN 0;
END IF;
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;