Help using template variable in a graph query

Grafana Dashboards
1

I’m not sure what I’m doing wrong with using my template variable in a query. I’m using a postgres database with a table called “Weather” that contains some weather data that I pulled from OpenWeather. I would like to use a template variable to select the town that I want to display weather data for on a dropdown.

The error I get is; db query error:

pq: syntax error at or near “TownName1”

The variable query which I called “selector” is:

SELECT DISTINCT town FROM “Weather”

My dash query is:

WITH pressure_stats AS (
  SELECT
    MIN(CAST(press AS DECIMAL)) AS min_press,
    MAX(CAST(press AS DECIMAL)) AS max_press
  FROM
    "Weather"
  WHERE
    town = '${selector}' 
)
SELECT
  town,
  (CAST(tempf AS DECIMAL) - ((100 - CAST(hum AS DECIMAL)) / 5)) AS "Dew Point",
  CAST(tempf AS DECIMAL) AS tempf,
  CAST(hum AS DECIMAL) AS hum,
  (((CAST(press AS DECIMAL) - min_press) / (max_press - min_press)) * 50) + 50 AS press_normalized,
  recdate,
FROM
  "Weather"
CROSS JOIN
  pressure_stats
WHERE
  town = '${selector}';

  • What Grafana version and what operating system are you using?
    10.0.2

  • What are you trying to achieve?
    use template variables in a graph query

  • How are you trying to achieve it?
    Code is above, I’m trying to reference a template variable from a variable query

  • What happened?

pq: syntax error at or near “TownName1”

  • What did you expect to happen?
    Dropdown with town name to appear on the graph

  • Can you copy/paste the configuration(s) that you are having problems with?
    above

  • Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.

  • Did you follow any online instructions? If so, what is the URL?

2

‘${selector}’

Change that to

‘${selector:singlequote}’

3

Thanks! That didn’t change anything unfortunately. Here is the complete error when I click the red exclamation point:

Status: 500. Message: db query error: pq: syntax error at or near “Loveland”

Here is the query with the template variable parsed out from the query inspector. This is obviously parsed out automatically from the template variable in the previous post. I added the ‘${selector:singlequote}’ clause, and this was the result.

WITH pressure_stats AS (
SELECT
MIN(CAST(press AS DECIMAL)) AS min_press,
MAX(CAST(press AS DECIMAL)) AS max_press
FROM
“Weather”
WHERE
town = ‘‘Loveland’,‘Flagstaff’,‘CrystalFalls’,‘Naples’,‘Libertyville’,‘Victoria’,‘NewYork’’
)
SELECT
town,
(CAST(tempf AS DECIMAL) - ((100 - CAST(hum AS DECIMAL)) / 5)) AS “Dew Point”,
CAST(tempf AS DECIMAL) AS tempf,
CAST(hum AS DECIMAL) AS hum,
(((CAST(press AS DECIMAL) - min_press) / (max_press - min_press)) * 50) + 50 AS press_normalized,
recdate,
FROM
“Weather”
CROSS JOIN
pressure_stats
WHERE
town = ‘‘Loveland’,‘Flagstaff’,‘CrystalFalls’,‘Naples’,‘Libertyville’,‘Victoria’,‘NewYork’’ ;

I changed it to ‘${selector:doublequote}’ which changed the Where clause to:

WHERE
town = ‘“Loveland”,“Flagstaff”,“CrystalFalls”,“Naples”,“Libertyville”,“Victoria”,“NewYork”’;

and the error is now:

Status: 500. Message: db query error: pq: syntax error at or near “FROM”

4

Got it! My where clauses should have been as follows:

WHERE
town IN ($selector)

Its working fine now, thanks for helping!

1 Like
5

Second problem: Not sure if this deserves a new thread. The template variable dropdown is presently only visible on panel viewer and panel editor. I’d like to be able to use it on the dashboard, and more importantly in an embedded iframe.

How can i enable the dropdown in iframe and dashboard?

6

Yeah I would do another thread to benefit others looking specifically for that approach