1. Forum
  2. Usenet
  3. COMP.DATABASES.INGRES

  • Was I wrong to expect this to work?

    From Roy Hann@21:1/5 to All on Thu Aug 4 15:24:48 2022
    * 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)
  • From Roy Hann@21:1/5 to G Jones on Fri Aug 5 08:11:12 2022
    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)
  • From G Jones@21:1/5 to Roy Hann on Fri Aug 5 00:20:32 2022
    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)