Conditional SELECT clause

Question

I'm building a dashboard and I need to return certain columns from a table depending on a sensor's choice. The user selects a sensor and the database returns some columns depending on which sensor the user chose.

The approach I had in mind was something simple like this:

if ${sensor} = '1' then
    select time, temperature from tbl;
elsif ${sensor} = '2' then
    select time, humidity from tbl;
else
    --return nothing
end if;

My table is made up of a timestamp column and several other float columns, each of them containing the value of a physical variable at that certain time (temperature, pressure, humidity, height and weight). So if the user selects for example Sensor 1, a thermometer, my query should return the time and temperature columns. Some of the sensors could also return more than one physical variable, so I would need to return a table with three columns, time, height and weight.

I'm using Grafana and I'm able to read the chosen variable without any problem, but I am unable to build a table with the columns of my choice depending on this value.

I thought about writing a function that returned a table, but this is way too far from my SQL knowledge and I was pretty sure this could be done with plain SQL.

Any ideas?

Answer 1

The same SELECT cannot return a different number of columns. You can issue a different SELECT from your client program. Else, you need to aggregate multiple columns somehow: a list of values, an array, or a document type like JSON - using json_build_object() for the purpose:

SELECT time
     , CASE ${sensor}
       WHEN 1 THEN json_build_object('temperature', temperature)
       WHEN 2 THEN json_build_object('humidity', humidity)
       WHEN 3 THEN json_build_object('height', height, 'weight', weight)  -- two columns!
       END AS variable_column
FROM   tbl
WHERE  ${sensor} IN (1, 2, 3);  -- return nothing for other numbers

The second part of your question is an amalgam of independent issues:

• You cannot execute SELECT in a PL/pgSQL code block without an assignment target.
• You cannot return anything from a DO statement. You need a FUNCTION (or, to a limited extent, a PROCEDURE) for that.

See:

• SELECT raises exception in PL/pgSQL function