CREATE FUNCTION

Crea un nueva función escalar definida por el usuario (UDF) mediante una cláusula SQL SELECT o un programa Python.

Para obtener más información y ejemplos, consulte Creación de funciones definidas por el usuario.

Privilegios necesarios

Debe haber obtenido permiso de una de las siguientes maneras para ejecutar CREATE OR REPLACE FUNCTION:

• Para CREATE FUNCTION:

  • Un superusuario puede utilizar lenguajes de confianza y que no sean de confianza para crear funciones.

  • Los usuarios con el privilegio CREATE [ OR REPLACE ] FUNCTION pueden crear funciones con lenguajes de confianza.

• Para REPLACE FUNCTION:

  • Superusuario

  • Usuarios con el privilegio CREATE [ OR REPLACE ] FUNCTION

  • Propietario de la función

Sintaxis

CREATE [ OR REPLACE ] FUNCTION f_function_name
( { [py_arg_name  py_arg_data_type |
sql_arg_data_type } [ , ... ] ] )
RETURNS data_type
{ VOLATILE | STABLE | IMMUTABLE }
AS $$
  { python_program | SELECT_clause }
$$ LANGUAGE { plpythonu | sql }

Parámetros

OR REPLACE

Especifica si ya existe una función con el mismo nombre y los mismos tipos de datos de argumento de entrada o firma que este, debe reemplazarse la función existente. Solo puede reemplazar una función con una nueva función que defina un conjunto idéntico de tipos de datos. Debe ser un superusuario para reemplazar una función.

Si define una función con el mismo nombre que una función existente, pero con una firma diferente, crea una nueva función. En otras palabras, se sobrecarga el nombre de la función. Para obtener más información, consulte Sobrecarga de los nombres de función.

f_function_name

El nombre de la función. Si especifica un nombre de esquema (como myschema.myfunction), la función se crea con el esquema especificado. De lo contrario, la función se crea en el esquema actual. Para obtener más información acerca de los nombres válidos, consulte Nombres e identificadores.

Recomendamos que utilice el prefijo f_ en los nombres de todas las UDF. Amazon Redshift reserva el prefijo f_ para los nombres de las UDF; por lo tanto, cuando utiliza el prefijo f_, se asegura de que el nombre de la UDF no entre en conflicto con el nombre de una función SQL integrada en Amazon Redshift existente o futura. Para obtener más información, consulte Dar nombre a las UDF.

Puede definir más de una función con el mismo nombre de función si los tipos de datos de los argumentos de entrada son diferentes. En otras palabras, se sobrecarga el nombre de la función. Para obtener más información, consulte Sobrecarga de los nombres de función.

py_arg_name py_arg_data_type | sql_arg_data type

Para una UDF de Python, una lista de nombres de argumento de entrada y tipos de datos. Para una UDF de SQL, una lista tipos de datos, sin nombres de argumento. En una UDF de Python, haga referencia a los argumentos usando los nombres de argumento. En una UDF de SQL, haga referencia a los argumentos usando $1, $2, etc., en función del orden de los argumentos en la lista de argumentos.

Para una UDF de SQL, los tipos de datos de entrada y salida pueden ser cualquier tipo de datos estándar de Amazon Redshift. Para una UDF Python, los tipos de datos de entrada y salida pueden ser SMALLINT, INTEGER, BIGINT, DECIMAL, REAL, DOUBLE PRECISION, BOOLEAN, CHAR, VARCHAR, DATE o TIMESTAMP. Además, las funciones definidas por el usuario (UDF) de Python admiten un tipo de datos de ANYELEMENT. Este tipo de datos se convierte automáticamente en un tipo de datos estándar basado en el tipo de datos correspondiente al argumento proporcionado en tiempo de ejecución. Si varios argumentos utilizan ANYELEMENT, todos se resuelven con el mismo tipo de datos durante el tiempo de ejecución, según el primer argumento ANYELEMENT de la lista. Para obtener más información, consulte Tipos de datos de UDF de Python y Tipos de datos.

Puede especificar un máximo de 32 argumentos.

RETURNS data_type

El tipo de datos del valor que la función devuelve. El tipo de datos RETURNS puede ser cualquier tipo de datos estándar de Amazon Redshift. Además, las UDF de Python pueden usar un tipo de datos de ANYELEMENT, que se convierte automáticamente en un tipo de datos estándar basado en el argumento proporcionado en tiempo de ejecución. Si especifica ANYELEMENT para el tipo de datos que se devuelve, al menos un argumento debe usar ANYELEMENT. El tipo de datos que se devuelve coincide con el tipo de datos proporcionado por el argumento ANYELEMENT cuando se convoca la función. Para obtener más información, consulte Tipos de datos de UDF de Python.

VOLATILE | STABLE | IMMUTABLE

Informa al optimizador de consultas acerca de la volatilidad de la función.

Obtendrá la mejor optimización si etiqueta la función con la categoría de volatilidad válida más estricta. No obstante, si la categoría es demasiado estricta, hay un riesgo de que el optimizador omita por error algunas ejecuciones, lo que resultaría en un conjunto de resultados incorrectos. Las categorías de volatilidad, de la menos estricta a la más estricta, son las siguientes:

• VOLATILE

• STABLE

• IMMUTABLE

VOLATILE

Dados los mismos argumentos, la función puede devolver resultados diferentes en ejecuciones consecutivas, incluso para las filas de una única instrucción. El optimizador de consultas no puede realizar ninguna suposición acerca del comportamiento de una función volátil, por lo que una consulta que utiliza una función volátil debe reevaluar la función para cada fila de entrada.

STABLE

