Instrucciones de PL/pgSQL admitidas - Amazon Redshift
AsignaciónSELECT INTONo-opSQL dinámicoReturnCondicionales: IF Condicionales: CASEBuclesCursoresRAISEControl de la transacción

Instrucciones de PL/pgSQL admitidas

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 puede usar la mayoría de los comandos SQL, lo que incluye el lenguaje de manipulación de datos (DML), como COPY, UNLOAD e INSERT, y el lenguaje de definición de datos (DDL), como CREATE TABLE. Para ver una lista completa de comandos SQL, consulte Comandos SQL. Además, Amazon Redshift admite las siguientes instrucciones PL/pgSQL.

Asignación

La instrucción de asignación asigna un valor a una variable. La expresión debe devolver un valor único.

identifier := expression;

También es posible usar = no estándar para la asignación, en lugar de :=.

Si el tipo de datos de la expresión no coincide con el tipo de datos de la variable o si la variable tiene un tamaño o precisión conocidos, el valor del resultado se convertirá de forma implícita.

A continuación se muestran ejemplos.

customer_number := 20;
tip := subtotal * 0.15;

SELECT INTO

La instrucción SELECT INTO asigna el resultado de varias columnas (pero solo una fila) a una variable de registro o lista de variables escalares.

SELECT INTO target select_expressions FROM ...;

En la sintaxis anterior, el destino puede ser una variable de registro o una lista separada por comas de variables simples y campos de registro. La lista seleccionar_expresiones y el resto del comando son iguales que en SQL normal.

Si se usa como destino una lista de variables, los valores seleccionados deben coincidir exactamente con la estructura del destino o se producirá un error de tiempo de ejecución. Cuando el destino es una variable de registro, se configura automáticamente al tipo fila de las columnas resultantes de la consulta.

La cláusula INTO puede aparece casi en cualquier parte en la instrucción SELECT. Normalmente, aparece justo después de la cláusula SELECT o justo antes de la cláusula FROM. Es decir, aparece justo antes o justo después de la lista seleccionar_expresiones.

Si la consulta no devuelve ninguna fila, se asignan valores NULL al destino. Si la consulta devuelve varias filas, se asigna la primera fila al destino y el resto se descartan. A menos que la instrucción contenga un comando ORDER BY, la primera fila no es determinista.

Para determinar si la asignación devuelve al menos una fila, use la variable especial FOUND.

SELECT INTO customer_rec * FROM cust WHERE custname = lname;
IF NOT FOUND THEN
  RAISE EXCEPTION 'employee % not found', lname;
END IF;

Para probar si un resultado de registro es nulo, puede usar el condicional IS NULL. No hay forma de determinar si se han descartado filas adicionales. En el siguiente ejemplo, se gestiona un caso donde no se devuelven filas.

CREATE OR REPLACE PROCEDURE select_into_null(return_webpage OUT varchar(256))
AS $$
DECLARE
  customer_rec RECORD;
BEGIN
  SELECT INTO customer_rec * FROM users WHERE user_id=3;
  IF customer_rec.webpage IS NULL THEN
    -- user entered no webpage, return "http://"
    return_webpage = 'http://';
  END IF;
END;
$$ LANGUAGE plpgsql;

No-op

La instrucción no-op (NULL;) es una instrucción de marcador de posición que no hace nada. La instrucción no-op puede indicar que una ramificación de una cadena IF-THEN-ELSE está vacía.

NULL;

SQL dinámico

Para generar comandos dinámicos que puedan implicar diferentes tablas o diferentes tipos de datos cada vez que se ejecutan desde un procedimiento almacenado de PL/pgSQL, use el comando EXECUTE.

EXECUTE command-string [ INTO target ];

En lo anterior, cadena-comando es una expresión que contiene una cadena (de tipo texto) que contiene el comando que se va a ejecutar. Esta valor de cadena-comando se envía al motor de SQL. No se realiza sustitución de variables de PL/pgSQL en la cadena del comando. Los valores de las variables se deben insertar en la cadena en el momento de su construcción.

