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;
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: |