What are the available variables for postgresql

Grafana PostgreSQL
1
  • What Grafana version and what operating system are you using v10.3.1 (00a22ff8b2)

I have a mixed data soure for my panel which has multiple postgresql datasources. I want to use the name of the data source as a field, is there a way I can do this?
In addition how can I find a list of all builtin variables that are available, I’ve scoured the internet but I can only find specific variables in posts where people have asked for help. E.g. “use ${__field.name}”. How can I find out what’s available to me?

2
3

This only gives functions to do with the time, not variables such as datasource name. Maybe these don’t exist?

4
1 Like
5
6

There is a variable-type called “Data source”.
Give it a name and a label (as an example “DataSource”) and select the PostgreSQL-Type.

Now you find a Data source called “${DataSource}”.

1 Like
7

It almost worked, I was excited for a moment. I created a datasource variable that allows multiple values including all but that is for the whole panel rather than the individual query. So for example if I have

variable datasource: All

datasource "source 1": select date_trunc('month', approved_at) AS month_start, count(id) as "${datasource:text}" from mytable, group by month_start

datasource source 2: select date_trunc('month', approved_at) AS month_start, count(id) as "${datasource:text}" from mytable, group by month_start

In this case count is displayed on the legend as “source 1 + source 2”, “source 1 + source 2”
This means the variable is for the whole panel, not the datasource for the individual query :frowning:

However I realised I can achieve what I want in a more manual and annoying way in this instance because postgresql lets you name fields with spaces if you put it in double quotes, so I can write

datasource A: select date_trunc('month', approved_at) AS month_start, count(id) as "source 1" from mytable, group by month_start

datasource B: select date_trunc('month', approved_at) AS month_start, count(id) as "source 2" from mytable, group by month_start

A bit annoying but at least it works for my use case.

So the question remains in general, is it possible to get source 1 and source 2 as a field in a mixed datasource scenario, where the variable is set to the datasource of that query. I.e. in query 1, from source 1, ${datasource:text} == “source 1” and in query 2 from source 2, ${datasource:text} == “source 2”
?

8

sorry i dont understand, can you maybe explain what you are trying to achieve with some basic examples?

there are global variables, there are custom variables, query variables, datasource variables etc.

you can specify these variables in your queries, you can build queries using datasource variables, there are so many permutations, that it is difficult to explain them all and their use cases…

9

Yeah I think the biggest issue is that I cannot find these variables in any documentation. In my particular case (I have similar cases) I have a panel with a Mixed data source which is made up of many postgresql data sources. Within the query of each of these postgresql data sources I would like to get the name of it’s data source. So for example lets say I have created 2 postgresql datasources, A and B, then I create a panel where I want to combine those data sources. Each data source will produce a bar on a bar chart. I want each bar to be labelled with the name of that data source. So in this case the legend would show 2 values, “A” and “B”. This means I need the queries to include count(*) as "A" for data source A and count(*) as "B" for data source B. What I would like to have instead is identical queries by using variables, i.e. both queries would include count(*) as "${datasource.name}" or similar.

10

are these datasources from distinctly different postgresql servers or same postgresql server but different databases, or same database different tables?

for the second requirement, why not just use static values?

for A

select count(1) as 'A'
11

One server, different databases, so without installing postgres extensions I don’t think we can have just 1 data source :frowning:

I can use static values however I was hoping to avoid that so I can have the same query for all sources.

Really I posted this question because I was hoping there was some documentation somewhere that I hadn’t found that listed all the builtin variables that I could use. For example in one panel I think I have the label {__field.name} but I always wanted to know what else is available other than field.

1 Like