Sql function: False error flag - Invalid statement: unterminated dollar-quoted string at or near "$$

[Donnerstagnacht]

Bug report

• [x] I confirm this is a bug with Supabase, not with my own application.
• [x] I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

If I start to edit functions, frequently but not always, the following error message appears even if the code is correct:

Invalid statement: unterminated dollar-quoted string at or near "$$

To Reproduce

I was not able to reproduce it. It happens frequently, but I could not find a pattern yet.

Expected behavior

$$ quotes are correct code.

Screenshots

If applicable, add screenshots to help explain your problem.

System information

• OS: windows 11
• editor: vs-code
• pls: 0.5.0

Additional context

Probably related to https://github.com/supabase-community/postgres-language-server/issues/327 ? An other idea of mine is that it might be connected to plpgsql functions.

It appears in a file which I use to test different generated types of the supabase type generator. Probably this could help as context or test cases.

-- Drop all functions
DROP FUNCTION if EXISTS return_row_type (int);

DROP FUNCTION if EXISTS return_setof_rows (int);

DROP FUNCTION if EXISTS return_setof_rows_sql (int);

DROP FUNCTION if EXISTS return_in_out (int);

DROP FUNCTION if EXISTS return_out (int);

DROP FUNCTION if EXISTS return_composite (int);

DROP FUNCTION if EXISTS return_composite_sql (int);

DROP FUNCTION if EXISTS return_setof_composite (int);

DROP FUNCTION if EXISTS return_setof_composite_sql (int);

DROP FUNCTION if EXISTS return_composite_with_row_constructor (int);

DROP FUNCTION if EXISTS return_table (int);

DROP FUNCTION if EXISTS return_table_sql (int);

DROP FUNCTION if EXISTS return_out_ddl_table_ref (int);

DROP FUNCTION if EXISTS return_table_ddl_table_ref (int);

DROP FUNCTION if EXISTS return_dynamic_record (int);


DROP TYPE if EXISTS group_composite;


DROP TABLE IF EXISTS public.groups;

CREATE TABLE groups (id int, name text);

CREATE TYPE group_composite AS (id int, name text);

INSERT INTO
    groups (id, name)
VALUES
    (1, 'Group 1');

CREATE FUNCTION return_row_type (group_id int) returns groups -- bedeutet: Rückgabe eines Rowtypes der Tabelle 'groups'
language plpgsql AS $$
DECLARE
    group_record groups%ROWTYPE;
BEGIN
    SELECT *
    INTO group_record
    FROM groups
    WHERE id = group_id;

    RETURN group_record;
END;
$$;

CREATE FUNCTION return_setof_rows (group_id int) returns setof groups -- bedeutet: Rückgabe mehrerer rows vom Typ 'groups'
language plpgsql AS $$
BEGIN
    RETURN QUERY
    SELECT *
    FROM groups
    WHERE id = group_id;
END;
$$;

CREATE FUNCTION return_setof_rows_sql (group_id int) returns setof groups language sql AS $$
    SELECT *
    FROM groups
    WHERE id = group_id;
$$;

CREATE FUNCTION return_in_out (IN group_id int, OUT id int, OUT name text) language plpgsql AS $$
BEGIN
    SELECT g.id, g.name
    INTO id, name
    FROM groups g
    WHERE g.id = group_id;
END;
$$;

CREATE FUNCTION return_out (group_id int, OUT id int, OUT name text) language plpgsql AS $$
BEGIN
    SELECT g.id, g.name
    INTO id, name
    FROM groups g
    WHERE g.id = group_id;
END;
$$;

CREATE FUNCTION return_composite (group_id int) returns group_composite language plpgsql AS $$
DECLARE
    result group_composite;
BEGIN
    SELECT g.id, g.name
    INTO result.id, result.name
    FROM groups g
    WHERE g.id = group_id;
    RETURN result;
END;
$$;

CREATE FUNCTION return_composite_sql (group_id int) returns group_composite language sql AS $$
    SELECT g.id, g.name
    FROM groups g
    WHERE g.id = group_id;
$$;

CREATE FUNCTION return_setof_composite (group_id int) returns setof group_composite language plpgsql AS $$
BEGIN
    RETURN QUERY
    SELECT g.id, g.name
    FROM groups g
    WHERE g.id = group_id;
END;
$$;

CREATE FUNCTION return_setof_composite_sql (group_id int) returns setof group_composite language sql AS $$
    SELECT g.id, g.name
    FROM groups g
    WHERE g.id = group_id;

$$;

CREATE FUNCTION return_composite_with_row_constructor (group_id int) returns group_composite language plpgsql AS $$
DECLARE
    found_id int;
    found_name text;
