Re: dynamically referencing a column name in a function

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