nota

No puede utilizar las instrucciones COMMIT y ROLLBACK desde un SQL dinámico. Para más información sobre el uso de las instrucciones COMMIT y ROLLBACK en un proceso almacenado, vea Administración de transacciones.

Al trabajar con comandos dinámicos, a menudo tendrá que gestionar el escape de comillas simples. Recomendamos poner el texto fijo entrecomillas en el cuerpo de la función mediante signos de dólar. Los valores dinámicos que se van a insertar en una consulta construida requieren gestión especial porque ellos mismos pueden estar entrecomillados. En el siguiente ejemplo, se supone que hay entrecomillado con signos de dólar de la función como un todo, de modo que las comillas no tienen que ser dobles.

EXECUTE 'UPDATE tbl SET '
  || quote_ident(colname)
  || ' = '
  || quote_literal(newvalue)
  || ' WHERE key = '
  || quote_literal(keyvalue);

El ejemplo anterior muestra las funciones quote_ident(text) y quote_literal(text). Este ejemplo pasa variables que contienen identificadores de columna y tabla a la función quote_ident. También pasa variables que contienen cadenas literales en el comando construido a la función quote_literal. Ambas funciones realizan los pasos adecuados para devolver el texto que se ingresó encerrado entre comillas dobles o simples, respectivamente, con cualquier carácter especial integrado con los identificadores de escape adecuados.

El entrecomillado con signos de dólar solo es útil para entrecomillar texto fijo. No escriba el ejemplo anterior en el formato siguiente.

EXECUTE 'UPDATE tbl SET '
  || quote_ident(colname)
  || ' = $$'
  || newvalue
  || '$$ WHERE key = '
  || quote_literal(keyvalue);

Esto no se hace porque el ejemplo produce un error si el contenido de newvalue tiene $$. El mismo problema ocurre con otros delimitadores de dólar que pueda usar. Para entrecomillar de manera segura texto que no conoce de antemano, use la función quote_literal.

Return

La instrucción RETURN vuelve al intermediario de un procedimiento almacenado.

RETURN;

A continuación se muestra un ejemplo.

CREATE OR REPLACE PROCEDURE return_example(a int)
AS $$  
BEGIN
  FOR b in 1..10 LOOP
    IF b < a THEN
      RAISE INFO 'b = %', b;
    ELSE
      RETURN;
    END IF;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

Condicionales: IF

La instrucción condicional IF puede adoptar las siguientes formas en el lenguaje PL/pgSQL que utiliza Amazon Redshift:

  • IF ... THEN

    IF boolean-expression THEN
  statements
END IF;

    A continuación se muestra un ejemplo.

    IF v_user_id <> 0 THEN
  UPDATE users SET email = v_email WHERE user_id = v_user_id;
END IF;

  • IF ... THEN ... ELSE

    IF boolean-expression THEN
  statements
ELSE
  statements
END IF;

    A continuación se muestra un ejemplo.

    IF parentid IS NULL OR parentid = ''
THEN
  return_name = fullname;
  RETURN;
ELSE
  return_name = hp_true_filename(parentid) || '/' || fullname;
  RETURN;
END IF;

  • IF ... THEN ... ELSIF ... THEN ... ELSE

    La palabra clave ELSIF también puede deletrearse ELSEIF.

    IF boolean-expression THEN
  statements
[ ELSIF boolean-expression THEN
  statements
[ ELSIF boolean-expression THEN
  statements
    ...] ]
[ ELSE
  statements ]
END IF;

    A continuación se muestra un ejemplo.

    IF number = 0 THEN
  result := 'zero';
ELSIF number > 0 THEN
  result := 'positive';
ELSIF number < 0 THEN
  result := 'negative';
ELSE
  -- the only other possibility is that number is null
  result := 'NULL';
END IF;

Condicionales: CASE

