Is it possible to return multiple rows in a Firebird's stored procedure?

Question

I was wondering if is possible to return more than one row found in a SELECT query in Firebird 1.5, like 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:

Statement failed, SQLCODE = -811

Multiple rows in singleton select

Obviously, the Firebird's engine doesn't allow me to return more than one value in a variable. I'm developing a Python's software where I want to get, as a tuple, for example, something like this [(1.00, 1), (2.00, 2), (3.00, 3)], based on the procedure TEST. I'm using the kinterbasdb module to connect with the GDB.

Is there a way to do that?

Answer 1

Firebird has two types of stored procedures:

• Executable procedures produce at most one row, and are executed using:

    execute procedure <procedurename>
  

• Selectable procedures can produce multiple rows and are executed using:

    select * from <procedurename>
  

  Or with parameters:

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

A selectable procedures contains the SUSPEND keyword which outputs the row and waits for the next fetch. Presence of this keyword is the only thing that distinguishes between the two types.

Warning: It is possible to use execute procedure with a selectable procedure, but in that case it will only produce a single row, and execution will end after that first row has been produced: the rest of the stored procedure after SUSPEND will not be executed! It also used to be possible to 'select' from an executable stored procedure, but in Firebird 3 that is no longer possible.

The specific reason of the error "Multiple rows in singleton select" is the line:

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

This select ... into ... statement is only allowed to produce a single row (aka a singleton select). If you expect more than one row, you need to use for select ... into ... do, which allows you to iterate over the resulting rows.

Combining this with suspend will then result in:

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

The BEGIN ... END-block is technically not necessary, but I prefer to always use a block here.

You can then execute this with:

select variable1, variable2 from test;

Answer 2

What you need is called "Selectable Stored Procedure". Basically, you must change your code to something like this:

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

and call the procedure with a SELECT, like this:

SELECT * FROM TEST;

Good luck!