Commented by: @mrotteveel

The first parameter to GEN_ID is an object, not a string value with the object name, so you can't use the value of a column here. The attempt with "r.RDB$GENERATOR_NAME" fails as Firebird expects a plain object name here.

In this specific case, RDB$GENERATOR_NAME seems to work, as RDB$GENERATOR_NAME is **also** a system generator in Firebird (description is "Implicit generator name"). So you are querying the value of the generator RDB$GENERATOR_NAME for each row in RDB$GENERATORS.

If you want to query the current values of all generators, you will need to construct an EXECUTE BLOCK (or stored procedure) that dynamically executes a query with GEN_ID(name, 0) for each individual generator (using EXECUTE STATEMENT).

Commented by: @mrotteveel

For example (removed most columns to keep it simple):

set term #⁠;

execute block
returns (generatorName varchar(31), generatorValue bigint)
as
begin
for SELECT r.RDB$GENERATOR_NAME FROM RDB$GENERATORS r INTO generatorName do
begin
execute statement 'select gen_id("'||generatorName||'",0) from rdb$database'
into generatorValue;
suspend;
end
end#⁠

set term ;#⁠

Commented by: @mrotteveel

As another alternative, you could define a function that does this, and use that in your query:

set term #⁠;

create function getGeneratorValue(generatorName varchar(31))
returns bigint
as
declare variable generatorValue bigint;
begin
execute statement 'select gen_id("'||generatorName||'",0) from rdb$database'
into generatorValue;
return generatorValue;
end#⁠

set term ;#⁠

Modified query:

SELECT r.RDB$GENERATOR_NAME, r.RDB$GENERATOR_ID, r.RDB$SYSTEM_FLAG,
r.RDB$DESCRIPTION, r.RDB$SECURITY_CLASS, r.RDB$OWNER_NAME,
r.RDB$INITIAL_VALUE, r.RDB$GENERATOR_INCREMENT,
getGeneratorValue(r.RDB$GENERATOR_NAME) AS V
FROM RDB$GENERATORS r

Commented by: @livius2

>>The first parameter to GEN_ID is an object, not a string value with the object name

then why this work?
select gen_id("'||generatorName||'",0) from rdb$database

it is not an object only string value

And are you sure that this is intentional aad should not be modified/fixed?

Commented by: @mrotteveel

My code is using 'select gen_id("'||generatorName||'",0) from rdb$database' (notice the single quotes!). This code constructs string(!) with a query with a quoted object name. This yields the following query in the form of a string

select gen_id("<generator-name>", 0) from rdb$database

and the string with this query is given to EXECUTE STATEMENT to execute dynamically.

I use double quotes around the object name, because in a dialect 3 database that quotes the object name, and it is possible to have generators with a case sensitive name (eq create sequence abc creates ABC, while create sequence "abc" creates abc, and you'll need to use "abc" to query for it).

This behavior is intentional. What you propose would be equivalent of suggesting that it should be possible to use a string value to specify the column name in a query. For example, select 'RDB$GENERATOR_NAME' from RDB$GENERATORS, which simply produces the string value 'RDB$GENERATOR_NAME' for each row and not the value in the column RDB$GENERATOR_NAME.

As a secondary reason, your original problem already demonstrates why it shouldn't be possible: how would Firebird decide what you mean with

select gen_id(RDB$GENERATOR_NAME,0) from RDB$GENERATORS

Would it be the value of the column or the generator with the name RDB$GENERATOR_NAME? And changing GEN_ID to take (only) a string value would break all existing Firebird code out there.

Commented by: @livius2

Ok, i have thinked that this can be overloaded and gen_id() will use object or string as name.
As it is a function not simple token like in your second example "select 'RDB$GENERATOR_NAME' from RDB$GENERATOR"

Will be good to have possibility to get db_object by name like gen_id(db_object_by_name('gen_name'), 0)
but this is to trivial case to make such changes as it can be accomplished in different way

one case is this possible to change parsing error message "Token unknown" to something like "gen_id only take generator object as parameter"

Commented by: Sean Leyne (seanleyne)

Modified subject to reflect the real requirement as outlined in the comments

Commented by: @mrotteveel

Even if we would modify GEN_ID to take a string parameter, the use case of the initial problem would be impossible: using a column in that location would lead to an ambiguity for the parser (the "is RDB$GENERATOR_NAME a generator name, or is it actually a column name of a string column with the name of a generator"-problem), so you would only be able to accept a string literal or parameter there. Supporting the use case of the original problem would require the introduction of a separate function, which as demonstrated above can easily be created by a user who really needs it regularly.

Modifying the error message might be possible, but would likely lead to additional complexity in the parser for little benefit (IMHO), as similar situations elsewhere will also lead to a token unknown error.