La instrucción condicional CASE puede adoptar las siguientes formas en el lenguaje PL/pgSQL que utiliza Amazon Redshift:

  • CASE simple 

    CASE search-expression
WHEN expression [, expression [ ... ]] THEN
  statements
[ WHEN expression [, expression [ ... ]] THEN
  statements
  ... ]
[ ELSE
  statements ]
END CASE;

    La instrucción CASE simple proporciona ejecución condicional basada en la igualdad de los operandos.

    El valor de expresión-búsqueda se evalúa una vez y se compara sucesivamente a cada expresión en la cláusula WHEN. Si se encuentra una coincidencia, se ejecuta la instrucción correspondiente y, a continuación, el control pasa a la siguiente instrucción después de END CASE. Las expresiones WHEN posteriores no se evalúan. Si no se encuentra una coincidencia, se ejecuta la instrucción ELSE. No obstante, si ELSE no está presente, entonces se genera una excepción CASE_NOT_FOUND.

    A continuación se muestra un ejemplo.

    CASE x
WHEN 1, 2 THEN
  msg := 'one or two';
ELSE
  msg := 'other value than one or two';
END CASE;

  • CASE de búsqueda 

    CASE
WHEN boolean-expression THEN
  statements
[ WHEN boolean-expression THEN
  statements
  ... ]
[ ELSE
  statements ]
END CASE;

    La forma de búsqueda de CASE proporciona ejecución condicional basada en expresiones boolenas verdaderas.

    Cada expresión-boleana de la cláusula WHEN se evalúa por turno, hasta encontrar una que dé como resultado true. A continuación se ejecuta la instrucción correspondiente y el control pasa a la siguiente instrucción después de END CASE. Las expresiones WHEN posteriores no se evalúan. Si no se encuentra ningún resultado true, se ejecutan las instrucciones ELSE. No obstante, si ELSE no está presente, entonces se genera una excepción CASE_NOT_FOUND.

    A continuación se muestra un ejemplo.

    CASE
WHEN x BETWEEN 0 AND 10 THEN
  msg := 'value is between zero and ten';
WHEN x BETWEEN 11 AND 20 THEN
  msg := 'value is between eleven and twenty';
END CASE;

Bucles

Las instrucciones bucle pueden adoptar las siguientes formas en el lenguaje PL/pgSQL que utiliza Amazon Redshift:

  • Bucle simple 

    [<<label>>]
LOOP
  statements
END LOOP [ label ];

    Un bucle simple define un bucle incondicional que se repite indefinidamente hasta que es terminado por una instrucción EXIT o RETURN. Las instrucciones EXIT y CONTINUE pueden utilizar la etiqueta opcional dentro de los bucles anidados para especificar a qué bucle se refiere la instrucción EXIT y CONTINUE.

    A continuación se muestra un ejemplo.

    CREATE OR REPLACE PROCEDURE simple_loop()
LANGUAGE plpgsql
AS $$
BEGIN
  <<simple_while>>
  LOOP
    RAISE INFO 'I am raised once';  
    EXIT simple_while;
    RAISE INFO 'I am not raised';
  END LOOP;
  RAISE INFO 'I am raised once as well';
END;
$$;

  • Bucle Exit

    EXIT [ label ] [ WHEN expression ];

    Si no está presente laetiqueta, se termina el bucle más interno y, a continuación, se ejecuta la instrucción después de END LOOP. Si la etiqueta está presente, debe ser la etiqueta del nivel actual o de uno superior del bloque o bucle anidado. A continuación, el bucle o bloque mencionado se termina y el control continúa con la instrucción después del correspondiente END del bucle o bloque.

    Si se especifica WHEN, solo se produce la salida del bucle si la expresión es verdadera. De no ser así, el control pasa a la instrucción después de EXIT.

    Puede usar EXIT con todos los tipos de bucles; su uso no se limita a bucles incondicionales.

    Cuando se usa con un bloque BEGIN, EXIT pasa el control a la siguiente instrucción después del final del bloque. Debe usarse una etiqueta para este propósito. Un EXIT sin etiqueta no se considera una coincidencia para un bloque BEGIN.

    A continuación se muestra un ejemplo.

    CREATE OR REPLACE PROCEDURE simple_loop_when(x int)
