dynamically referencing a column name in a function

Question

Below is a stripped-down example to show the crux of my issue.

I have a function (test_column_param) that calls a sub-function (sub_test_function) and passes in a value from a column query that is being looped through. The issue is that I don’t know the name of the column to pass into sub_test_function until run-time. The name of the column is passed into test_column_param and I want to use that value to dynamically pull the correct column value from the recordset. But I’m not having luck.

I’ve found a number of postings that have various work arounds but none seem to address the issue at hand. In the real code, I’m dealing with 100’s of columns that are returned from sql_qry and have multiple column parameters that need to be dynamically passed into the sub-function call. Any advice is greatly appreciated.

CREATE TABLE demo.a_test
(
 col_a integer,
 col_b integer,
col_c integer
);

_

INSERT INTO demo.a_test(col_a, col_b, col_c) VALUES (5, 10, 15);
INSERT INTO demo.a_test(col_a, col_b, col_c) VALUES (20, 25, 30);
INSERT INTO demo.a_test(col_a, col_b, col_c) VALUES (35, 40, 45);

-

CREATE OR REPLACE FUNCTION demo.sub_test_function(col_value integer)
RETURNS integer as $$
begin
return col_value;
end; $$
LANGUAGE plpgsql;

_

CREATE OR REPLACE FUNCTION demo.test_column_param(col_name text)
RETURNS void as $$
declare
sql_qry text;
sql_data record;
sql_func_call text;
sub_func_ret integer;

begin
sql_qry:= 'select * from demo.a_test;';

 --this outputs 10,25,40 as expected
 for sql_data in execute sql_qry loop
sql_func_call:= 'select * from demo.sub_test_function (' || sql_data.col_b || ');';
execute sql_func_call into sub_func_ret;

raise notice '%', sub_func_ret;

 end loop;
end; $$
 LANGUAGE plpgsql;

_

select * from demo.test_column_param('col_b');

The following attempts to use the parameter value in conjunction with the record reference fail with the associated error messages

--ERROR: record "sql_data" has no field "col_name" 
 for sql_data in execute sql_qry loop
sql_func_call:= 'select * from demo.sub_test_function (' || sql_data.col_name || ');';
execute sql_func_call into sub_func_ret;

raise notice '%', sub_func_ret;

 end loop;

_

--ERROR: syntax error at or near "." 
for sql_data in execute sql_qry loop
sql_func_call:= 'select * from demo.sub_test_function (' || sql_data || '.' || col_name || ');';
execute sql_func_call into sub_func_ret;

raise notice '%', sub_func_ret;

end loop;

_

--ERROR: schema "sql_data" does not exist
for sql_data in execute sql_qry loop
sql_func_call:= 'select * from demo.sub_test_function (' || sql_data.quote_ident(col_name) || ');';
execute sql_func_call into sub_func_ret;

raise notice '%', sub_func_ret;

end loop;

Answer 1

Create initial query dynamically using column name

CREATE OR REPLACE FUNCTION demo.test_column_param(col_name text)
RETURNS void as $$
declare
sql_qry text;
sql_data record;

begin
  sql_qry:= 'select demo.sub_test_function(' || col_name || )' as value from demo.a_test;';

  for sql_data in execute sql_qry loop

    raise notice '%', sql_data.value;

  end loop;
end; $$
 LANGUAGE plpgsql;