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?

user330315 · Accepted Answer · 2020-01-22 09:08:31Z

11

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.

answered Jan 22, 2020 at 9:08

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using parameter as column name in Postgres function

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related