Intelligence for sql function bodies

[Donnerstagnacht]

Feature request

Is your feature request related to a problem? Please describe.

Using the vs-code extension, type checking returns different results for sql function bodies.

It recognizes a spelling error in the plain select statement as expected but does not find the error in the sql statement which is wrapped in the function body (blue line is from a grammar check).

Describe the solution you'd like

Statement checks should return the same results as outside of a function body.

Describe alternatives you've considered

none.

Additional context

What is the context of this issue? #178
Should the type checking for sql function bodies already work?

Versions

0.3.1.
0.4.0

Log output

2025-04-15 16:54:34.985 [info] PostgresTools extension started
2025-04-15 16:55:14.630 [info] Downloaded PostgresTools 0.4.0 to c:\Users\tobia\AppData\Roaming\Code\User\globalStorage\supabase.postgrestools\global-bin\postgrestools.exe
2025-04-15 16:56:05.498 [info] User did not specify path to config file. Using default.
2025-04-15 16:56:05.501 [info] Found config file.
	path="c:\\polity\\postgrestools.jsonc"
2025-04-15 16:56:05.501 [info] Binary not found with strategy
	strategy="VSCode Settings Strategy"
2025-04-15 16:56:05.501 [info] Searching for node_modules package
	postgrestoolsPackageNameJson="@postgrestools/postgrestools/package.json"
2025-04-15 16:56:05.532 [info] Copying binary to temp location
	currentLocation="c:\\polity\\node_modules\\@postgrestools\\cli-x86_64-windows-msvc\\postgrestools.exe"
2025-04-15 16:56:05.549 [info] Server Options: 
	serverOptions={"command":"c:\\Users\\tobia\\AppData\\Roaming\\Code\\User\\globalStorage\\supabase.postgrestools\\tmp-bin\\postgrestools-0.4.0.exe","transport":0,"options":{"cwd":"c:\\polity"},"args":["lsp-proxy","--config-path=c:\\polity\\postgrestools.jsonc"]}
2025-04-15 16:56:05.643 [info] Created a global LSP session
2025-04-15 16:56:05.643 [info] PostgresTools extension restarted
2025-04-15 16:56:10.114 [error] PostgresTools language server closed
2025-04-15 16:56:38.340 [info] PostgresTools extension started
2025-04-15 16:57:48.362 [info] PostgresTools extension started
2025-04-15 16:58:25.463 [info] PostgresTools extension stopped

folder structure

root
|--> node_modules
        |-->cli-x86_64-windows-msvc
        |--> postgrestools
                   | --> bin
                   | --> package.json
|--> supabase
|--> package-lock.json
|--> package.json
|--> postgrestools.jsonc

C:\Users\tobia\AppData\Roaming\Code\User\globalStorage\supabase.postgrestools
|--> tmp-bin 
         |--> postgrestools.exe
|--> global-bin 
         |--> postgrestools-0.4.0.exe

package.json:

{
  "devDependencies": {
    "@postgrestools/postgrestools": "^0.4.0",
     ....
}

postgrestool.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/*"
        ]
    }
}

[psteinroe]

hey @Donnerstagnacht, I moved the issue to the main repo since this is not related to the extension.

can you check what version you are using?

[Donnerstagnacht]

@psteinroe ,

Originally, I experienced it on 0.3.1. But now I updated to 0.4.0 and still have the same issue that the function body type checks return different results (e.g. it seems to not check the function body).

I also provided the logs and the folder structures above. Could you have a look at it? I had some issue moving to 0.4.0. Is the function body check working for you?

[psteinroe]

thanks for reporting! I was rewriting a lot of internals in the same PR and focussed so much on it that I forgot to even add a single test for the SQL function body extraction. 😢 This will be fixed in the next release.