postgresql - Using parameter as column name in Postgres function

Question

I have a Postgres table bearing the following form

CREATE TABLE "public"."days" 
(
 "id" integer NOT NULL,
 "day" character varying(9) NOT NULL,
 "visits" bigint[] NOT NULL,
 "passes" bigint[] NOT NULL
);

I would like to write a function that allows me to return the visits or the passees column as its result for a specified id. My first attempt goes as follows

CREATE OR REPLACE FUNCTION day_entries(INT,TEXT) RETURNS BIGINT[] LANGUAGE sql AS
'SELECT $2 FROM days WHERE id = $1;'

which fails with an error along the lines of

return type mismatch in function declared to return bigint[] DETAIL: Actual return type is text.

If I put in visits in place of the $2 things work just as expected. It would make little sense to define several functions to match different columns from the days table. Is there a way to pass the actual column name as a parameter while still keeping Postgres happy?

Answer 1

You can't use parameters as identifiers (=column name), you need dynamic SQL for that. And that requires PL/pgSQL:

CREATE OR REPLACE FUNCTION day_entries(p_id int, p_column text) 
  RETURNS BIGINT[] 
AS
$$
declare 
  l_result bigint[];
begin
  execute format('SELECT %I FROM days WHERE id = $1', p_column) 
     using p_id
     into l_result;
  return l_result;
end;     
$$
LANGUAGE plpgsql;

format() properly deals with identifiers when building dynamic SQL. The $1 is a parameter placeholder and the value for that is passed with the using p_id clause of the execute statement.