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 and join pg_class and possibly pg_namespace. 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
– sticky bit, Commented May 8, 2021 at 22:45

Erwin Brandstetter · Accepted Answer · 2024-07-14 14:53:25Z

2

There is no apparent reason to complicate matters with a function. Simply:

SELECT col1 FROM tab1;

To achieve what you ask for, you need dynamic SQL because SQL does not allow to parameterize identifiers - or anything but values for that matter:

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');

Collectives™ on Stack Overflow

PostgreSQL function taking column name as argument

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