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