CREATE PROCEDURE

Crea un procedimiento almacenado nuevo o sustituye un procedimiento existente para la actual base de datos.

Para obtener más información y ejemplos, consulte Creación de procedimientos almacenados en Amazon Redshift.

Privilegios necesarios

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

• Para CREATE PROCEDURE:

  • Superusuario

  • Usuarios con privilegios CREATE y USAGE en el esquema donde se crea el procedimiento almacenado

• Para REPLACE PROCEDURE:

  • Superusuario

  • Propietario del procedimiento

Sintaxis

CREATE [ OR REPLACE ] PROCEDURE sp_procedure_name  
  ( [ [ argname ] [ argmode ] argtype [, ...] ] )
[ NONATOMIC ]
AS $$
  procedure_body
$$ LANGUAGE plpgsql
[ { SECURITY INVOKER | SECURITY DEFINER } ]
[ SET configuration_parameter { TO value | = value } ]

Parámetros

OR REPLACE

Una cláusula que especifica que si ya existe un procedimiento con el mismo nombre y los mismos tipos de datos de argumento de entrada o firma que este, debe reemplazarse el procedimiento existente. Solo puede reemplazar un procedimiento por un nuevo procedimiento que defina un conjunto idéntico de tipos de datos.

Si define un procedimiento con el mismo nombre que un procedimiento existente, pero con una firma diferente, creará un nuevo procedimiento. En otras palabras, se sobrecarga el nombre del procedimiento. Para obtener más información, consulte Sobrecarga de nombres de procedimientos.

sp_procedure_name

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

Se recomienda que agregue el prefijo sp_ a todos los nombres de procedimientos almacenados. Amazon Redshift reserva el prefijo sp_ para los nombres de procedimientos almacenados. Con el uso del prefijo sp_, se asegura de que el nombre del procedimiento almacenado no entre en conflicto con el nombre de una función o un procedimiento almacenado integrado de Amazon Redshift existente o futuro. Para obtener más información, consulte Nomenclatura de los procedimientos almacenados.

Puede definir más de un procedimiento con el mismo nombre si los tipos de datos para los argumentos de entrada, o firmas, son diferentes. En otras palabras, en este caso se sobrecarga el nombre del procedimiento. Para obtener más información, consulte Sobrecarga de nombres de procedimientos

[argname] [ argmode] argtype

Una lista de nombres de argumento, modos de argumento y tipos de datos. Solo se necesita el tipo de datos. El nombre y el modo son opcionales y es posible cambiar su posición.

El modo del argumento puede ser IN, OUT O INOUT. El valor predeterminado es IN.

Puede utilizar argumentos OU e INOUT para devolver uno o más valores de una llamada de procedimientos. Cuando hay argumentos o INOUT, la llamada de procedimientos devuelve una fila de resultados que contiene n columnas, done n es el número total de argumentos OUT o INOUT.

Los argumentos INOUT son argumentos de entrada y salida al mismo tiempo. Los argumentos de entrada incluyen argumentos IN e INOUT, y los argumentos de salida incluyen argumentos OUT e INOUT.

Los argumentos OUT no se especifican como parte de la instrucción CALL. Especifique argumentos INOUT en la declaración CALL de procedimientos almacenada. Los argumentos INOUT pueden ser útiles al pasar y devolver valores de una llamada anidada y también al devolver un refcursor. Para obtener más información sobre los tipos refcursor, consulte Cursores.

Los tipos de datos de argumentos pueden ser cualquier tipo de datos estándar de Amazon Redshift. Además, un tipo de datos de argumentos puede ser refcursor.

Puede especificar un máximo de 32 argumentos de entrada y 32 argumentos de salida.

AS $$ procedure_body $$

Se trata de una construcción que contiene el procedimiento que se ejecutará. Las palabras claves literales $$ y $$ son obligatorias.

Amazon Redshift exige que se incluya la instrucción en el procedimiento mediante 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 prueba de cadena.

$test$ my statement $test$

Esta sintaxis también es útil para el entrecomillado de dólar anidado. 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.

procedure_body

Un conjunto de instrucciones PL/pgSQL válidas. Las instrucciones PL/pgSQL aumentan los comandos SQL con construcciones de procedimiento, entre las que se incluyen expresiones condicionales y bucles, para controlar el flujo lógico. Se pueden usar la mayoría de los comandos SQL en el cuerpo del procedimiento, incluidos lenguajes de modificación de datos (DML) como COPY, UNLOAD e INSERT y lenguajes de definición de datos (DDL) como CREATE TABLE. Para obtener más información, consulte Referencia del lenguaje PL/pgSQL.

LANGUAGE plpgsql

Un valor del lenguaje. Especifique plpgsql. Debe tener permiso para usar el lenguaje para utilizar plpgsql. Para obtener más información, consulte GRANT.

NONATOMIC

