Ejemplo de CREATE FUNCTION de Postgresql

CREAR FUNCIÓN: definir una nueva función

Sinopsis

CREATE[ORREPLACE]FUNCTION
    name ([[ argmode ][ argname ] argtype [ =  default_expr ][,...]])[RETURNS rettype
      |RETURNSTABLE( column_name column_type [,...])]
  RETURNSNULLONNULL INPUT  ...

Descripción

CREATE FUNCTION define una nueva función. CREATE OR REPLACE FUNCTION creará una nueva función o reemplazará una definición existente. Para poder definir una función, el usuario debe tener la USAGE privilegio sobre el idioma.

Si se incluye un nombre de esquema, la función se crea en el esquema especificado. De lo contrario, se crea en el esquema actual. El nombre de la nueva función no debe coincidir con ninguna función o procedimiento existente con los mismos tipos de argumentos de entrada en el mismo esquema. Sin embargo, las funciones y procedimientos de diferentes tipos de argumentos pueden compartir un nombre (esto se llama sobrecarga).

Para reemplazar la definición actual de una función existente, use CREATE OR REPLACE FUNCTION. No es posible cambiar el nombre o los tipos de argumentos de una función de esta manera (si lo intentara, en realidad estaría creando una función nueva y distinta). También, CREATE OR REPLACE FUNCTION no le permitirá cambiar el tipo de retorno de una función existente. Para hacer eso, debe eliminar y volver a crear la función. (Cuando usas OUT parámetros, eso significa que no puede cambiar los tipos de ningún OUT parámetros excepto al eliminar la función).

Cuando CREATE OR REPLACE FUNCTION se utiliza para reemplazar una función existente, la propiedad y los permisos de la función no cambian. A todas las demás propiedades de la función se les asignan los valores especificados o implícitos en el comando. Debe ser propietario de la función para reemplazarla (esto incluye ser miembro del rol propietario).

Si elimina y luego vuelve a crear una función, la nueva función no es la misma entidad que la anterior; tendrá que eliminar las reglas, vistas, activadores, etc. existentes que se refieren a la función anterior. Usar CREATE OR REPLACE FUNCTION para cambiar la definición de una función sin romper los objetos que hacen referencia a la función. También, ALTER FUNCTION se puede utilizar para cambiar la mayoría de las propiedades auxiliares de una función existente.

El usuario que crea la función se convierte en el propietario de la función.

Para poder crear una función, debe tener USAGE privilegio sobre los tipos de argumentos y el tipo de retorno.

Parámetros

name

El nombre (opcionalmente calificado por esquema) de la función que se va a crear.

argmode

El modo de un argumento: IN, OUT, INOUT, o VARIADIC. Si se omite, el valor predeterminado es IN. Solamente OUT los argumentos pueden seguir un VARIADIC uno. También, OUT y INOUT Los argumentos no se pueden utilizar junto con el RETURNS TABLE notación.

argname

El nombre de un argumento. Algunos lenguajes (incluidos SQL y PL / pgSQL) le permiten usar el nombre en el cuerpo de la función. Para otros lenguajes, el nombre de un argumento de entrada es solo documentación adicional, en lo que respecta a la función en sí; pero puede usar nombres de argumentos de entrada al llamar a una función para mejorar la legibilidad (consulte la Sección 4.3). En cualquier caso, el nombre de un argumento de salida es significativo, porque define el nombre de la columna en el tipo de fila de resultados. (Si omite el nombre de un argumento de salida, el sistema elegirá un nombre de columna predeterminado).

argtype

Los tipos de datos de los argumentos de la función (opcionalmente calificados por esquema), si los hay. Los tipos de argumentos pueden ser tipos base, compuestos o de dominio, o pueden hacer referencia al tipo de columna de una tabla.

