1

There is an error in the following function creating query:

CREATE FUNCTION `_increase_num` () RETURNS int(11)
RETURNS INT
READS SQL DATA
DETERMINISTIC
BEGIN
    RETURN IF(@counter, @counter:=@counter+1, @counter:=1);
END
  1. Any idea how to fix?
  2. Can I avoid using ; in the query above? The development framework doesn't allow multiple queries in one call and identifies this one as multiple queries.

EDIT1: The aim of the function to make each row unique, like an autoincreased primary key. But for some development reason I can't use autoincremented primary keys.

EDIT2: The error message is usual, that query failed. You have an error in your query.

EDIT3: I use the function like this way: SELECT _increase_num() as rownr, content FROM mytable

WORKING SOLUTION:

CREATE FUNCTION _increase_num() 
returns int 
    return IF(@counter, @counter:=@counter+1, @counter:=1)
  • There are a couple of obvious problems: RETURNS int(11) RETURNS INT. It does not appear to read any SQL DATA, you can probably skip that one. Is the function really deterministic? It's probably a bad idea to update a global variable via a function, can't you pass it as an argument to the function? – Lennart Jul 18 '17 at 19:12
  • It can also be helpful to provide the actual error message you got (if any). – RDFozz Jul 18 '17 at 19:21
  • Thanks for your answer and your fixes! The aim of the function to make line unique, like an autoincreased primary key. But for some development reason I can't use autoincremented primary keys. – klor Jul 18 '17 at 19:25
  • Edited my post. – klor Jul 18 '17 at 19:37
1

Here's a version that does not give a syntax error:

delimiter !!
drop function _increase_num !! 
CREATE FUNCTION _increase_num() 
returns int 
begin 
    return IF(@counter, @counter:=@counter+1, @counter:=1); 
end !!
delimiter ;

A bit of warning though, whenever counter looses its value it will start from 1. Also, I would not put any money on how well this works when the function is called in parallel from several processes.

Edit: ";" has a special meaning in stored procedure language, it separates statements within the procedure. This is why you normally change the statement terminator to something else:

delimiter @

However, for this trivial procedure, we don't need the begin/end block. That is:

CREATE FUNCTION _increase_num() 
returns int 
    return IF(@counter, @counter:=@counter+1, @counter:=1);

will do.

  • Thanks for your answer! Can I avoid using ; sign? I described reason in EDIT1. – klor Jul 18 '17 at 20:55
  • I got an error, that function doesn't exist to drop. – klor Jul 18 '17 at 21:06
  • well, don't drop it then ;-) I added some info – Lennart Jul 18 '17 at 21:30
  • Great solutions! But the ; sign is still not eliminated completely. I must have to avoid it. – klor Jul 18 '17 at 21:49
  • Have you tried removing it? – Lennart Jul 18 '17 at 21:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.