cannot create a function via SQL command

[halmai]

Preconditions

• HeidiSQL version: Version 11.1.0.6211 (64 Bit)
• Database type and version: PostgreSQL 11.9 (Debian 11.9-0+deb10u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
• OS: win10

Describe the bug

Creating a function via a CREATE FUNCTION command doesn't work.

To Reproduce

1. Open a new Query Tab
2. Paste the below code:

CREATE FUNCTION x() RETURNS INT LANGUAGE plpgsql AS $$
BEGIN 
  RETURN 1;
END;
$$;

3. Ctrl-A (Edit -> Select All)
4. F9 (Query -> Run)

Screenshots or Crash reports

I get this error message:

ERROR:  unterminated dollar-quoted string at or near "$$
BEGIN 
  RETURN 1"
LINE 1: CREATE FUNCTION x() RETURNS INT LANGUAGE 
plpgsql AS $$
                                                            ^

Executing the same command from psql works well so the syntax is correct. Heidi also should be able to execute it, I think.

[ansgarbecker]

Semicolon is the default delimiter for queries in HeidiSQL. You have to change it to something else before running such compound statements. Use the button with red semicolon on the main toolbar to do that.

Alternatively, you can change it via code:

DELIMITER !!
-- your code
DELIMITER ;

[pcopissa]

@ansgarbecker I tried your suggested workaround and it does not work on HeidiSQL12.6.6765 (or 12.4 for that matter)

Namely:

DROP FUNCTION IF EXISTS public.aab;
DELIMITER !!
CREATE FUNCTION public.aab() RETURNS INT LANGUAGE plpgsql AS $$
BEGIN 
  RETURN 0;
END;
$$;
DELIMITER ;

It fails silently. function aab is not created. Same if I change the separator to !! with the blue semicolon (I don't have a button with a red semicolon BTW ?) I also tried this:

DROP FUNCTION IF EXISTS public.aab;
DELIMITER !!
CREATE FUNCTION public.aab() RETURNS INT LANGUAGE plpgsql AS $$
BEGIN 
  RETURN 0!!
END!!
$$!!
DELIMITER ;

In the second case I get the same error about the $$ string not being closed.

Addendum However this works (at least on 12.6): First set the separator to !! with the blue semicolon button (Don't use DELIMITER !!. If you do, no error is reported and yet the function will not be created)

DROP FUNCTION IF EXISTS public.aab;
CREATE FUNCTION public.aab() RETURNS INT LANGUAGE plpgsql AS $$
BEGIN 
  RETURN 0;
END;
$$ ; -- Note the space between $$ and ; 

In this case the function is created as expected. psql however does not need the space to work. Another thing that works is to move the LANGUAGE clause at the end (which has the side effect of introducing a space after $$). Thus:

DROP FUNCTION IF EXISTS public.aab;
CREATE FUNCTION public.aab() RETURNS INT AS $$
BEGIN 
  RETURN 0;
END;
$$ LANGUAGE plpgsql;