Dependiendo del lenguaje de implementación, también se puede permitir especificar “pseudo-tipos“ tal como cstring. Los pseudo-tipos indican que el tipo de argumento real está especificado de forma incompleta o está fuera del conjunto de tipos de datos SQL ordinarios.

Se hace referencia al tipo de columna escribiendo table_name.column_name%TYPE. El uso de esta función a veces puede ayudar a que una función sea independiente de los cambios en la definición de una tabla.

default_expr

Una expresión que se utilizará como valor predeterminado si no se especifica el parámetro. La expresión debe ser coercible al tipo de argumento del parámetro. Solo entrada (incluida INOUT) los parámetros pueden tener un valor predeterminado. Todos 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 de retorno (opcionalmente calificado por esquema). El tipo de retorno puede ser un tipo base, compuesto o de dominio, o puede hacer referencia al tipo de columna de una tabla. Dependiendo del lenguaje de implementación, también se puede permitir especificar “pseudo-tipos“ tal como cstring. Si se supone que la función no debe devolver un valor, especifique void como tipo de retorno.

Cuando hay OUT o INOUT parámetros, el RETURNS la cláusula se puede omitir. Si está presente, debe coincidir 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.

los SETOF El modificador indica que la función devolverá un conjunto de elementos, en lugar de un solo elemento.

Se hace referencia al tipo de columna escribiendo table_name.column_name%TYPE.

column_name

El nombre de una columna de salida en el RETURNS TABLE sintaxis. Esta es efectivamente otra forma de declarar un nombre OUT parámetro, excepto que RETURNS TABLE también implica RETURNS SETOF.

column_type

El tipo de datos de una columna de salida en el RETURNS TABLE sintaxis.

lang_name

El nombre del idioma en el que se implementa la función. Puede ser sql, c, internal, o el nombre de un lenguaje de procedimiento definido por el usuario, por ejemplo, plpgsql. Incluir el nombre entre comillas simples está obsoleto y requiere que coincidan las mayúsculas y las minúsculas.

TRANSFORM FOR TYPE type_name [, ... ] }

Deben aplicarse las listas que transforman una llamada a la función. Las transformaciones convierten entre tipos de SQL y tipos de datos específicos del idioma; ver CREAR TRANSFORMAR. Las implementaciones de lenguaje de procedimientos generalmente tienen un conocimiento codificado de los tipos integrados, por lo que no es necesario enumerarlos aquí. Si una implementación de lenguaje de procedimientos no sabe cómo manejar un tipo y no se proporciona ninguna transformación, recurrirá a un comportamiento predeterminado para convertir tipos de datos, pero esto depende de la implementación.

WINDOW

WINDOW indica que la función es una función de ventana en lugar de una función simple. Actualmente, esto solo es útil para funciones escritas en C. WINDOW El atributo no se puede cambiar al reemplazar una definición de función existente.

IMMUTABLESTABLEVOLATILE

Estos atributos informan al optimizador de consultas sobre el comportamiento de la función. Como máximo, se puede especificar una opción. Si ninguno de estos aparece, VOLATILE es la suposición predeterminada.

IMMUTABLE indica que la función no puede modificar la base de datos y siempre devuelve el mismo resultado cuando se le dan los mismos valores de argumento; es decir, no realiza búsquedas en bases de datos ni utiliza información que no esté directamente presente en su lista de argumentos. Si se da esta opción, cualquier llamada de la función con argumentos totalmente constantes se puede reemplazar inmediatamente con el valor de la función.

STABLE indica que la función no puede modificar la base de datos y que dentro de un solo escaneo de tabla devolverá consistentemente el mismo resultado para los mismos valores de argumento, pero que su resultado podría cambiar en todas las declaraciones SQL. Esta es la selección adecuada para funciones cuyos resultados dependen de búsquedas en la base de datos, variables de parámetros (como la zona horaria actual), etc. (no es apropiado para AFTER disparadores que desean consultar filas modificadas por el comando actual.) También tenga en cuenta que el current_timestamp La familia de funciones califica como estable, ya que sus valores no cambian dentro de una transacción.

