Estructura de PL/pgSQL

PL/pgSQL es un lenguaje de procedimientos con muchas de las mismas construcciones que otros lenguajes de procedimientos.

Bloque

PL/pgSQL es un lenguaje estructurado en bloques. El cuerpo completo de un procedimiento se define en un bloque, que contiene declaraciones de variables e instrucciones de PL/pgSQL. Una instrucción también puede ser un bloque anidado, o subbloque.

Debe finalizar las instrucciones y declaraciones con un punto y coma. Siga la palabra clave END en un bloque o subbloque con un punto y coma. No use punto y coma después de las palabras clave DECLARE y BEGIN.

Puede escribir todas las palabras clave e identificadores con una combinación de mayúsculas y minúsculas. Los identificadores se convierten en minúsculas de forma implícita a menos que estén entrecomillados (con comillas dobles).

Un guion doble (--) inicia un comentario que se extiende hasta el final de la línea. A /* marca el inicio de un bloque de comentarios que se extiende hasta la siguiente ocurrencia de */. Los bloques de comentarios no pueden anidarse. No obstante, los comentarios con guiones dobles pueden encerrarse en un bloque de comentarios, y un guion doble puede ocultar los delimitadores de bloques de comentarios /* y */.

Cualquier instrucción en la sección de instrucciones de un bloque puede ser un subbloque. Puede utilizar subbloques con fines de agrupación lógica o para localizar variables en un pequeño grupo de instrucciones.

[ <<label>> ]
[ DECLARE
  declarations ]
BEGIN
  statements
END [ label ];  

Las variables declaradas en la sección de declaraciones antes de un bloque se inicializan con sus valores predeterminados cada vez que se introduce el bloque. En otras palabras, no se inicializan solo una vez por cada llamada a la función.

A continuación se muestra un ejemplo.

CREATE PROCEDURE update_value() AS $$
DECLARE
  value integer := 20;
BEGIN
  RAISE NOTICE 'Value here is %', value;  -- Value here is 20
  value := 50;
  --
  -- Create a subblock
  --
  DECLARE
    value integer := 80;
  BEGIN
    RAISE NOTICE 'Value here is %', value;  -- Value here is 80
  END;

  RAISE NOTICE 'Value here is %', value;  -- Value here is 50
END;
$$ LANGUAGE plpgsql;               

Utilice una etiqueta para identificar el bloque que se usará en una instrucción EXIT o para cualificar los nombres de las variables declaradas en el bloque.

No confunda el uso de BEGIN/END para la agrupación de instrucciones en PL/pgSQL con los comandos de la base de datos para el control de transacciones. BEGIN y END en PL/pgSQL solo son para agrupar. No inician ni finalizan una transacción.

Declaración de variables

Declare todas las variables de un bloque, excepto las variables de bucle, en la sección DECLARE del bloque. Las variables pueden utilizar cualquier tipo de datos de Amazon Redshift válido. Para conocer los tipos de datos admitidos, consulte Tipos de datos.

Las variables de PL/pgSQL pueden ser cualquier tipo de datos admitido por Amazon Redshift, además de RECORD y refcursor. Para obtener más información acerca de RECORD, consulte Tipos de registro. Para obtener más información acerca de refcursor, consulte Cursores.

DECLARE
name [ CONSTANT ] type [ NOT NULL ] [ { DEFAULT | := } expression ];

A continuación se muestran declaraciones de variables de ejemplo.

customerID integer;
numberofitems numeric(6);
link varchar;
onerow RECORD;

La variable de bucle para la iteración de un bucle FOR a lo largo de un intervalo de valores enteros se declara automáticamente como una variable con valor entero.

La cláusula DEFAULT, si se proporciona, especifica el valor inicial asignado a la variable cuando se introduce el bloque. Si la cláusula DEFAULT no se proporciona, la variable se inicializa con el valor NULL de SQL. La opción CONSTANT impide la asignación de otros valores a la variable, de modo que su valor permanece constante durante la duración del bloque. Si se especifica NOT NULL, una asignación de un valor NULL produce un error de tiempo de ejecución. Todas las variables declaradas como NOT NULL deben tener un valor no nulo especificado de manera predeterminada.

