* 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

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).

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