PostgreSQL Datasource - Unwanted Single Quote is added when Variable is used in Panel Query

Grafana Configuration
1

I have PostgreSQL Datasource. I have a database function which returns a dynamically generated string. I need to use this dynamically generated string in select clause of my dashboard panel query.

So, I defined a variable named TestCaseVar. I get the correct string in the preview values

Preview of values
(case when tt.name = ‘Test Name1’ then tt.col1 end) as "Test Name1"

Until this point things looks good.

Now, I added a panel in the dashboard and tried to use TestCaseVar in the query as below,

SELECT
$__time(tt.“timestamp”),
[[TestCaseVar]]
FROM
testtable tt

This shows me error. When I see the generated SQL I see,

SELECT
tt.“timestamp” AS “time”,
(case when tt.name = ’ ‘Test Name1’ ’ then tt.col1 end) as “Test Name1”
FROM
testtable tt

The Issue is it adds additional single quote (’) in the generated query.

How Can I get rid-off this additional single quote from the query?

Any quick help / suggestions would be highly appreciated.

2

Is TestCaseVar calling a database function? Is it a single or multi-value variable?

3

Yes TestCaseVar is calling a database functions which receives expected result as shown in the preview of values.

4

Anyone any comments? Is there any suggestion or workaround to get rid-off this issue?

5

I can’t recreate this. Can you show your variable settings? Any other details that might help to recreate it?

I didn’t create a function but did a select string instead:

select '(case when tt.name = ‘Test Name1’ then tt.col1 end) as "Test Name1"')

image
image1650×1320 133 KB

When I test it in a query, it looks right to me:

image
image2666×1152 182 KB

Have you tried ${TestCaseVar:raw} instead of [[TestCaseVar]]?

6

Yes. TestCaseVar is calling a database functions which receives expected result as shown in the preview of values but even if a simple constant variable is created and assigned the text value as returned by DB function the behavior is same.

7

I am not seeing the same thing, I tested with “custom” variable first and it worked for me. Can you provide more details:

  • Grafana version
  • Postgres version
  • Screenshot of your variable settings
8

Thanks @daniellee for your quick response !

I used ${TestCaseVar:raw} syntax as well as [[TestCaseVar:raw]]. They both seems to work.

Earlier before creating this post, I had tried to use [[TestCaseVar:raw]] and noticed that GenerateSQL option wasn’t viewable. So, I got bit confused and assumed that there is something wrong so that the GenerateSQL option isn’t available and I could not see the generated SQL.
To see the exact syntax of my single quoted string, I introduced small error in the query then I noticed that the GenerateSQL got visible and my query string was formed correctly.

Not sure why GenerateSQL option gets hidden when I use ${TestCaseVar:raw} or [[TestCaseVar:raw]].

2 Likes