Support for templated parameters

[reteps]

It would be excellent to have support for templated parameters:

SELECT
  *
WHERE
  aq.id = $assessment_question_id
FROM
  assessment_questions AS aq;

Where $assessment_question_id is replaced either with:

• a appropriate value for the column type
• the name of the parameter in quotes

Currently, I get an error:

Error message on `0.6.1`

yarn postgrestools check apps/prairielearn/src/models/dummy.sql
apps/prairielearn/src/models/dummy.sql:1:1 syntax ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

  ✖ Invalid statement: syntax error at or near "$"

  > 1 │ SELECT
      │ ^^^^^^
  > 2 │   *
  > 3 │ WHERE
  > 4 │   aq.id = $assessment_question_id
  > 5 │ FROM
  > 6 │   assessment_questions AS aq;
      │   ^^^^^^^^^^^^^^^^^^^^^^^^^^^
    7 │


Checked 1 file in 12ms. No fixes applied.
Found 1 error.
check ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

  ✖ Some errors were emitted while running checks

SQLFluff takes the second approach (replace the templated parameter with the name in quotes), and allows you to configure templating, as well as overriding the replacement behavior.

[sqlfluff:templater:placeholder]
param_style = dollar
assessment_question_id = 0

Default:

SELECT
  *
WHERE
  aq.id = 'assessment_question_id'
FROM
  assessment_questions AS aq;

With customization:

SELECT
  *
WHERE
  aq.id = 0
FROM
  assessment_questions AS aq;

Related:

• ts server plugin #176
• Postgres Language Server: JS + TS Template Literal Support #25
• SQLC and The "@" symbol #376

[reteps]

A simple transformation to a quoted version of the variable name allows for type diagnostics:

Perhaps an approach like:

1. transform to quoted version, and mark transformation from templated variable (to allow for parsing)
2. upon typechecking, transform into the appropriate null type to avoid any diagnostic errors, or simply ignore the diagnostics if it came from a transformed variable.

If this seems like a reasonable approach, I would be happy to try and get a PR up.

[psteinroe]

If we go for a custom parser, we will simply support the variables out of the box.

If we still need to replace, we need to derive it from the schema cache. But yes, this is going to be trickier.

[reteps]

Just so I understand, the route for a custom parser would essentially allow a preprocessor step where you can make arbitrary substitutions / changes to the file before it gets properly parsed (e.g. allowing users to define a find/replace regex as a proprocessing step)? Or would this be in replacement of libpg_query entirely?

[psteinroe]

It would replace libpg_query entirely.

I am currently contributing a bit to https://squawkhq.com because the parser there is quite advanced compared to any other attempt I have seen so far.

[reteps]

I am also aware of the sqruff parser (https://github.com/quarylabs/sqruff/blob/main/crates/lib-dialects/src/postgres.rs), but I don't know how it compares to squawk.

[reteps]

The configuration could take inspiration from https://github.com/UniqueVision/plpgsql-lsp?tab=readme-ov-file#query-parameters

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