Devolver un conjunto de resultados

Normalmente, un procedimiento NZPLSQL devuelve un valor de retorno exclusivo, pero también puede devolver un conjunto de resultados en el formato de una tabla especificada.

Para crear un procedimiento almacenado que devuelva un conjunto de resultados:

Defina el procedimiento almacenado con un valor de retorno de “RETURNS REFTABLE (<table-name>)” para indicar que devuelve un conjunto de resultados que es parecido a la tabla especificada.
Dentro del cuerpo del procedimiento, utilice la variable REFTABLENAME para hacer referencia a la tabla de resultados.

La tabla especificada en el valor RETURNS debe existir en el momento en que se crea el procedimiento almacenado, aunque la tabla puede estar vacía. La tabla continúa existiendo después de que se complete el procedimiento almacenado. No puede descartar la tabla de referencia mientras se define el procedimiento almacenado. (Es decir, debe descartar el procedimiento almacenado o modificarlo para que devuelva una tabla de referencia diferente antes de que pueda descartar la tabla.)

Por ejemplo, el siguiente procedimiento almacenado, returntwo, devuelve un conjunto de resultados utilizando la característica de tabla de referencia. La tabla de referencia que utiliza, tbl, se ha definido anteriormente utilizando el mandato siguiente:

CREATE TABLE tbl (i INT4, i2 bigint);

A continuación se proporciona una descripción de tbl:

DEV.SCH1(ADMIN)=> \d tbl
                  Table "TBL"
 Attribute |  Type   | Modifier | Default Value 
-----------+---------+----------+---------------
 I         | INTEGER |          | 
 I2        | BIGINT  |          | 
Distributed on hash: "I"

Después de confirmar que la tabla de referencia existe, puede utilizar el mandato siguiente para definir el procedimiento almacenado returntwo:

DEV.SCH1(ADMIN)=> CREATE OR REPLACE PROCEDURE returntwo(timestamp) RETURNS 
REFTABLE(tbl) LANGUAGE NZPLSQL AS 
BEGIN_PROC
  BEGIN
    EXECUTE IMMEDIATE 'INSERT INTO ' || REFTABLENAME ||' values (1,1)';
    EXECUTE IMMEDIATE 'INSERT INTO ' || REFTABLENAME ||' values (2,2)';
    RETURN REFTABLE;
  END;
  END_PROC;

A continuación se proporciona una llamada de ejemplo al procedimiento almacenado returntwo:

DEV.SCH1(ADMIN)=> EXECUTE PROCEDURE returntwo(now());
 I | I2 
---+----
 1 |  1
 2 |  2
(2 rows)

Restricción: No se puede especificar una cláusula WHERE en una consulta que llame a un procedimiento almacenado que devuelva un conjunto de resultados.

Cuando se llama o se invoca el procedimiento almacenado utilizando un mandato SQL, por ejemplo SELECT procedure(), CALL procedure(), EXECUTE procedure(), etc., la base de datos realiza lo siguiente:

Genera un nombre de tabla TEMPFUNC<oid> donde oid es el ID de objeto del procedimiento que se ha invocado
Comprueba si el nombre de tabla existe; si es así, emite un mandato DROP TABLE <temp-table-name>
Emite CREATE TEMPORARY TABLE <temp-table-name> as select * from <table-name> LIMIT 0 para crear la tabla para los resultados establecidos sin contenido inicial
Devuelve el resultado de SELECT * from <temp-table-name> donde proc(args) es NULL (Esta situación es la única situación en la que se permite invocar un procedimiento almacenado con una cláusula FROM y donde el valor de retorno se utiliza como parte de una consulta.)

Para utilizarlo en un procedimiento, debe insertar los resultados en <temp-table-name> utilizando la variable REFTABLENAME para obtener el nombre. Este mandato SQL se debe invocar dinámicamente para que use la variable.

Además, debe devolver NULL en el procedimiento mediante uno de los siguientes medios:

RETURN REFTABLE;
RETURN NULL;
return;
No se especifica una cláusula RETURN.

Si no devuelve NULL, el procedimiento devuelve un error. El método recomendado para devolver NULL es RETURN REFTABLE.

Un procedimiento REFTABLE puede llamar a otro, pero se producirán resultados inusuales si un procedimiento REFTABLE se llama a sí mismo (directamente o de forma recurrente) debido a la lógica de tabla temporal; por consiguiente, evite diseñar un procedimiento REFTABLE que se llame a sí mismo.

El acceso de bases de datos cruzadas para un procedimiento REFTABLE debe funcionar sin problemas porque la tabla temporal se creará en la base de datos local; recuperará la forma de la definición REFTABLE de la otra base de datos.

El SQL que se ejecuta (por ejemplo, CREATE TEMPORARY TABLE, DROP TABLE) utiliza el ID de propietario del procedimiento como ID de usuario en vigor, si se establece EXECUTE AS OWNER; de lo contrario, si se establece EXECUTE AS CALLER, el SQL utiliza el ID de usuario de la cuenta que llama o invoca el procedimiento.