PostgreSQL function taking column name as argument

Question

I have a table tab1 with four columns col1, col2, col3 and col4.

I want to create a function like f4(a) where a is defined by user and if user types select f4(col1) he gets column tab1.col1.

Is there any way to create such function in PostgreSQL?

The question is to broad. You should try something and come back if you have a **specific** problem, not just a requirement dump. Hint: You can query [`pg_attribute`](https://www.postgresql.org/docs/current/catalog-pg-attribute.html) and join [`pg_class`](https://www.postgresql.org/docs/current/catalog-pg-class.html) and possibly [`pg_namespace`](https://www.postgresql.org/docs/current/catalog-pg-namespace.html). And you should think about how to handle the case, when more than one table has a column named like the function's input. — sticky bit, May 08 '21 at 22:45

score 2 · Answer 1 · answered May 09 '21 at 02:16

There is really not good reason to complicate matters with a function here.
What you should do instead:

SELECT col1 FROM tab1;

What you ask for:

CREATE OR REPLACE FUNCTION f4(_col text)
  RETURNS TABLE (col_x text)
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY EXECUTE 
   format('SELECT %I FROM tab1', _col);
END
$func$;

Call:

SELECT * FROM f4('col1');

You need dynamic SQL because SQL does not allow to parameterize identifiers.

PostgreSQL function taking column name as argument

1 Answers1