Crea el procedimiento almacenado en un modo de transacción no atómico. El modo NONATOMIC confirma automáticamente las instrucciones que incluye el procedimiento. Además, cuando se produce un error en el procedimiento NONATOMIC, el error no se vuelve a generar si se gestiona mediante un bloque de excepciones. Para obtener más información, consulte Administración de transacciones y RAISE.

Cuando defina un procedimiento almacenado como NONATOMIC, tenga en cuenta lo siguiente:

• Cuando anide llamadas a procedimientos almacenados, todos deben crearse en el mismo modo de transacción.

• Las opciones SECURITY DEFINER y SET configuration_parameter no se admiten cuando se crea un procedimiento en modo NONATOMIC.

• Cualquier cursor que esté abierto (explícita o implícitamente) se cierra automáticamente cuando se procesa una confirmación implícita. Por lo tanto, debe abrir una transacción explícita antes de iniciar un bucle de cursor para asegurarse de que ninguna instrucción SQL en la iteración del bucle se confirme de forma implícita.

SECURITY INVOKER | SECURITY DEFINER

La opción SECURITY DEFINER no se admite cuando se especifica NONATOMIC.

El modo de seguridad para el procedimiento determina los privilegios de acceso del procedimiento en el tiempo de ejecución. El procedimiento tiene que tener permiso para acceder a los objetos subyacentes de la base de datos.

Para el modo SECURITY INVOKER, el procedimiento usa los privilegios del usuario que llama al procedimiento. El usuario tiene que tener permisos explícitos sobre los objetos subyacentes de la base de datos. El valor predeterminado es SECURITY INVOKER.

Para el modo SECURITY DEFINER, el procedimiento usa los privilegios del propietario del procedimiento. El propietario del procedimiento se define como el usuario propietario del procedimiento en tiempo de ejecución, no necesariamente el usuario que definió inicialmente el procedimiento. El usuario que llama al procedimiento necesita privilegio de ejecución en el procedimiento pero no necesita ningún privilegio en los objetos subyacentes.

SET configuration_parameter { TO value | = value }

Estas opciones no se admiten cuando se especifica NONATOMIC.

La cláusula SET causa que el parámetro configuration_parameter especificado se establezca en el valor especificado cuando se especifica el procedimiento. Esta cláusula restaura a continuación configuration_parameter a su valor anterior cuando el procedimiento existe.

Notas de uso

Si se creó un procedimiento almacenado mediante la opción SECURITY DEFINER, al invocar la función CURRENT_USER desde el procedimiento almacenado, Amazon Redshift devuelve el nombre de usuario del propietario del procedimiento almacenado.

Ejemplos

nota

Si al ejecutar estos ejemplos se produce un error similar a:

ERROR: 42601: [Amazon](500310) unterminated dollar-quoted string at or near "$$

Consulte Información general de procedimientos almacenados en Amazon Redshift.

El siguiente ejemplo crea un procedimiento con dos parámetros de entrada.

CREATE OR REPLACE PROCEDURE test_sp1(f1 int, f2 varchar(20))
AS $$
DECLARE
  min_val int;
BEGIN
  DROP TABLE IF EXISTS tmp_tbl;
  CREATE TEMP TABLE tmp_tbl(id int);
  INSERT INTO tmp_tbl values (f1),(10001),(10002);
  SELECT INTO min_val MIN(id) FROM tmp_tbl;
  RAISE INFO 'min_val = %, f2 = %', min_val, f2;
END;
$$ LANGUAGE plpgsql;

nota

Al escribir procedimientos almacenados, sugerimos una práctica recomendada para proteger los valores confidenciales:

No codifique ninguna información confidencial en la lógica de procedimientos almacenada. Por ejemplo, no asigne una contraseña de usuario en una instrucción CREATE USER del cuerpo de un procedimiento almacenado. Esto supone un riesgo de seguridad, porque los valores codificados de forma rígida se pueden registrar como metadatos de esquema en las tablas del catálogo. En cambio, pase valores confidenciales, como las contraseñas, como argumentos al procedimiento almacenado, mediante parámetros.

Para obtener más información acerca de los procedimientos almacenados, consultePROCEDIMIENTO DE CREACIÓNyCreación de procedimientos almacenados en Amazon Redshift. Para obtener más información acerca de las tablas de catálogos, consulte Tablas de catálogos de sistema.

El siguiente ejemplo crea un procedimiento con un parámetro IN, un parámetro OUT y un parámetro INOUT.

CREATE OR REPLACE PROCEDURE test_sp2(f1 IN int, f2 INOUT varchar(256), out_var OUT varchar(256))
AS $$
DECLARE
  loop_var int;
BEGIN
  IF f1 is null OR f2 is null THEN
    RAISE EXCEPTION 'input cannot be null';
  END IF;
  DROP TABLE if exists my_etl;
  CREATE TEMP TABLE my_etl(a int, b varchar);
    FOR loop_var IN 1..f1 LOOP
        insert into my_etl values (loop_var, f2);
        f2 := f2 || '+' || f2;
    END LOOP;
  SELECT INTO out_var count(*) from my_etl;
END;
$$ LANGUAGE plpgsql;