Creación y uso de una función en Azure Database for PostgreSQL

• 5 minutos

Ya hemos aprendido que PostgreSQL admite diferentes lenguajes. Las funciones se pueden clasificar en cuatro tipos distintos:

• Funciones escritas en SQL.
• Funciones de lenguaje de procedimientos, escritas en un lenguaje de procedimientos compatible, como PL.pgSQL.
• Funciones internas.
• Funciones de lenguaje C.

Además, el propósito de la función también se puede clasificar como volátil, inmutable o estable.

Una función volátil (la predeterminada) puede modificar la base de datos y no devolver necesariamente el mismo resultado con los mismos parámetros de entrada cada vez. Por lo tanto, cada vez que se llama a esta función, se debe volver a evaluar.

Una función estable no puede modificar la base de datos y devuelve el mismo resultado si se pasan los mismos argumentos y se ejecutan dentro de la misma instrucción. Si se llama a esta función varias veces, el optimizador de consultas puede usar los resultados de la última vez que se llamó.

Una función inmutable no puede modificar la base de datos y devuelve los mismos resultados si se pasan los mismos argumentos, independientemente de la consulta que lo llame.

La volatilidad de una función marca una gran diferencia respecto de la eficiencia con la que el optimizador de consultas la controla.

Creación de una función

Una función devuelve un valor único y se puede usar dentro de una instrucción SELECT.

Esta es la sintaxis para crear una función:

CREATE [OR REPLACE] FUNCTION
myfunction ([inputparam] type {default})
RETURNS returntype AS
$$
SQL body
$$
LANGUAGE 'language_name';
CREATE FUNCTION

Al igual que con los procedimientos almacenados, el símbolo $$ se usa para iniciar y finalizar la cadena.

Las funciones toman los parámetros siguientes:

• name: opcionalmente, incluya el nombre del esquema.
• argmode: el modo del argumento. Puede ser IN, OUT, INOUT o VARIADIC. El valor predeterminado es IN. VARDIAC es un número indefinido de argumentos de entrada del mismo tipo, y va seguido de argumentos OUT. Los argumentos OUT e INOUT no se pueden usar junto con las notaciones RETURNS TABLE.
• argname: el nombre del argumento.
• argtype: el tipo de datos del argumento. Puede ser tipo de base, compuesto o de dominio, o bien hacer referencia a un tipo de columna de tabla. El tipo de columna se escribe como table_name.column_name%TYPE. Este tipo de datos puede ayudar a realizar una función independiente de los cambios de definición de tabla.
• t_expr: valor predeterminado (del mismo tipo) si no se especifica el parámetro. Solo los parámetros IN e INOUT tienen un valor predeterminado. Los parámetros de entrada que siguen a un parámetro con un valor predeterminado también deben tener valores predeterminados.
• rettype: tipo de datos devuelto, que puede ser un tipo de base, compuesto o de dominio, o bien hacer referencia a un tipo de columna de tabla. Si la función no devuelve un valor, especifique el tipo de valor devuelto como void. Cuando hay parámetros OUT o INOUT, se puede omitir la cláusula RETURNS. Si está presente, debe concordar con el tipo de resultado implícito según los parámetros de salida: RECORD si hay varios parámetros de salida o el mismo tipo que el parámetro de salida único. El modificador SETOF indica que la función devuelve un conjunto de elementos, en lugar de un solo elemento. Para hacer referencia al tipo de una columna, se debe escribir table_name.
• column_name: nombre de una columna de salida en la sintaxis RETURNS TABLE. Este parámetro declara un parámetro OUT con nombre, excepto que RETURNS TABLE también implique RETURNS SETOF.
• column_type: tipo de datos de una columna de salida en la sintaxis RETURNS TABLE.
• lang_name: lenguaje que se usa para escribir el procedimiento. El valor predeterminado es sql si se especifica sql_body. Puede ser sql, c, interno o el nombre de un lenguaje de procedimientos definido por el usuario, por ejemplo, plpgsql.

Use las palabras clave IMMUTABLE, STABLE o VOLATILE como sugerencia para el optimizador de consultas sobre la función. VOLATILE es el valor predeterminado.

Llamada a una función

Para usar una función en una consulta, pase los parámetros que sean pertinentes. Por ejemplo:

SELECT myfunction(3), CatID, CatName
    FROM myCats

Funciones integradas

PostgreSQL incluye muchas funciones integradas que puede usar en las consultas. Esto abarca la realización de comparaciones, la agregación de datos, funciones matemáticas, entre otros. Consulte la documentación en línea para obtener una lista completa de las funciones de PostgreSQL.

Un ejemplo de una función de cadena integrada es una subcadena.

substring (*string* text [ FROM *start* integer ] [ FOR *count* integer ] ) → text

Esta función toma tres parámetros de entrada:

• String (tipo Texto)
• FROM start (tipo Entero)
• FOR count (tipo entero)

La subcadena devuelve parte del texto de entrada, desde el carácter establecido en start y hasta el número de caracteres establecido en count. Por ejemplo:

substring('Thomas' from 2 for 3) → hom
substring('Thomas' from 3) → omas
substring('Thomas' for 2) → Th

Esta función es la misma que substr:

substr ( *string* text, *start* integer [, *count* integer ] ) → text
substr('alphabet', 3) → phabet
substr('alphabet', 3, 2) → ph