LANGUAGE plpgsql
AS $$
DECLARE i INTEGER := 0;
BEGIN
  <<simple_loop_when>>
  LOOP
    RAISE INFO 'i %', i;
    i := i + 1;
    EXIT simple_loop_when WHEN (i >= x);
  END LOOP;
END;
$$;

  • Bucle Continue 

    CONTINUE [ label ] [ WHEN expression ];

    Si no se proporciona una etiqueta, la ejecución salta a la siguiente iteración del bucle más interno. Es decir, se omiten todas las instrucciones restantes en el cuerpo del bucle. El control vuelve a la expresión de control del bucle (de haberla) para determinar si se necesita otra iteración del bucle. Si la etiqueta está presente, especifica la etiqueta del bucle cuya ejecución se continúa.

    Si se especifica WHEN, la siguiente iteración del bucle solo empieza si la expresión es verdadera. De no ser así, el control pasa a la instrucción después de CONTINUE.

    Puede usar CONTINUE con todos los tipos de bucles; su uso no se limita a bucles incondicionales.

    CONTINUE mylabel;

  • Bucle WHILE 

    [<<label>>]
WHILE expression LOOP
  statements
END LOOP [ label ];

    La instrucción WHILE repite una secuencia de instrucciones siempre que la expresión-boleana sea verdadera. La expresión se comprueba justo antes de cada entrada al cuerpo del bucle.

    A continuación se muestra un ejemplo.

    WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
  -- some computations here
END LOOP;

WHILE NOT done LOOP
  -- some computations here
END LOOP;

  • Bucle FOR (variante de entero) 

    [<<label>>]
FOR name IN [ REVERSE ] expression .. expression LOOP
  statements
END LOOP [ label ];

    El bucle FOR (variante de entero) crea un bucle que se repite a lo largo de un intervalo de valores enteros. El nombre de la variable se define automáticamente como de tipo entero y solo existe dentro del bucle. Se hace caso omiso de cualquier definición existente del nombre de la variable dentro del bucle. Las dos expresiones que indican los límites inferior y superior del intervalo se evalúan una vez cuando se entra en el bucle. Si especifica REVERSE, el valor de paso se sustrae, en lugar de sumarse después de cada iteración.

    Si el límite inferior es mayor que el superior (o menor en el caso de REVERSE), el cuerpo del bucle no se ejecuta. No se genera un error.

    Si se adjunta una etiqueta al bucle FOR, puede hacer referencia a la variable de bucle de valor entero con un nombre cualificado, usando esa etiqueta.

    A continuación se muestra un ejemplo.

    FOR i IN 1..10 LOOP
  -- i will take on the values 1,2,3,4,5,6,7,8,9,10 within the loop
END LOOP;

FOR i IN REVERSE 10..1 LOOP
  -- i will take on the values 10,9,8,7,6,5,4,3,2,1 within the loop
END LOOP;

  • Bucle FOR (variante de conjunto de resultados) 

    [<<label>>]
FOR target IN query LOOP
  statements
END LOOP [ label ];

    El destino es una variable de registro o lista separada por comas de variables escalares. Se asigna sucesivamente al destino cada fila resultante de la consulta; el cuerpo del bucle se ejecuta para cada fila.

    El bucle FOR (variante de conjunto de resultados) permite que un procedimiento almacenado se repita a lo largo de los resultados de una consulta y manipula esos datos en consecuencia.

    A continuación se muestra un ejemplo.

    CREATE PROCEDURE cs_refresh_reports() AS $$
DECLARE
  reports RECORD;
BEGIN
  FOR reports IN SELECT * FROM cs_reports ORDER BY sort_key LOOP
    -- Now "reports" has one record from cs_reports
    EXECUTE 'INSERT INTO ' || quote_ident(reports.report_name) || ' ' || reports.report_query;
  END LOOP;
  RETURN;
