editor needs lsp features

[DanielFGray]

• completions
  • context-sensitive completions
  • completion for identifiers in introspection
  • completion for identifiers in current document (not in introspection)
• hovers
  • table/column info
  • signatures for user-defined functions
  • signatures for postgres built-in functions
• diagnostics
  • detect trailing/missing comma
  • error when referencing undefined
    • schemas
    • tables
    • columns
    • types
    • functions
  • type mismatches
    • for operators [cannot compare uuid to int]
    • for functions [function expected uuid, received ..]
• go to definition

docs:

• LSP spec
• lsp-web-extension-sample

prior art:

• https://github.com/microsoft/vscode-postgresql/
• https://github.com/UniqueVision/plpgsql-lsp/

alternatives:

• https://github.com/supabase-community/postgres_lsp
  since it's written in rust, it will need some work (wasm compiling?) to be able to run in a browser and interact directly with pglite

[DanielFGray]

a lot of things are currently blocked by needing a SQL parser

some options [roughly in order of decreasing preference]:

• parse using PEG
  • see Runtime-Extensible SQL Parsers Using PEG has examples/ideas
• use postgres' own query parser libpg-query
  • pganalyze/pg-query-emscripten doesnt seem to provide types
• parse with JS
  • sql-parser-cst is currently missing full Postgres Support nene/sql-parser-cst#40
• parse using wasm build of treesitter
  • see Parsing pgSQL with tree-sitter in WASM
• parse using textmate grammar included with vscode/monace
  • [seems possible not but no idea if the hooks exist to be able to grab identifiers and such from it]

[DanielFGray]

some features may also be blocked by microsoft/language-server-protocol#1264

[DanielFGray]

postgres_lsp plans to provide a wasm build (supabase-community/postgres-language-server#174) that should drop-in for everything needed here