Información general de procedimientos almacenados en Amazon Redshift - Amazon Redshift

Información general de procedimientos almacenados en Amazon Redshift

En este tema se describen los detalles sobre la finalidad y el uso de los procedimientos almacenados.

Los procedimientos almacenados se utilizan normalmente para encapsular lógica destinada a la transformación de datos, la validación de datos y la lógica específica para empresas. Mediante la combinación de varios pasos de SQL en un procedimiento almacenado, puede reducir las idas y vueltas entre sus aplicaciones y la base de datos.

Para lograr un control de acceso preciso, puede crear procedimientos almacenados que ejecuten funciones sin otorgar a un usuario acceso a las tablas subyacentes. Por ejemplo, solo el propietario o un superusuario pueden truncar una tabla. Un usuario necesita privilegios de escritura para insertar datos en una tabla. En lugar de conceder privilegios al usuario para las tablas subyacentes, puede crear un procedimiento almacenado que realice la tarea. A continuación, puede dar privilegios al usuario para que ejecute el procedimiento almacenado.

Un procedimiento almacenado con el atributo de seguridad DEFINER se ejecuta con los privilegios del propietario del procedimiento almacenado. De forma predeterminada, un procedimiento almacenado tiene seguridad INVOKER, lo que significa que el procedimiento usa los privilegios del usuario que llama el procedimiento.

Para crear un procedimiento almacenado, use el comando CREATE PROCEDURE. Para ejecutar un procedimiento, use el comando CALL. Se proporcionan ejemplos más adelante en esa sección.

nota

Algunos clientes pueden mostrar el siguiente error al crear un procedimiento almacenado de Amazon Redshift.

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

Este error ocurre debido a la incapacidad del cliente para analizar correctamente la instrucción CREATE PROCEDURE con puntos y coma delimitando las instrucciones y con un símbolo del dólar ($) citado. Esto causa que solo una parte de la instrucción se envíe al servidor de Amazon Redshift. A menudo, puede sortear este error usando las opciones Run as batch o Execute selected del cliente.

Por ejemplo, si usa un cliente Aginity, use la opción Run entire script as batch. Si usa SQL Workbench/J, recomendamos la versión 124. Si usa la versión 125 de SQL Workbench/J, considere especificar un delimitador alternativo como solución.

CREATE PROCEDURE contiene instrucciones SQL delimitadas por un punto y coma (;). Al definir un delimitador alternativo, como una barra diagonal (/) y situarlo al final de una instrucción CREATE PROCEDURE, se envía la instrucción entera al servidor de Amazon Redshift para procesarla. A continuación se muestra un ejemplo.

CREATE OR REPLACE PROCEDURE test()
AS $$
BEGIN
  SELECT 1 a;
END;
$$
LANGUAGE plpgsql
;
/

Para más información, vea el Delimitador alterno en la documentación de SQL Workbench/J. También puede usar un cliente con una compatibilidad mejor para analizar instrucciones CREATE PROCEDURE, como el editor de consultas en la consola de Amazon Redshift o TablePlus.

Temas

El siguiente ejemplo muestra un procedimiento sin argumentos de salida. De forma predeterminada, los argumentos son argumentos de entrada (IN).

CREATE OR REPLACE PROCEDURE test_sp1(f1 int, f2 varchar)
AS $$
BEGIN
  RAISE INFO 'f1 = %, f2 = %', f1, f2;
END;
$$ LANGUAGE plpgsql;

call test_sp1(5, 'abc');
INFO: f1 = 5, f2 = abc
CALL

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 almacenados. 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 esta forma 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 sobre los procedimientos almacenados, consulte CREATE PROCEDURE y Creación de procedimientos almacenados en Amazon Redshift. Para obtener más información sobre las tablas de catálogo, consulte Tablas de catálogos de sistema.

El siguiente ejemplo muestra un procedimiento con argumentos de salida. Los argumentos son entrada (IN), entrada y salida (INOUT) y salida (OUT).

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;


call test_sp2(2,'2019');

         f2          | column2
---------------------+---------
 2019+2019+2019+2019 | 2
(1 row)