How to execute a query with a column name passed as parameter to a plpgsql function?

user_12190115 0
Viewed 365

I have a table with multiple columns in PostgreSQL. I try to make a function returning a table with a few default columns and a variable column. The column name should be passed as function parameter. Example: 

SELECT * FROM get_gas('temperature');

This is my code right now:

CREATE OR REPLACE FUNCTION get_gas(gas text) 
RETURNS TABLE (id INTEGER, node_id INTEGER, 
                  gas_name DOUBLE PRECISION, 
                  measurement_timestamp timestamp without time zone )
AS 
$$
BEGIN
  SELECT measurements_lora.id, measurements_lora.node_id, gas, measurements_lora.measurement_timestamp
  AS  measure
  FROM public.measurements_lora;
END
$$ LANGUAGE plpgsql;

When passing, for example, 'temperature' as column name (gas), I want to get a table with these columns from the function call.

id - node_id - temperature - measurement_timestamp

How would I achieve this?

plpgsql parameter-passing postgresql
2 Answers

You can use EXECUTE statement.

CREATE OR REPLACE FUNCTION get_gas(gas text) RETURNS TABLE (f1 INTEGER, f2 INTEGER, f3 DOUBLE PRECISION, f4 timestamp without time zone ) AS
    $$
        DECLARE
           sql_to_execute TEXT;
        BEGIN
            SELECT 'SELECT measurements_lora.id, 
                           measurements_lora.node_id, '
                           || gas ||',
                           measurements_lora.measurement_timestamp AS  measure
                      FROM public.measurements_lora ' 
              INTO sql_to_execute;
            RETURN QUERY EXECUTE sql_to_execute;
        END
    $$ LANGUAGE plpgsql;

This will create a variable sql_to_execute with your field and. QUERY EXECUTE will execute your interpreted query.

EDIT 1: Look at the another answer the concernings about security issues.

user_121216270

If you really need dynamic SQL in a PL/pgSQL function (which you don't), be sure to defend against SQL injection! Like:

CREATE OR REPLACE FUNCTION get_gas(gas text)
  RETURNS TABLE (id integer
               , node_id integer 
               , gas_name double precision 
               , measurement_timestamp timestamp)
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY EXECUTE format(
   'SELECT m.id, m.node_id, m.%I, m.measurement_timestamp
    FROM   public.measurements_lora m'
  , gas
   );
END
$func$;

The format specifier %I in format() double-quotes identifiers where needed,

See:

user_9398600
Related