PostgreSQL Language Server

This tool is a Language Server to support development with PostgreSQL and PL/pgSQL. This is accomplished by a combination of querying the database and parsing SQL.

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'"
  }
]

Workspace validation

If you want to validate the workspace files in the background, please set the settings like this,

{
  "plpgsqlLanguageServer.workspaceValidationTargetFiles": [
    "definitions/**/*.pgsql",
    // "setup/**/*.pgsql"
  ]
}

Validation is executed under the following conditions.

Start Language Server.
"Execute the Current File Query" command was successfull.
Execute "Validate the Workspace Files" command.

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);

Multiple Statements

If you want to validate multiple statements individually, you can do so by giving the following settings.

{
  "plpgsqlLanguageServer.keywordQueryParameterPattern": [
    "@{keyword}",
    "sqlc\\.arg\\s*\\('{keyword}'\\)",
    "sqlc\\.narg\\s*\\('{keyword}'\\)",
  ],
  "plpgsqlLanguageServer.statements.separatorPattern": "-- name:[\\s]+.*",
}

This setting allows the following files to be handled.

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

-- name: ListUser :many
SELECT
  id,
  name
FROM
  users
WHERE
  id = sqlc.arg('id');

-- name: ListUsers :many
SELECT
  id,
  name
FROM
  users
WHERE
  name = ANY(@names);

-- name: DoNotValidate :many
-- plpgsql-language-server:disable
SELECT * FROM inexistent_table; -- no error raised for this statement

UniqueVision/plpgsql-lsp