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

Completado

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

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

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

Un volátil (valor predeterminado) puede modificar la base de datos y podría 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 hace una gran diferencia con la eficiencia con la que el optimizador de consultas lo controla.

Creación de una función

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

La sintaxis para crear una función es:

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:

• nombre: 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: nombre de argumento.
• argtype: el tipo de datos del argumento. Puede ser tipos base, compuestos o de dominio, o 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: el tipo de datos devuelto, que puede ser un tipo base, compuesto o de dominio, o hacer referencia a un tipo de columna de una 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 estar de acuerdo con el tipo de resultado implícito en 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. Se hace referencia al tipo de una columna escribiendo table_name.
• column_name - El nombre de una columna de salida en la sintaxis de RETURNS TABLE. Este parámetro declara un parámetro con nombre OUT, salvo que RETURNS TABLE también implica RETURNS SETOF.
• column_type: el tipo de datos de salida de una columna en la sintaxis de RETURNS TABLE.
• lang_name: el idioma usado 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 al optimizador de consultas sobre la función. VOLATILE es el valor predeterminado.

Llamar a una función

Una función se puede usar en una consulta pasando los parámetros pertinentes a ella. Por ejemplo:

SELECT myfunction(3), CatID, CatName
    FROM myCats

Funciones integradas

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

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

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

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

• Cadena (tipo texto)
• FROM start (tipo entero)
• FOR count (tipo entero)

La subcadena devuelve parte del texto de entrada, comenzando en el carácter inicial y deteniéndose después de cantidad de caracteres. 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 (subcadena):

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

Nota

Si está familiarizado con las funciones, habrá observado que la primera versión usa palabras clave en lugar de comas para separar argumentos. PostgreSQL proporciona ambas versiones de estas funciones.