exebook exebook - 26 days ago 6
SQL Question

Pass column name to a function

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

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;