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

Question

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?

William Prigol Lopes · Accepted Answer · 2020-05-28T13:42:23.237

0

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.

edited May 28 '20 at 13:42

answered May 22 '20 at 12:31

William Prigol Lopes

1,803
14
31

I tried this, but got the following error: ERROR: a column definition list is required for functions returning "record" LINE 1: SELECT * FROM get_gas('temperature') – Jochem May 22 '20 at 12:38
I solved it by changing the return to table: CREATE OR REPLACE FUNCTION get_gas(gas text) RETURNS TABLE (f1 INTEGER, f2 INTEGER, f3 DOUBLE PRECISION, f4 timestamp without time zone ) AS $$ ... – Jochem May 22 '20 at 12:41
@WilliamPrigolLopes - use `SELECT INTO` only when you read from a table. When it is not necessary, then use classic assignment statement `:=`. It is faster and more readable. – Pavel Stehule May 22 '20 at 14:05
*Never* concatenate user input into SQL statements without defending against SQL injection. This is how you nuke a database. – Erwin Brandstetter May 28 '20 at 13:22
I believe that @ErwinBrandstetter is a better (safer) approach. – William Prigol Lopes May 28 '20 at 13:37

score 0 · Answer 2 · answered May 28 '20 at 00:06

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:

Look at this detail, to avoid SQL injection if your variable, in some way, is manipulated by a form. — William Prigol Lopes, May 28 '20 at 13:35

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

2 Answers2