VOLATILE indica que el valor de la función puede cambiar incluso dentro de un solo escaneo de tabla, por lo que no se pueden realizar optimizaciones. Relativamente pocas funciones de base de datos son volátiles en este sentido; algunos ejemplos son random(), currval(), timeofday(). Pero tenga en cuenta que cualquier función que tenga efectos secundarios debe clasificarse como volátil, incluso si su resultado es bastante predecible, para evitar que las llamadas se optimicen; un ejemplo es setval().

Para obtener más detalles, consulte Sección 37.7.

LEAKPROOF

LEAKPROOF indica que la función no tiene efectos secundarios. No revela información sobre sus argumentos más que por su valor de retorno. Por ejemplo, una función que arroja un mensaje de error para algunos valores de argumento pero no para otros, o que incluye los valores de argumento en cualquier mensaje de error, no es a prueba de fugas. Esto afecta la forma en que el sistema ejecuta las consultas en las vistas creadas con el security_barrier opción o tablas con seguridad de nivel de fila habilitada. El sistema hará cumplir las condiciones de las políticas de seguridad y las vistas de barreras de seguridad antes de cualquier condición proporcionada por el usuario de la consulta en sí que contenga funciones a prueba de fugas, con el fin de evitar la exposición inadvertida de los datos. Se supone que las funciones y operadores marcados como a prueba de fugas son confiables y pueden ejecutarse antes de las condiciones de las políticas de seguridad y las vistas de barreras de seguridad. Además, las funciones que no toman argumentos o que no se pasan ningún argumento de la vista o tabla de barrera de seguridad no tienen que marcarse como a prueba de fugas para ejecutarse antes de las condiciones de seguridad. Ver CREAR VISTA y Sección 40.5. Esta opción solo la puede configurar el superusuario.

CALLED ON NULL INPUTRETURNS NULL ON NULL INPUTSTRICT

CALLED ON NULL INPUT (el valor predeterminado) indica que la función se llamará normalmente cuando algunos de sus argumentos sean nulos. Entonces, es responsabilidad del autor de la función verificar los valores nulos si es necesario y responder adecuadamente.

RETURNS NULL ON NULL INPUT o STRICT indica que la función siempre devuelve nulo siempre que alguno de sus argumentos sea nulo. Si se especifica este parámetro, la función no se ejecuta cuando hay argumentos nulos; en su lugar, se asume automáticamente un resultado nulo.

[EXTERNAL] SECURITY INVOKER[EXTERNAL] SECURITY DEFINER

SECURITY INVOKER indica que la función debe ejecutarse con los privilegios del usuario que la llama. Ese es el valor predeterminado. SECURITY DEFINER especifica que la función se ejecutará con los privilegios del usuario que la posee.

La palabra clave EXTERNAL está permitido para la conformidad con SQL, pero es opcional ya que, a diferencia de SQL, esta característica se aplica a todas las funciones, no solo a las externas.

PARALLEL

PARALLEL UNSAFE indica que la función no se puede ejecutar en modo paralelo y la presencia de dicha función en una declaración SQL obliga a un plan de ejecución en serie. Este es el predeterminado. PARALLEL RESTRICTED indica que la función se puede ejecutar en modo paralelo, pero la ejecución está restringida al líder de grupo paralelo. PARALLEL SAFE indica que la función es segura para ejecutarse en modo paralelo sin restricciones.