END;
$$ LANGUAGE plpgsql;

  • Bucle FOR con SQL dinámico

    [<<label>>]
FOR record_or_row IN EXECUTE text_expression LOOP 
  statements
END LOOP;

    El bucle FOR con SQL dinámico permite a un procedimiento almacenado repetirse a lo largo de los resultados de una consulta dinámica y manipula esos datos en consecuencia.

    A continuación se muestra un ejemplo.

    CREATE OR REPLACE PROCEDURE for_loop_dynamic_sql(x int)
LANGUAGE plpgsql
AS $$
DECLARE
  rec RECORD;
  query text;
BEGIN
  query := 'SELECT * FROM tbl_dynamic_sql LIMIT ' || x;
  FOR rec IN EXECUTE query
  LOOP
    RAISE INFO 'a %', rec.a;
  END LOOP;
END;
$$;

Cursores

En lugar de ejecutar una consulta completa a la vez, puede establecer un cursor. Un cursor encapsula una consulta y después lee el resultado de la consulta una pocas filas a la vez. Una de las razones por las que se hace esto es evitar desbordar la memoria cuando el resultado contiene un gran número de filas. Otra razón es devolver una referencia a un cursor que ha creado un procedimiento almacenado, lo que permite al intermediario leer las filas. Esta estrategia ofrece una forma eficiente de devolver grandes conjuntos de filas desde los procedimientos almacenados.

Para utilizar los cursores en un procedimiento almacenado NONATOMIC, coloque el bucle de cursor entre START TRANSACTION...COMMIT.

Para establecer un cursor, primero debe declarar una variable de cursor. Todos los accesos a cursores en PL/pgSQL pasan por variables de cursor, que son siempre del tipo de datos especial refcursor. Un tipo de datos refcursor simplemente contiene una referencia a un cursor.

Una manera de crear una variable de cursor es declararla como una variable de tipo refcursor. También puede utilizar la siguiente sintaxis de declaración del cursor.

name CURSOR [ ( arguments ) ] FOR query ;

En lo anterior, la lista de argumentos (si se especifica) es una lista separada por comas de pares de nombre tipo de datos que definen los nombres que se sustituirán por valores de los parámetros de la consulta. Los valores reales que se sustituirán para estos nombres se especifican más adelante, cuando se abre el cursor.

A continuación se muestran ejemplos.

DECLARE
  curs1 refcursor;
  curs2 CURSOR FOR SELECT * FROM tenk1;
  curs3 CURSOR (key integer) IS SELECT * FROM tenk1 WHERE unique1 = key;

Estas tres variables tienen el tipo de datos refcursor, pero la primera se puede usar con cualquier consulta. En contraste, la segunda tiene una consulta totalmente especificada vinculada a ella y la última tiene una consulta parametrizada vinculada a ella. El valor key se sustituye por un valor de parámetro de número entero cuando se abre el cursor. Se dice que la variable curs1 es no vinculadaporque no está vinculada a ninguna consulta en particular.

Antes de usar el cursor para recuperar filas, debe abrirse el cursor. PL/pgSQL tiene tres formas de la instrucción OPEN, dos de las cuales utilizan variables de cursor no vinculadas mientras que la tercera utiliza una variable de cursor vinculada.

  • Abrir para seleccionar: la variable de cursor se abre y se le entrega la consulta específica que debe ejecutar. El cursor no puede estar abierto aun. Además, tiene que haberse declarado como un cursor no vinculado (es decir, como una variable refcursor simple). La consulta SELECT se trata del mismo modo que otras instrucciones SELECT en PL/pgSQL. 

    OPEN cursor_name FOR SELECT ...;

    A continuación se muestra un ejemplo.

    OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;

  • Abrir para ejecutar: la variable de cursor se abre y se le entrega la consulta específica que debe ejecutar. El cursor no puede estar abierto aun. Además, tiene que haberse declarado como un cursor no vinculado (es decir, como una variable refcursor simple). La consulta se especifica como una expresión de cadena del mismo modo que el comando EXECUTE. Esta estrategia proporciona flexibilidad porque la consulta puede variar de una ejecución a otra.

    OPEN cursor_name FOR EXECUTE query_string;

    A continuación se muestra un ejemplo.

    OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1);

  • Abrir un cursor vinculado: esta forma de OPEN se usa para abrir una variable de cursor que tenía una consulta vinculada cuando se declaró. El cursor no puede estar abierto aun. Debe aparecer una lista de expresiones de argumento si, y solo si, se había declarado que el cursor tenía argumentos. Estos valores se sustituyen en la consulta. 

    OPEN bound_cursor_name [ ( argument_values ) ];

    A continuación se muestra un ejemplo.

    OPEN curs2;
