dynamically referencing a column name in a function

From: James Sharrett <jsharrett(at)tidemark(dot)com>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: dynamically referencing a column name in a function
Date: 2014-02-15 21:34:05
Message-ID: CF25476A.47780%jsharrett@tidemark.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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 a_test
(
col_a integer,
col_b integer,
col_c integer
);
INSERT INTO a_test(col_a, col_b, col_c) VALUES (5, 10, 15);
INSERT INTO a_test(col_a, col_b, col_c) VALUES (20, 25, 30);
INSERT INTO a_test(col_a, col_b, col_c) VALUES (35, 40, 45);

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

--select * from test_column_param('col_b');

CREATE OR REPLACE FUNCTION 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 a_test;';

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

raise notice '%', sub_func_ret;

end loop;

/*
--ERROR: record "sql_data" has no field "col_name"
for sql_data in execute sql_qry loop
sql_func_call:= 'select * from 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 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 sub_test_function (' ||
sql_data.quote_ident(col_name) || ');';
execute sql_func_call into sub_func_ret;

raise notice '%', sub_func_ret;

end loop;
*/
end; $$
LANGUAGE plpgsql;

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Adrian Klaver 2014-02-15 22:14:36 Re: dynamically referencing a column name in a function
Previous Message Adrian Klaver 2014-02-15 15:30:09 Re: