How to return a newly generated ID from a stored procedure in Firebird

Question

I have a procedure which inserts record into a table. I also have a trigger which auto-increments sequence on BEFORE INSERT (also there is identity type). I'd like to operate with its result, e.g. highlight a new record in a table on a website for example. Is usage of RETURNS with GEN_ID(<sequence>, 0) the only way? If so, there is a race condition, as far as I know, isn't it? My ID may not be mine if someone's transaction succeeds first.

There is also a RETURNING clause, but as far as I know it could be used in DML queries like in execute block or dynamic statements as in INSERT INTO ... RETURNING ID?

Answer 1

To return a generated id from a stored procedure, all you have to do is use the RETURNING clause in your INSERT statement, and assign it to one of the output variables of the stored procedure.

As a simple example:

create table example_table (
  id integer generated by default as identity constraint pk_example_table primary key,
  val varchar(50)
);

set term #;
create procedure add_to_example(new_val type of column example_table.val) 
  returns (new_id type of column example_table.id)
as
begin
  insert into example_table (val) values (:new_val) returning id into new_id;
end#
set term ;#

You can then execute this as:

execute procedure add_to_example('abc');

For example:

SQL> execute procedure add_to_example('abc');

      NEW_ID
============
           1

SQL> execute procedure add_to_example('bce');

      NEW_ID
============
           2

SQL>