Las funciones deben etiquetarse en paralelo como inseguras si modifican el estado de la base de datos, o si realizan cambios en la transacción, como el uso de subtransacciones, o si acceden a secuencias o intentan realizar cambios persistentes en la configuración (p. ej., setval). Deben etiquetarse como restringidos en paralelo si acceden a tablas temporales, estado de conexión del cliente, cursores, declaraciones preparadas o estado local de backend misceláneo que el sistema no puede sincronizar en modo paralelo (por ejemplo, setseed no puede ser ejecutado por otra persona que no sea el líder del grupo porque un cambio realizado por otro proceso no se reflejaría en el líder). En general, si una función está etiquetada como segura cuando está restringida o insegura, o si está etiquetada como restringida cuando de hecho no es segura, puede arrojar errores o producir respuestas incorrectas cuando se usa en una consulta paralela. En teoría, las funciones en lenguaje C podrían exhibir un comportamiento totalmente indefinido si se etiquetan incorrectamente, ya que no hay forma de que el sistema se proteja contra el código C arbitrario, pero en los casos más probables el resultado no será peor que para cualquier otra función. En caso de duda, las funciones deben etiquetarse como UNSAFE, que es el predeterminado.

COSTexecution_cost

Un número positivo que da el costo de ejecución estimado para la función, en unidades de cpu_operator_cost. Si la función devuelve un conjunto, este es el costo por fila devuelta. Si no se especifica el costo, se asume 1 unidad para el lenguaje C y las funciones internas, y 100 unidades para las funciones en todos los demás idiomas. Los valores más altos hacen que el planificador trate de evitar evaluar la función con más frecuencia de la necesaria.

ROWSresult_rows

Un número positivo que da el número estimado de filas que el planificador debería esperar que devuelva la función. Esto solo se permite cuando se declara que la función devuelve un conjunto. La suposición predeterminada es 1000 filas.

SUPPORTsupport_function

El nombre (opcionalmente calificado por esquema) de un función de apoyo al planificador utilizar para esta función. Ver Sección 37.11 para detalles. Debe ser superusuario para utilizar esta opción.

configuration_parametervalue

los SET La cláusula hace que el parámetro de configuración especificado se establezca en el valor especificado cuando se ingresa la función, y luego se restaura a su valor anterior cuando la función sale. SET FROM CURRENT guarda el valor del parámetro que es actual cuando CREATE FUNCTION se ejecuta como el valor que se aplicará cuando se ingrese la función.

Si un SET cláusula se adjunta a una función, entonces los efectos de una SET LOCAL El comando ejecutado dentro de la función para la misma variable está restringido a la función: el valor anterior del parámetro de configuración aún se restaura al salir de la función. Sin embargo, un ordinario SET comando (sin LOCAL) anula el SET cláusula, tanto como lo haría para una cláusula anterior SET LOCAL comando: los efectos de dicho comando persistirán después de la salida de la función, a menos que se revierta la transacción actual.

Ver SET y Capítulo 19 para obtener más información sobre los nombres y valores de los parámetros permitidos.

definition

Una constante de cadena que define la función; el significado depende del idioma. Puede ser un nombre de función interno, la ruta a un archivo de objeto, un comando SQL o texto en un lenguaje de procedimiento.

A menudo es útil utilizar comillas en dólares (consulte la Sección 4.1.2.4) para escribir la cadena de definición de la función, en lugar de la sintaxis normal de comillas simples. Sin las comillas en dólares, las comillas simples o las barras invertidas en la definición de la función deben eliminarse duplicándolas.

obj_file, link_symbol

Esta forma de AS La cláusula se utiliza para funciones en lenguaje C cargables dinámicamente cuando el nombre de la función en el código fuente del lenguaje C no es el mismo que el nombre de la función SQL. La cuerda obj_file es el nombre del archivo de biblioteca compartida que contiene la función C compilada y se interpreta como el comando LOAD. La cuerda link_symbol es el símbolo de enlace de la función, es decir, el nombre de la función en el código fuente del lenguaje C. Si se omite el símbolo de enlace, se supone que es el mismo que el nombre de la función SQL que se está definiendo. Los nombres C de todas las funciones deben ser diferentes, por lo que debe asignar a las funciones C sobrecargadas diferentes nombres C (por ejemplo, use los tipos de argumentos como parte de los nombres C).

