[Community Puzzle] Remove insert statements

Puzzles
#1

Coding Games and Programming Challenges to Code Better

Send your feedback or ask for help here!

Created by @nguyen.lam,validated by @Razovsky,@Regulus136 and @Drunkey.
If you have any issues, feel free to ping them.

#2

I think the test cases are missing many edge cases: e.g.

  • the insert, begin, end, or -- strings within a string literal;
  • the insert, begin, end, or ' strings within a comment;
  • multi-line insert with several of its lines ending with a comment (not just after the final ; )
  • nested begin… end blocks
#3

I’m afraid writing a proper SQL tokenizer is no longer “easy”… So the text should indeed have mentioned the simplifications made, especially regarding strings.
Other missing tests include spaces before or after a ‘;’, as well as at the start of a line (which has been specifically pointed out as possible for ‘insert’).

#4

:wave: Hi to all, and thanks to @nguyen.lam for this “parsing” puzzle :wink: !
I don’t know yet about SQL requests, but i know a bit about parsing text data using machine state …
→ and this one is not specially a difficult one.

Thanks for this pleasant exercise, and to the Aprovers too (Razovsky, @Regulus136 and @Drunkey) :slight_smile: !

Have sun :sun_with_face: , fun :dark_sunglasses: and CodinGames :keyboard: … :rofl: !

#6

Hi, I passed all tests, but my code still fails validators 6 (functions + insert) and 7 (multi-line insert instruction), however I didn’t find an issue. Does anyone have some edge cases for these two ?
I can also send my code by private message.
Thanks a lot.

#7

You may send me your code by private message and I’ll take a look.

#8

So I managed to complete the puzzle with the following test case :

Input:

25
--
-- Name: fp_insert(integer)
--
CREATE FUNCTION func.fp_insert(pid integer) RETURNS integer
    LANGUAGE plpgsql
    AS $$
declare
Begin
  return 1;
end;
$$;
-- TOC entry 49324
INSERT INTO func.ap VALUES ('PLAYER1', 'CODINGAME', 0);
INSERT into func.ap VALUES ('PLAYER2', 'CODINGAME', 1);
insert INTO func.ap VALUES ('PLAYER3', 'CODINGAME', 2);
--
CREATE TABLE winners (
    date_creation timestamp without time zone,
    game_name text,
    id character integer NOT NULL,
);
insert into winners(date_creation, game_name, id_ch) 
values('BOSS', 'CODINGAME', -1);
Insert INTO func.wr VALUES ('DON', '2024-04-28 21:00:00');
-- END Batch

Output:

--
-- Name: fp_insert(integer)
--
CREATE FUNCTION func.fp_insert(pid integer) RETURNS integer
    LANGUAGE plpgsql
    AS $$
declare
Begin
  return 1;
end;
$$;
-- TOC entry 49324
--
CREATE TABLE winners (
    date_creation timestamp without time zone,
    game_name text,
    id character integer NOT NULL,
);
-- END Batch

For me, the point was that I looked for begin followed by a space, and I forgot the line could end right after the keyword. Also, I looked for a closing parenthesis to detect when a multi-line insert would end, but it was the semicolon.

Many thanks to 5DN1L who shared this example

1 Like