Dados los mismos argumentos, se garantiza que la función devuelve los mismos resultados para todas las filas procesadas dentro de una única instrucción. La función puede devolver resultados diferentes cuando se evoca en diferentes instrucciones. Esta categoría permite que el optimizador mejore diversas ejecuciones de la función dentro de una única instrucción a una única ejecución para la instrucción.

IMMUTABLE

Dados los mismos argumentos, la función siempre devuelve el mismo resultado. Cuando una consulta evoca una función IMMUTABLE con argumentos constantes, el optimizador evalúa previamente la función.

AS $$ statement $$

Una construcción que contiene la instrucción que se ejecutará. Las palabras claves literales AS $$ y $$ son obligatorias.

En Amazon Redshift, la instrucción debe incluirse en la función con un formato llamado entrecomillado con símbolo de dólar. Cualquier elemento que se encuentre dentro de los signos se transmite exactamente como es. No necesita incluir en una secuencia de escape los caracteres especiales porque el contenido de la cadena se escribe literalmente.

Con el entrecomillado de dólar, se utilizan un par de signos de dólar ($$) para representar el inicio y el final de la instrucción que se va a ejecutar, tal y como se muestra en el siguiente ejemplo.

$$ my statement $$

De manera opcional, entre los signos de dólar de cada par, puede especificar una cadena para ayudar a identificar la instrucción. La cadena que usa debe ser la misma en el inicio y en el final de los pares de signos. La cadena distingue entre mayúsculas y minúsculas, y sigue las mismas restricciones que un identificador sin comillas, excepto que no puede contener signos de dólar. En el siguiente ejemplo, se usa la cadena test.

$test$ my statement $test$

Para obtener más información acerca de los signos de dólar, consulte Dollar-quoted String Constants bajo Lexical Structure en la documentación de PostgreSQL.

python_program

Un programa Python ejecutable válido que devuelve un valor. La instrucción que pase con la función debe ajustarse a los requisitos de sangría, tal y como se especifica en la guía de estilo para el código de Python del sitio web de Python. Para obtener más información, consulte Compatibilidad del lenguaje Python con las UDF.

SQL_clause

Una cláusula SQL SELECT.

La cláusula SELECT no puede incluir ninguno de los tipos de cláusulas siguientes:

• FROM

• INTO

• WHERE

• GROUP BY

• ORDER BY

• LIMIT

LANGUAGE { plpythonu | sql }

Para Python, especifique plpythonu. Para SQL, especifique sql. Debe tener permiso para usar el lenguaje de SQL o plpythonu. Para obtener más información, consulte Privilegios y seguridad de las UDF.

Notas de uso

Funciones anidadas

Puede llamar a otra función definida por el usuario (UDF) de SQL desde una UDF de SQL. La función anidada debe existir cuando se ejecuta el comando CREATE FUNCTION. Amazon Redshift no realiza un seguimiento de las dependencias de las UDF; por lo tanto, si se elimina la función anidada, Amazon Redshift no devuelve ningún error. No obstante, la UDF producirá un error si la función anidada no existe. Por ejemplo, la siguiente función llama a la función f_sql_greater en la cláusula SELECT.

create function f_sql_commission (float, float )
  returns float
stable
as $$
  select f_sql_greater ($1, $2)
$$ language sql;

Privilegios y seguridad de las UDF

Para crear una UDF, debe tener permiso para usar el lenguaje de SQL o plpythonu (Python). De manera predeterminada, se concede el permiso USAGE ON LANGUAGE SQL a PUBLIC. No obstante, debe conceder explícitamente el permiso de USAGE ON LANGUAGE PLPYTHONU a usuarios o grupos específicos.

Para revocar los permisos de uso de SQL, primero revoque el uso de PUBLIC. A continuación, conceda los permisos de uso de SQL a solo los usuarios o grupos que tienen permiso para crear UDF de SQL. En el siguiente ejemplo, se revocan los permisos de uso de SQL de PUBLIC y después se conceden permisos de uso al grupo de usuarios udf_devs.

revoke usage on language sql from PUBLIC;
grant usage on language sql to group udf_devs;

Para ejecutar una UDF, debe tener permisos de ejecución para cada función. De manera predeterminada, el permiso de ejecución de nuevas UDF está establecido en PUBLIC. Para restringir el uso, revoque el permiso de ejecución PUBLIC para la función. A continuación, conceda el privilegio a usuarios o grupos específicos.

En el siguiente ejemplo, se revocan los permisos de ejecución en la función f_py_greater de PUBLIC y después se conceden permisos de uso al grupo de usuarios udf_devs.

revoke execute on function f_py_greater(a float, b float) from PUBLIC;
grant execute on function f_py_greater(a float, b float) to group udf_devs;

De manera predeterminada, los superusuarios tienen todos los privilegios.

Para obtener más información, consulte GRANT y REVOKE.

Ejemplos

Ejemplo de una UDF escalar de Python

En el siguiente ejemplo, se crea una UDF de Python que compara dos enteros y devuelve el valor mayor.

create function f_py_greater (a float, b float)
  returns float
stable
as $$
  if a > b:
    return a
  return b
$$ language plpythonu;

En el siguiente ejemplo, se consulta la tabla SALES y se evoca la nueva función f_py_greater para devolver COMMISSION o 20 por ciento de PRICEPAID, el que represente el mayor valor.

select f_py_greater (commission, pricepaid*0.20) from sales;

Ejemplo de una UDF escalar de SQL

En el siguiente ejemplo, se crea una función que compara dos números y devuelve el valor más grande.

create function f_sql_greater (float, float)
  returns float
stable
as $$
  select case when $1 > $2 then $1
    else $2
  end
$$ language sql;

La siguiente consulta llama a la nueva función f_sql_greater para consultar la tabla SALES y devuelve COMMISSION o el 20 por ciento de PRICEPAID, lo que sea mayor.

select f_sql_greater (commission, pricepaid*0.20) from sales;