Cuando se repite CREATE FUNCTION las llamadas se refieren al mismo archivo de objeto, el archivo solo se carga una vez por sesión. Para descargar y volver a cargar el archivo (quizás durante el desarrollo), inicie una nueva sesión.

Referirse a Sección 37.3 para obtener más información sobre las funciones de escritura.

Sobrecarga

PostgreSQL permite la función sobrecarga; es decir, se puede usar el mismo nombre para varias funciones diferentes siempre que tengan distintos tipos de argumentos de entrada. Ya sea que la use o no, esta capacidad implica precauciones de seguridad al llamar a funciones en bases de datos donde algunos usuarios desconfían de otros usuarios; consulte la Sección 10.3.

Dos funciones se consideran iguales si tienen los mismos nombres y aporte tipos de argumentos, ignorando cualquier OUT parámetros. Así, por ejemplo, estas declaraciones entran en conflicto:

CREATEFUNCTION foo(int)...CREATEFUNCTION foo(int,outtext)...

Las funciones que tienen listas de tipos de argumentos diferentes no se considerarán en conflicto en el momento de la creación, pero si se proporcionan valores predeterminados, podrían entrar en conflicto en el uso. Por ejemplo, considere

CREATEFUNCTION foo(int)...CREATEFUNCTION foo(int,intdefault42)...

Una llamada foo(10) fallará debido a la ambigüedad sobre qué función debe llamarse.

Notas

Se permite la sintaxis completa de tipo SQL para declarar los argumentos y el valor de retorno de una función. Sin embargo, los modificadores de tipo entre paréntesis (p. Ej., El campo de precisión para el tipo numeric) son descartados por CREATE FUNCTION. Así por ejemplo CREATE FUNCTION foo (varchar(10)) ... es exactamente lo mismo que CREATE FUNCTION foo (varchar) ....

Al reemplazar una función existente con CREATE OR REPLACE FUNCTION, existen restricciones para cambiar los nombres de los parámetros. No puede cambiar el nombre ya asignado a ningún parámetro de entrada (aunque puede agregar nombres a parámetros que antes no tenían ninguno). Si hay más de un parámetro de salida, no puede cambiar los nombres de los parámetros de salida, porque eso cambiaría los nombres de columna del tipo compuesto anónimo que describe el resultado de la función. Estas restricciones se realizan para garantizar que las llamadas existentes de la función no dejen de funcionar cuando se reemplaza.

Si se declara una función STRICT con un VARIADIC argumento, la comprobación de rigurosidad comprueba que la matriz variadic como un todo no es nulo. La función aún se llamará si la matriz tiene elementos nulos.

Ejemplos de

A continuación, se muestran algunos ejemplos triviales que le ayudarán a empezar. Para obtener más información y ejemplos, consulte Sección 37.3.

CREATEFUNCTIONadd(integer,integer)RETURNSintegerAS'select $1 + $2;'LANGUAGESQL
    IMMUTABLE
    RETURNSNULLONNULL INPUT;

Incrementar un número entero, haciendo uso de un nombre de argumento, en PL / pgSQL:

CREATEORREPLACEFUNCTION increment(i integer)RETURNSintegerAS $$
        BEGINRETURN i +1;END;
$$ LANGUAGE plpgsql;

Devuelve un registro que contiene varios parámetros de salida:

CREATEFUNCTION dup(inint,out f1 int,out f2 text)AS $$ SELECT $1, CAST($1AStext)||' is text' $$
    LANGUAGESQL;SELECT*FROM dup(42);

Puede hacer lo mismo de forma más detallada con un tipo compuesto con nombre explícito:

CREATETYPE dup_result AS(f1 int, f2 text);CREATEFUNCTION dup(int)RETURNS dup_result
    AS $$ SELECT $1, CAST($1AStext)||' is text' $$
    LANGUAGESQL;SELECT*FROM dup(42);

