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]

Thanks for the suggestion! I already put a pr up here #366 that will be merge soon.

The approach is quite similar. We replace the variables with their default value or null, whatever is shorter in length.

[psteinroe]

and as a side note: I am currently investing my time in a new custom parser which would fix this properly.

[reteps]

Thanks for the update! I am extremely excited that you are considering this 🙏