El valor predeterminado se evalúa cada vez que se introduce el bloque. Por ejemplo, la asignación de now() a una variable de tipo timestamp provoca que la variable tenga la fecha y hora actual del momento de llamar a la función, no del momento de la compilación previa de la función.

quantity INTEGER DEFAULT 32;
url VARCHAR := 'http://mysite.com';
user_id CONSTANT INTEGER := 10;

El tipo de datos refcursor es el tipo de datos de variables de cursor dentro de procedimientos almacenados. Un procedimiento almacenado puede devolver un valor refcursor. Para obtener más información, consulte Devolución de un conjunto de resultados desde un procedimiento almacenado.

Declaración de alias

Si la firma del procedimiento almacenado omite el nombre del argumento, puede declarar un alias para el argumento.

name ALIAS FOR $n;

Variables integradas

Se admiten las siguientes variables integradas:

FOUND es una variable especial de tipo booleano. FOUND se inicializa como false en cada llamada al procedimiento. FOUND se establece mediante los siguientes tipos de instrucciones:

A continuación se muestra un ejemplo.

CREATE TABLE employee(empname varchar);
CREATE OR REPLACE PROCEDURE show_found()
AS  $$
DECLARE
  myrec record;
BEGIN
  SELECT INTO myrec * FROM employee WHERE empname = 'John';
  IF NOT FOUND THEN
    RAISE EXCEPTION 'employee John not found';
  END IF;
END;
$$ LANGUAGE plpgsql;

En un controlador de excepciones, la variable especial SQLSTATE contiene el código de error que se corresponde a la excepción que se generó. La variable especial SQLSTATE contiene el mensaje de error asociado con la excepción. Estas variables están sin definir fuera de los controladores de excepciones y muestran un error si se utilizan.

A continuación se muestra un ejemplo.

CREATE OR REPLACE PROCEDURE sqlstate_sqlerrm() AS
$$
BEGIN
  UPDATE employee SET firstname = 'Adam' WHERE lastname = 'Smith';
  EXECUTE 'select invalid';
  EXCEPTION WHEN OTHERS THEN
  RAISE INFO 'error message SQLERRM %', SQLERRM;
  RAISE INFO 'error message SQLSTATE %', SQLSTATE;
END;
$$ LANGUAGE plpgsql;               

ROW_COUNT se utiliza con el comando GET DIAGNOSTICS. Muestra el número de filas procesadas por el último comando SQL enviado al motor SQL.

A continuación se muestra un ejemplo.

CREATE OR REPLACE PROCEDURE sp_row_count() AS
$$
DECLARE
  integer_var int;
BEGIN
  INSERT INTO tbl_row_count VALUES(1);
  GET DIAGNOSTICS integer_var := ROW_COUNT;
  RAISE INFO 'rows inserted = %', integer_var;
END;
$$ LANGUAGE plpgsql;               

Tipos de registro

Un tipo RECORD no es un auténtico tipo de datos, solo un marcador de posición. Las variables de tipo registro toman la estructura actual de la fila que se les asigna durante un comando SELECT o FOR. La subestructura de una variable de registro puede cambiar cada vez que se le asigne un valor. Hasta que no se asigne un valor a una variable de registro por primera vez, esta no tendrá subestructura. Los intentos de acceder a un campo en ella producen un error de tiempo de ejecución.

name RECORD;

A continuación se muestra un ejemplo.

CREATE TABLE tbl_record(a int, b int);
INSERT INTO tbl_record VALUES(1, 2);
CREATE OR REPLACE PROCEDURE record_example()
LANGUAGE plpgsql
AS $$
DECLARE
  rec RECORD;
BEGIN
  FOR rec IN SELECT a FROM tbl_record
  LOOP
    RAISE INFO 'a = %', rec.a;
  END LOOP;
END;
$$;