Thread: dynamically referencing a column name in a function
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;
On 02/15/2014 01:34 PM, James Sharrett wrote:
> 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');
>
The below works, but will probably not scale for what you want to do.
The problem if I remember correctly is you cannot modify the record
variable once it has been assigned to. For the sort of dynamic stuff you
want to do a more forgiving language is probably in order. When I do
this sort of thing I use plpythonu.
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 '|| col_name ||' as col 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 || ')';
execute sql_func_call into sub_func_ret;
raise notice '%', sub_func_ret;
end loop;
end; $$ LANGUAGE plpgsql;
>
--
Adrian Klaver
adrian.klaver@gmail.com
Thanks Adrian. The problem is that the data query will return many
columns all of which I need in later operations. For this specific block
in the process, I need to take a pair of columns, based on user inputs,
and run their values them thru the operations performed in the sub
function to perform some operations and return the results and I need the
values I pass into the parameter to be in sync with the recordset which
will have many records with the same values for the column pairs so
keeping the whole record being operated on in line with the values passed
to the sub-function becomes the difficult part. I¹m trying to avoid using
a cursor but even with that I think I may run into similar issues.
On 2/15/14, 5:14 PM, "Adrian Klaver" <adrian.klaver@gmail.com> wrote:
>On 02/15/2014 01:34 PM, James Sharrett wrote:
>> 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');
>>
>
>The below works, but will probably not scale for what you want to do.
>The problem if I remember correctly is you cannot modify the record
>variable once it has been assigned to. For the sort of dynamic stuff you
>want to do a more forgiving language is probably in order. When I do
>this sort of thing I use plpythonu.
>
>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 '|| col_name ||' as col 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 ||
>')';
>execute sql_func_call into sub_func_ret;
>
>raise notice '%', sub_func_ret;
>
> end loop;
>end; $$
> LANGUAGE plpgsql;
>
>>
>
>
>--
>Adrian Klaver
>adrian.klaver@gmail.com
On 02/15/2014 02:41 PM, James Sharrett wrote: > Thanks Adrian. The problem is that the data query will return many > columns all of which I need in later operations. For this specific block > in the process, I need to take a pair of columns, based on user inputs, > and run their values them thru the operations performed in the sub > function to perform some operations and return the results and I need the > values I pass into the parameter to be in sync with the recordset which > will have many records with the same values for the column pairs so > keeping the whole record being operated on in line with the values passed > to the sub-function becomes the difficult part. I¹m trying to avoid using > a cursor but even with that I think I may run into similar issues. > Well I did say it probably would not scale:) Without a concrete example of the actual process it would be hard to offer any specific advice. -- Adrian Klaver adrian.klaver@gmail.com