Retorno de um conjunto de resultados

É possível retornar um conjunto de resultados usando um cursor ou uma tabela temporária.

Retorno de um cursor

Para retornar um cursor, crie um procedimento com um argumento INOUT definido com um tipo de dados refcursor. Ao chamar o procedimento, dê um nome ao cursor. Depois, você pode buscar os resultados do cursor pelo nome.

O exemplo a seguir cria um procedimento chamado get_result_set com um argumento INOUT chamado rs_out usando o tipo de dados refcursor. O procedimento abre o cursor usando uma instrução SELECT.

CREATE OR REPLACE PROCEDURE get_result_set (param IN integer, rs_out INOUT refcursor)
AS $$
BEGIN
  OPEN rs_out FOR SELECT * FROM fact_tbl where id >= param;
END;
$$ LANGUAGE plpgsql;

O comando CALL a seguir abre o cursor com o nome mycursor. Use cursores somente nas transações.

BEGIN;
CALL get_result_set(1, 'mycursor');   

Depois que o cursor for aberto, você pode buscar a partir do cursor, conforme mostram os exemplos a seguir.

FETCH ALL FROM mycursor;

    id | secondary_id | name
-------+--------------+---------
     1 |            1 | Joe
     1 |            2 | Ed
     2 |            1 | Mary
     1 |            3 | Mike
(4 rows)   

Ao final, a transação é confirmada ou revertida.

COMMIT;   

Um cursor retornado por um procedimento armazenado está sujeito às mesmas restrições e considerações de performance, conforme descrito em DECLARE CURSOR. Para obter mais informações, consulte Restrições de cursor.

O exemplo a seguir mostra a chamada do procedimento armazenado get_result_set usando um tipo de dados refcursor a partir do JDBC. O 'mycursor' literal (o nome do cursor) é enviado para a prepareStatement. Depois, os resultados são obtidos do ResultSet.

static void refcursor_example(Connection conn) throws SQLException {
    conn.setAutoCommit(false);
    PreparedStatement proc = conn.prepareStatement("CALL get_result_set(1, 'mycursor')");
    proc.execute();
    ResultSet rs = statement.executeQuery("fetch all from mycursor");
    while (rs.next()) {
      int n = rs.getInt(1);
      System.out.println("n " + n);
    }

Uso de uma tabela temporária

Para retornar resultados, você pode retornar um identificador para uma tabela temporária que contém linhas de resultados. O cliente pode fornecer um nome como um parâmetro para o procedimento armazenado. Dentro do procedimento armazenado, SQL dinâmico pode ser usado para operar na tabela temporária. Por exemplo:

CREATE PROCEDURE get_result_set(param IN integer, tmp_name INOUT varchar(256)) as $$
DECLARE
  row record;
BEGIN
  EXECUTE 'drop table if exists ' || tmp_name;
  EXECUTE 'create temp table ' || tmp_name || ' as select * from fact_tbl where id <= ' || param;
END;
$$ LANGUAGE plpgsql;

CALL get_result_set(2, 'myresult');
 tmp_name
-----------
 myresult
(1 row)

SELECT * from myresult;
 id | secondary_id | name
----+--------------+------
  1 |            1 | Joe
  2 |            1 | Mary
  1 |            2 | Ed
  1 |            3 | Mike
(4 rows)