Pass column name to a function

Question

CREATE OR REPLACE FUNCTION test123(column_name VARCHAR(40)) RETURNS RECORD AS $$
DECLARE   
    rec RECORD ;
BEGIN
    SELECT * INTO rec FROM my_table WHERE column_name=1 LIMIT 1;
    RETURN rec;
END;
$$ LANGUAGE plpgsql;

How can you pass the column name to a function and then use that name in the query?

The above code does not work:

error: operator does not exist: character varying = integer

Answer 1

When you want to use function parameters as identifiers in queries inside a function you need to dynamically EXECUTE the query:

CREATE OR REPLACE FUNCTION test123(column_name VARCHAR(40)) RETURNS RECORD AS $$
DECLARE   
    rec RECORD;
BEGIN
    EXECUTE format('SELECT * FROM my_table WHERE %I = 1 LIMIT 1', column_name)
    INTO rec;
    RETURN rec;
END;
$$ LANGUAGE plpgsql;

or much shorter:

CREATE OR REPLACE FUNCTION test123(column_name VARCHAR(40)) RETURNS RECORD AS $$
BEGIN
    RETURN QUERY EXECUTE format('SELECT * FROM my_table WHERE %I = 1 LIMIT 1', column_name);
END;
$$ LANGUAGE plpgsql;