Is it possible to return multiple lines to a Firebird stored procedure?

Question

I was wondering if it is possible to return more than one row found in a request SELECTin Firebird 1.5, as shown below:

| FIELD1 | FIELD 2 |
--------------------
| 1.00   | 1       |
| 2.00   | 2       |
| 3.00   | 3       |

SET TERM /;

CREATE OR ALTER PROCEDURE TEST
RETURNS (VARIABLE1 DOUBLE PRECISION, VARIABLE2 INTEGER)
AS
BEGIN
  SELECT FIELD1, FIELD2 FROM TABLE INTO :VARIABLE1, :VARIABLE2;
END/

EXECUTE PROCEDURE TEST/

SET TERM ;/

Assuming the query returns more than one result / row, the following error is thrown:

Runtime Error, SQLCODE = -811

Multiple rows in a single selection

Obviously, the Firebird mechanism does not allow me to return more than one value in a variable. I am developing Python software where I want to get, for example, a tuple, something like this ((1.00, 1), (2.00, 2), (3.00, 3)], based on the TEST procedure. I use the kinterbasdb module for connect to gdb.

Is there any way to do this?

Answer 1

Firebird :

• :

  execute procedure <procedurename>
  

• :

  select * from <procedurename>
  

  :

  select * from <procedurename>(param, ...)
  

SUSPEND, . - , .

. execute procedure , , , : SUSPEND ! "" , Firebird 3, .

" " - :

SELECT FIELD1, FIELD2 FROM TABLE INTO :VARIABLE1, :VARIABLE2;

select ... into ... (, select). , for select ... into ... do, .

SUSPEND :

CREATE OR ALTER PROCEDURE TEST
RETURNS (VARIABLE1 DOUBLE PRECISION, VARIABLE2 INTEGER)
AS
BEGIN
  FOR SELECT FIELD1, FIELD2 FROM TABLE INTO :VARIABLE1, :VARIABLE2
  DO
  BEGIN
      SUSPEND;
  END
END

BEGIN ... END - , .

:

select variable1, variable2 from test;

Answer 2

, , " ". , - :

CREATE OR ALTER PROCEDURE TEST
RETURNS (VARIABLE1 DOUBLE PRECISION, VARIABLE2 INTEGER)
AS
BEGIN
  for SELECT FIELD1, FIELD2 FROM TABLE INTO :VARIABLE1, :VARIABLE2
   do SUSPEND;
END

SELECT, :

SELECT * FROM TEST;

!