Access column using variable instead of explicit column name

Question

I would like to access a column by using variable instead of a static column name.
Example:

variable := 'customer';

SELECT table.variable (this is what I would prefer) instead of table.customer

I need this functionality as records in my table vary in terms of data length (eg. some have data in 10 columns, some in 14 or 16 columns etc.) so I need to address columns dynamically. As I understand, I can't address columns by their index (eg. select 8-th column of the table) right?

I can loop and put the desired column name in a variable for the given iteration. However, I get errors when I try to access a column using that variable (e.g. table_name.variable is not working).

For the sake of simplicity, I paste just some dummy code to illustrate the issue:

CREATE OR REPLACE FUNCTION dynamic_column_name() returns text
LANGUAGE PLPGSQL
AS $$
DECLARE
col_name text;
return_value text;

BEGIN

create table customer (
    id bigint,
    name varchar
);

INSERT INTO customer VALUES(1, 'Adam');

col_name := 'name';

-- SELECT customer.name INTO return_value FROM customer WHERE id = 1; -- WORKING, returns 'Adam' but it is not DYNAMIC.
-- SELECT customer.col_name INTO return_value FROM customer WHERE id = 1; -- ERROR:  column customer.col_name does not exist
-- SELECT 'customer.'||col_name INTO return_value FROM customer WHERE id = 1; -- NOT working, returns 'customer.name'
-- SELECT customer||'.'||col_name INTO return_value FROM customer WHERE id = 1; -- NOT working, returns whole record + .name, i.e.: (1,Adam).name

DROP TABLE customer;
RETURN return_value;
END;
$$;

SELECT dynamic_column_name();

So how to obtain 'Adam' string with SQL query using col_name variable when addressing column of customer table?

Erwin Brandstetter · Accepted Answer · 2021-05-12T15:31:53.373

1

SQL does not allow to parameterize identifiers (incl. column names) or syntax elements. Only values can be parameters.

You need dynamic SQL for that. (Basically, build the SQL string and execute.) Use EXECUTE in a plpgsql function. There are multiple syntax variants. For your simple example:

CREATE OR REPLACE FUNCTION dynamic_column_name(_col_name text, OUT return_value text)
  RETURNS text
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE format('SELECT %I FROM customer WHERE id = 1', _col_name)
   INTO return_value;
END
$func$;

Call:

SELECT dynamic_column_name('name');

db<>fiddle here

Data types have to be compatible, of course.

More examples:

edited May 12 '21 at 15:31

answered May 12 '21 at 15:26

Erwin Brandstetter

605,456
145
1,078
1,228

Thanks a lot Erwin Brandstetter! Execute and format allowed me to solve my real case problem. I wonder if I can apply the same to the CURSOR? I.e. I would like to traverse each row(record) and do something with its values. However I still need to access the columns using a variable (e.g. cursor.variable instead of cursor.name). I managed to do the task by traversing table using subsequent IDs and with each iteration selecting desired values by searching with that ID - which is inefficient. I was unable to achieve that with Cursor which sould save me the searching by ID every iteration – Jans May 17 '21 at 11:30
@jans: You might ask another *question* with all relevant details about that cursor thing. – Erwin Brandstetter May 17 '21 at 22:52

Access column using variable instead of explicit column name

1 Answers1

Linked