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?

1) You will have to do this in a function, plain SQL does not support `if/else` 2) `DO` anonymous functions can't return anything so they will not be of much use. 3) Do as the `Hint` suggests and use `PERFORM` instead of `SELECT` per [plpgsql statements](https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-GENERAL-SQL). 4) Spend some time reading through [plpgsql](https://www.postgresql.org/docs/current/plpgsql.html) — Adrian Klaver, May 17 '22 at 00:10
@BeRT2me no, the user selects a value for this variable and the query changes accordingly, but the table doesn't contain this value — Guillermo Moreno Castaño, May 17 '22 at 00:43

Erwin Brandstetter · Answer 1 · 2022-05-17T01:24:21.537

0

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

edited May 17 '22 at 01:24

answered May 17 '22 at 00:46

Erwin Brandstetter

605,456
145
1,078
1,228

Thank you Erwin, I edited the question to provide more information on the scope – Guillermo Moreno Castaño May 17 '22 at 01:00
I think I need to dig deeper into `json_build_object` because I have no idea how it works... Probably a noob question, but why does have to be the _same select_ and not 3 different ones? If only I could choose what select to perform depending on the sensor's choice... – Guillermo Moreno Castaño May 17 '22 at 01:32
You can choose your `SELECT` *in client code*. Or you can write a server-side function, but a function has a single, immutable return type ... – Erwin Brandstetter May 17 '22 at 01:37

Conditional SELECT clause

1 Answers1