BEGIN
    SELECT g.id, g.name
    INTO found_id, found_name
    FROM groups g
    WHERE g.id = group_id;
    
    RETURN ROW(found_id, found_name)::group_composite;
END;
$$;

CREATE FUNCTION return_table (group_id int) returns TABLE (id int, name text) language plpgsql AS $$
BEGIN
    RETURN QUERY
    SELECT g.id, g.name
    FROM groups g
    WHERE g.id = group_id;
END;
$$;

CREATE FUNCTION return_table_sql (group_id int) returns TABLE (id int, name text) language sql AS $$
    SELECT g.id, g.name
    FROM groups g
    WHERE g.id = group_id;
$$;

CREATE FUNCTION return_out_ddl_table_ref (
    group_id int,
    OUT id groups.id % type,
    OUT name groups.name % type
) language plpgsql AS $$
BEGIN
    SELECT g.id, g.name
    INTO id, name
    FROM groups g
    WHERE g.id = group_id;
END;
$$;

CREATE FUNCTION return_table_ddl_table_ref (group_id int) returns TABLE (id groups.id % type, name groups.name % type) language plpgsql AS $$
BEGIN
    RETURN QUERY
    SELECT g.id, g.name
    FROM groups g
    WHERE g.id = group_id;
END;
$$;

CREATE FUNCTION return_dynamic_record (group_id int) returns record language plpgsql AS $$
DECLARE
    result RECORD;
BEGIN
    SELECT g.id, g.name
    INTO result
    FROM groups g
    WHERE g.id = group_id;
    RETURN result;
END;
$$;

[psteinroe]

Would it be possible to get a log file from opening the file to to the error happening?

[Donnerstagnacht]

Like this? The editor actions seem to create no additional logs. Do I have to activate something?

2025-04-22 13:57:45.716 [info] 





2025-04-22 13:57:45.716 [info] PostgresTools extension 1.1.6 activated
2025-04-22 13:57:45.716 [info] Starting with config…
	config={"enabled":true,"allowDownloadPrereleases":false,"configFile":"","bin":null}
2025-04-22 13:57:45.716 [info] User-facing commands registered
2025-04-22 13:57:45.716 [info] User did not specify path to config file. Using default.
2025-04-22 13:57:45.716 [info] Found config file.
	path="c:\\polity\\postgrestools.jsonc"
2025-04-22 13:57:45.716 [info] Binary not found with strategy
	strategy="VSCode Settings Strategy"
2025-04-22 13:57:45.716 [info] Searching for node_modules package
	postgrestoolsPackageNameJson="@postgrestools/postgrestools/package.json"
2025-04-22 13:57:45.716 [info] Copying binary to temp location
	currentLocation="c:\\polity\\node_modules\\@postgrestools\\cli-x86_64-windows-msvc\\postgrestools.exe"
2025-04-22 13:57:45.716 [info] Server Options: 
	serverOptions={"command":"c:\\Users\\tobia\\AppData\\Roaming\\Code\\User\\globalStorage\\supabase.postgrestools\\tmp-bin\\postgrestools-0.5.0.exe","transport":0,"options":{"cwd":"c:\\polity"},"args":["lsp-proxy","--config-path=c:\\polity\\postgrestools.jsonc"]}
2025-04-22 13:57:45.881 [info] Created a global LSP session
2025-04-22 13:57:45.882 [info] PostgresTools extension started
2025-04-22 13:57:45.882 [info] Started listening for configuration changes
2025-04-22 13:57:45.882 [info] Started listening for active text editor changes

LSP (/): 2025-04-22 13:57:45.929 [info] [Info - 1:57:45 PM] Server initialized with PID: 25820 I clicked in the file and pressed enter: It sees to only appears if I add a character (letter, enter) to a sql function body.

postgrestools.jsonc:

{
  "$schema": "https://pgtools.dev/schemas/0.0.0/schema.json",
  "vcs": {
    "enabled": false,
    "clientKind": "git",
    "useIgnoreFile": false
  },
  "files": {
    "ignore": []
  },
  "linter": {
    "enabled": true,
    "rules": {
      "recommended": true
    }
  },
  "db": {
    "host": "127.0.0.1",
    "port": 54322,
    "username": "postgres",
    "password": "postgres",
    "database": "postgres",
    "connTimeoutSecs": 10,
    "allowStatementExecutionsAgainst": ["127.0.0.1/*", "localhost/*"]
  }
}

Another example:

1. Error exists and is detected correctly.

2. I position my cursor (no change)

3. I remove or add a character and the mentioned error appears and is positioned at the create keyword.

[Donnerstagnacht]

I think you meant the local log file, right? Probably we could add a link to the log docs or the file paths to the bug template. server-error-log.txt

[psteinroe]

Thanks for sharing!! That helps a lot. And good point with the docs link. Will add it. 🫶🏼