OPEN curs3(42);

Después de abrir un cursor, puede trabajar con él usando las instrucciones descritas a continuación. Estas instrucciones no tienen que ocurrir necesariamente en el mismo procedimiento almacenado en el que inicialmente se abrió el cursor. Puede devolver un valor refcursor de una procedimiento almacenado y dejar que el intermediario opere en el cursor. Todos los portales se cierran de forma implícita al final de la transacción. Por lo tanto, un valor refcursor se puede utilizar para hacer referencia a un cursor abierto solo hasta el final de la transacción.

  • FETCH recupera la siguiente fila del cursor en un destino. El destino puede ser una variable de fila, una variable de registro o una lista separada por comas de variables simples, como con SELECT INTO. Al igual que con SELECT INTO, puede comprobar la variable especial FOUND para ver si se ha obtenido una fila.

    FETCH cursor INTO target;

    A continuación se muestra un ejemplo.

    FETCH curs1 INTO rowvar;

  • CLOSE cierra el portar subyacente de un cursor abierto. Puede usar esta instrucción para liberar recursos antes del final de la transacción. También puede usar esta instrucción para liberar la variable de cursor para poder abrirla de nuevo.

    CLOSE cursor;

    A continuación se muestra un ejemplo.

    CLOSE curs1;

RAISE

Utilice la instrucción RAISE level para comunicar mensajes y generar errores.

RAISE level 'format' [, variable [, ...]];

Los niveles posibles son NOTICE, INFO, LOG, WARNING y EXCEPTION. EXCEPTION genera un error, que normalmente cancela la transacción en curso. Los demás niveles solo generan mensajes con distintos grados de prioridad.

Dentro de la cadena de formato, % se sustituye por la siguiente representación de la cadena del argumento opcional. Escriba %% para emitir un % literal. Actualmente, los argumentos opcionales deben ser variables simples, no expresiones, y el formato debe ser una simple cadena literal.

En el siguiente ejemplo, el valor de v_job_id sustituye el % en la cadena.

RAISE NOTICE 'Calling cs_create_job(%)', v_job_id;

Utilice la instrucción RAISE para volver a generar la excepción detectada por un bloque de gestión de excepciones. Esta instrucción solo es válida en bloques de gestión de excepciones de procedimientos almacenados de modo NONATOMIC.

RAISE;

Control de la transacción

Puede trabajar con instrucciones de control de la transacción en el lenguaje PL/pgSQL que utiliza Amazon Redshift. Para más información sobre el uso de las instrucciones COMMIT, ROLLBACK y TRUNCATE en un proceso almacenado, vea Administración de transacciones.

En los procedimientos almacenados de modo NONATOMIC, use START TRANSACTION para iniciar un bloque de transacción.

START TRANSACTION;
nota

La instrucción START TRANSACTION de PL/pgSQL difiere del comando SQL START TRANSACTION en los siguientes aspectos:

  • En los procedimientos almacenados, START TRANSACTION no es sinónimo de BEGIN.

  • La instrucción PL/pgSQL no admite palabras clave opcionales de nivel de aislamiento ni de permiso de acceso.