PL/pgSQL Language Server

Features

complete definitions.
go to definition.
go to document/workspace symbols.
hover the definition.
check syntax errors.
check static analysis errors (when plpgsql_check use) .
execute the current file query by CodeLens/Command.
support Multi-root Workspace.

	Completion	Go to definition	Hover
DOMAIN	✔️	✔️	✔️
FUNCTION	✔️	✔️	✔️
INDEX	-	✔️	✔️
MATERIALIZED VIEW	✔️	✔️	✔️
PROCEDURE	✔️	✔️	✔️
TABLE	✔️	✔️	✔️
TRIGGER	-	✔️	✔️
TYPE	✔️	✔️	✔️
VIEW	✔️	✔️	✔️

Usage

Set your database connection to VSCode settings.
Open .pgsql file and edit your code!

VSCode Settings Sample

{
  "plpgsqlLanguageServer.database": "your_database_name",
  "plpgsqlLanguageServer.user": "your_database_user",
  "plpgsqlLanguageServer.password": "your_database_password",
  "plpgsqlLanguageServer.definitionFiles": [
    // Support glob.
    "**/*.sql",
    "**/*.psql",
    "**/*.pgsql"
  ],
  // The supported extention types are ['*.pgsql', '*.psql'].
  // If you want to use this extension in '*.sql', add the following settings.
  "files.associations": {
    "*.sql": "postgres"
  }
}

Disable Specific File

If you want to disable the extension for a specific file, just add this comment your file top.

/* plpgsql-language-server:disable */

Or, if you want to disable only the validation feature, try this

/* plpgsql-language-server:disable validation */

Execute the Current File Query

You can execute the current file query from VSCode Command.

Also, you can use it by CodeLens when your file is valid.

If you want to disable this feature, try this

{
  "plpgsqlLanguageServer.enableExecuteFileQueryCommand": false
}

Shortcuts are not supported by default, but can be configured by adding the following setting to "keybindings.json".

[
  {
    "key": "f5",
    "command": "plpgsql-lsp.executeFileQuery",
    "when": "editorLangId == 'postgres'"
  }
]

[Experimental Feature] Query Parameters

In the case of a file that defines a query with parameters, You can validate it by adding the query parameter comment at the file top.

/* plpgsql-language-server:use-query-parameter */

SELECT
  id,
  name
FROM
  users
WHERE
  id = $1 AND name = ANY($2);

If you want to use keyword query parameter, need to set the parameter pattern in the settings file.

{
  "plpgsqlLanguageServer.queryParameterPattern": ":[A-Za-z_][A-Za-z0-9_]*"
}

/* plpgsql-language-server:use-query-parameter */

SELECT
  id,
  name
FROM
  users
WHERE
  id = :id AND name = ANY(:names);

Positional Query Parameters

To more explicitly indicate that it is a positional parameter, the following comment can be given.

/* plpgsql-language-server:use-positional-query-parameter */

SELECT
  id,
  name
FROM
  users
WHERE
  id = $1 AND name = ANY($2);

This tool counts the number of parameters with a simple regular expression, so complex queries file require the number to be specified explicitly in the comment.

/* plpgsql-language-server:use-positional-query-parameter number=2 */

SELECT
  id,
  name,
  'This text contains "$3" :('
FROM
  users
WHERE
  id = $1 AND name = ANY($2);

Keyword Query Parameters

Support query with keyword parameters, same as positional parameters.

However, the format of the keyword parameter varies from library to library, so must be indicated in the settings file.

{
  "plpgsqlLanguageServer.keywordQueryParameterPattern": "@{keyword}"
}

You have finished setting, you can validate it like this.

-- plpgsql-language-server:use-keyword-query-parameter

SELECT
  id,
  name
FROM
  users
WHERE
  id = @id AND name = ANY(@names);

The complex queries file require the keywords to be specified explicitly in the comment.

-- plpgsql-language-server:use-keyword-query-parameter keywords=[id, names]

SELECT
  id,
  name,
  'This text contains "@tags" :('
FROM
  users
WHERE
  id = @id AND name = ANY(@names);

dsernst/plpgsql-lsp