Una buena forma de desarrollar en PL/pgSQL es utilizar el editor de texto de tu
elección para crear tus funciones y, en otra ventana, utilizar psql para cargar
y probar esas funciones. Si lo haces de esta manera, es una buena idea escribir la función utilizando
CREATE OR REPLACE FUNCTION. De esta manera, solo tienes que recargar el archivo para actualizar
la definición de la función. Por ejemplo:
CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $$
....
$$ LANGUAGE plpgsql;
Mientras ejecutas psql, puedes cargar o recargar dicho archivo de definición de función con:
\i nombre_archivo.sql
y luego emitir inmediatamente comandos SQL para probar la función.
Otra buena forma de desarrollar en PL/pgSQL es con una herramienta GUI de acceso a bases de datos que facilite el desarrollo en un lenguaje procedimental. Un ejemplo de dicha herramienta es pgAdmin, aunque existen otras. Estas herramientas a menudo proporcionan características convenientes como el escape de comillas simples y facilitan la recreación y depuración de funciones.
El código de una función PL/pgSQL se especifica en CREATE FUNCTION
como un literal de cadena. Si escribes el literal de cadena de la manera habitual con comillas simples circundantes,
cualquier comilla simple dentro del cuerpo de la función debe duplicarse; del mismo modo, cualquier barra invertida
debe duplicarse (asumiendo que se utiliza la sintaxis de escape de cadenas). Duplicar comillas es, en el mejor de los
casos, tedioso, y en casos más complicados el código puede volverse francamente incomprensible, porque puedes
encontrarte fácilmente necesitando media docena o más de comillas adyacentes. Se recomienda que, en su lugar,
escribas el cuerpo de la función como un literal de cadena con “comillas de dólar” (dollar-quoted)
(consulta la Section 4.1.2.4). En el enfoque de comillas de dólar, nunca duplicas las
comillas, sino que te aseguras de elegir un delimitador de comillas de dólar diferente para cada nivel de anidamiento
que necesites. Por ejemplo, podrías escribir el comando CREATE FUNCTION como:
CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS $PROC$
....
$PROC$ LANGUAGE plpgsql;
Dentro de esto, puedes usar comillas para cadenas literales simples en comandos SQL y $$ para
delimitar fragmentos de comandos SQL que estés ensamblando como cadenas. Si necesitas entrecomillar texto que
incluye $$, puedes usar $Q$, y así sucesivamente.
La siguiente tabla muestra lo que tienes que hacer al escribir comillas sin comillas de dólar. Puede ser útil al traducir código anterior a las comillas de dólar a algo más comprensible.
Para comenzar y finalizar el cuerpo de la función, por ejemplo:
CREATE FUNCTION foo() RETURNS integer AS '
....
' LANGUAGE plpgsql;
En cualquier parte dentro de un cuerpo de función delimitado por comillas simples, las comillas deben aparecer en parejas.
Para literales de cadena dentro del cuerpo de la función, por ejemplo:
a_output := ''Blah''; SELECT * FROM users WHERE f_name=''foobar'';
En el enfoque de comillas de dólar, simplemente escribirías:
a_output := 'Blah'; SELECT * FROM users WHERE f_name='foobar';
que es exactamente lo que vería el analizador de PL/pgSQL en cualquier caso.
Cuando necesitas una sola comilla en una constante de cadena dentro del cuerpo de la función, por ejemplo:
a_output := a_output || '' AND name LIKE ''''foobar'''' AND xyz''
El valor realmente añadido a a_output sería:
AND name LIKE 'foobar' AND xyz.
In the dollar-quoting approach, you'd write:
a_output := a_output || $$ AND name LIKE 'foobar' AND xyz$$
being careful that any dollar-quote delimiters around this are not
just $$.
When a single quotation mark in a string inside the function body is adjacent to the end of that string constant, for example:
a_output := a_output || '' AND name LIKE ''''foobar''''''
The value appended to a_output would then be:
AND name LIKE 'foobar'.
In the dollar-quoting approach, this becomes:
a_output := a_output || $$ AND name LIKE 'foobar'$$
When you want two single quotation marks in a string constant (which accounts for 8 quotation marks) and this is adjacent to the end of that string constant (2 more). You will probably only need that if you are writing a function that generates other functions, as in Example 41.10. For example:
a_output := a_output || '' if v_'' ||
referrer_keys.kind || '' like ''''''''''
|| referrer_keys.key_string || ''''''''''
then return '''''' || referrer_keys.referrer_type
|| ''''''; end if;'';
The value of a_output would then be:
if v_... like ''...'' then return ''...''; end if;
In the dollar-quoting approach, this becomes:
a_output := a_output || $$ if v_$$ || referrer_keys.kind || $$ like '$$
|| referrer_keys.key_string || $$'
then return '$$ || referrer_keys.referrer_type
|| $$'; end if;$$;
where we assume we only need to put single quote marks into
a_output, because it will be re-quoted before use.
Para ayudarte a encontrar casos de problemas simples pero comunes antes
de que causen daños, PL/pgSQL proporciona
comprobaciones adicionales. Cuando están habilitadas, según la configuración, se
pueden usar para emitir un WARNING o un ERROR
durante la compilación de una función. Una función que haya recibido
un WARNING se puede ejecutar sin producir más mensajes,
por lo que se recomienda probar en un entorno de desarrollo independiente.
Se recomienda configurar plpgsql.extra_warnings, o
plpgsql.extra_errors, según corresponda, en "all"
en entornos de desarrollo y/o pruebas.
Estas comprobaciones adicionales se habilitan a través de las variables de configuración
plpgsql.extra_warnings para las advertencias y
plpgsql.extra_errors para los errores. Ambas se pueden configurar con
una lista de comprobaciones separadas por comas, "none" o
"all". El valor por defecto es "none". Actualmente,
la lista de comprobaciones disponibles incluye:
shadowed_variables #Comprueba si una declaración oculta (shadows) una variable definida previamente.
strict_multi_assignment #
Algunos comandos de PL/pgSQL permiten asignar
valores a más de una variable a la vez, como
SELECT INTO. Normalmente, el número de variables de destino
y el número de variables de origen deberían coincidir, aunque
PL/pgSQL utilizará NULL
para los valores faltantes y las variables adicionales se ignorarán. Habilitar esta
comprobación hará que PL/pgSQL lance un
WARNING o ERROR siempre que el
número de variables de destino y el número de variables de origen sean
diferentes.
too_many_rows #
Habilitar esta comprobación hará que PL/pgSQL
verifique si una consulta determinada devuelve más de una fila cuando se
utiliza una cláusula INTO. Como una sentencia
INTO solo usará una fila, el hecho de que una consulta devuelva múltiples
filas es generalmente ineficiente y/o no determinista y,
por lo tanto, probablemente sea un error.
El siguiente ejemplo muestra el efecto de configurar plpgsql.extra_warnings
como shadowed_variables:
SET plpgsql.extra_warnings TO 'shadowed_variables';
CREATE FUNCTION foo(f1 int) RETURNS int AS $$
DECLARE
f1 int;
BEGIN
RETURN f1;
END;
$$ LANGUAGE plpgsql;
WARNING: variable "f1" shadows a previously defined variable
LINE 3: f1 int;
^
CREATE FUNCTION
El siguiente ejemplo muestra los efectos de configurar
plpgsql.extra_warnings como
strict_multi_assignment:
SET plpgsql.extra_warnings TO 'strict_multi_assignment'; CREATE OR REPLACE FUNCTION public.foo() RETURNS void LANGUAGE plpgsql AS $$ DECLARE x int; y int; BEGIN SELECT 1 INTO x, y; SELECT 1, 2 INTO x, y; SELECT 1, 2, 3 INTO x, y; END; $$; SELECT foo(); WARNING: number of source and target fields in assignment does not match DETAIL: strict_multi_assignment check of extra_warnings is active. HINT: Make sure the query returns the exact list of columns. WARNING: number of source and target fields in assignment does not match DETAIL: strict_multi_assignment check of extra_warnings is active. HINT: Make sure the query returns the exact list of columns. foo ----- (1 row)