Re: dynamically referencing a column name in a function

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(dot)klaver(at)gmail(dot)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(dot)klaver(at)gmail(dot)com