* create sequence genno as integer;
* create procedure next_vno result row (integer)
* as declare vno integer not null;
* begin
* select genno.nextval * 10 + generate_digit('LUHN_A',genno.currval) into :vno;
* return row (:vno);
* end
* \g
Executing . . .

continue
* select * from next_vno() \g
Executing . . .

E_LQ003A Cannot start up 'select' query.
Unexpected initial protocol response.

Roy

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

G Jones wrote:

On Thursday, August 4, 2022 at 4:24:50 PM UTC+1, Roy Hann wrote:

* create sequence genno as integer;
* create procedure next_vno result row (integer)
* as declare vno integer not null;
* begin
* select genno.nextval * 10 + generate_digit('LUHN_A',genno.currval) into :vno;
* return row (:vno);
* end
* \g
Executing . . .

continue
* select * from next_vno() \g
Executing . . .

E_LQ003A Cannot start up 'select' query.
Unexpected initial protocol response.

Roy

I get E_SC0206, but splitting the query up slightly persuades it to produce a result:

create procedure next_vno result row (integer) as
declare v1 integer not null;
v2 integer not null;
v3 integer not null;
begin
select genno.nextval, genno.currval into :v1,:v2;
select :v1 * 10 + generate_digit('LUHN_A',:v2) into :v3;
return row(:v3);
end;

(Using 11.1 +p15773).

Thanks Geraint.

I did roughly the same thing so currval wasn't needed at all. But your
way confirms currval is not the source of the problem.

Incidentally I had previously tried using a view, on the basis that
any query can be a view. It turns out querying a sequence in a view is explicitly disallowed.

I cannot think what the justification would be. It's an annoying
exceptional case. But hey-ho; it's SQL. :-P

Roy

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Thursday, August 4, 2022 at 4:24:50 PM UTC+1, Roy Hann wrote:

* create sequence genno as integer;
* create procedure next_vno result row (integer)
* as declare vno integer not null;
* begin
* select genno.nextval * 10 + generate_digit('LUHN_A',genno.currval) into :vno;
* return row (:vno);
* end
* \g
Executing . . .

continue
* select * from next_vno() \g
Executing . . .

E_LQ003A Cannot start up 'select' query.
Unexpected initial protocol response.

Roy

I get E_SC0206, but splitting the query up slightly persuades it to produce a result:

create procedure next_vno result row (integer) as
declare v1 integer not null;
v2 integer not null;
v3 integer not null;
begin
select genno.nextval, genno.currval into :v1,:v2;
select :v1 * 10 + generate_digit('LUHN_A',:v2) into :v3;
return row(:v3);
end;

(Using 11.1 +p15773).

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)