Otra forma de devolver varias columnas es utilizar un TABLE función:

CREATEFUNCTION dup(int)RETURNSTABLE(f1 int, f2 text)AS $$ SELECT $1, CAST($1AStext)||' is text' $$
    LANGUAGESQL;SELECT*FROM dup(42);

Sin embargo, un TABLE La función es diferente de los ejemplos anteriores, porque en realidad devuelve un colocar de registros, no solo un registro.

Escribiendo SECURITY DEFINER Funciones seguras

Porque un SECURITY DEFINER La función se ejecuta con los privilegios del usuario que la posee, se debe tener cuidado para garantizar que la función no se pueda utilizar incorrectamente. Por motivos de seguridad, search_path debe establecerse para excluir cualquier esquema en el que puedan escribir usuarios que no sean de confianza. Esto evita que los usuarios malintencionados creen objetos (p. Ej., Tablas, funciones y operadores) que enmascaren los objetos destinados a ser utilizados por la función. Particularmente importante a este respecto es el esquema de tabla temporal, que se busca primero de forma predeterminada y normalmente cualquier persona puede escribirlo. Se puede obtener una disposición segura obligando a que se busque en último lugar el esquema temporal. Para hacer esto, escribe pg_temp como la última entrada en search_path. Esta función ilustra el uso seguro:

CREATEFUNCTION check_password(uname TEXT, pass TEXT)RETURNSBOOLEANAS $$
DECLARE passed BOOLEAN;BEGINSELECT(pwd = $2)INTO passed
        FROM    pwds
        WHERE   username = $1;RETURN passed;END;
$$  LANGUAGE plpgsql
    SECURITY DEFINER-- Set a secure search_path: trusted schema(s), then 'pg_temp'.SET search_path = admin, pg_temp;

La intención de esta función es acceder a una tabla admin.pwds. Pero sin el SET cláusula, o con una SET cláusula que menciona solo admin, la función podría subvertirse creando una tabla temporal llamada pwds.

Antes de la versión 8.3 de PostgreSQL, el SET la cláusula no estaba disponible, por lo que las funciones más antiguas pueden contener una lógica bastante complicada para guardar, configurar y restaurar search_path. los SET La cláusula es mucho más fácil de usar para este propósito.

Otro punto a tener en cuenta es que, de forma predeterminada, el privilegio de ejecución se otorga a PUBLIC para funciones recién creadas (consulte la Sección 5.7 para obtener más información). Con frecuencia, deseará restringir el uso de una función de definición de seguridad a solo algunos usuarios. Para hacer eso, debe revocar el valor predeterminado PUBLIC privilegios y luego otorgar privilegios de ejecución de forma selectiva. Para evitar tener una ventana donde la nueva función sea accesible para todos, créela y establezca los privilegios dentro de una sola transacción. Por ejemplo:

BEGIN;CREATEFUNCTION check_password(uname TEXT, pass TEXT)... SECURITY DEFINER;REVOKEALLONFUNCTION check_password(uname TEXT, pass TEXT)FROMPUBLIC;GRANTEXECUTEONFUNCTION check_password(uname TEXT, pass TEXT)TO admins;COMMIT;

Compatibilidad

A CREATE FUNCTION El comando está definido en el estándar SQL. La versión de PostgreSQL es similar pero no totalmente compatible. Los atributos no son portables, ni tampoco los diferentes idiomas disponibles.

Para compatibilidad con algunos otros sistemas de bases de datos, argmode se puede escribir antes o después argname. Pero solo la primera forma cumple con los estándares.

Para los valores predeterminados de los parámetros, el estándar SQL especifica solo la sintaxis con la DEFAULT palabra clave. La sintaxis con = se utiliza en T-SQL y Firebird.

Ver también

ALTER FUNCTION, DROP FUNCTION, GRANT, LOAD, REVOKE