Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WEmsN-0000dD-2u for pgsql-sql@arkaria.postgresql.org; Sat, 15 Feb 2014 21:34:19 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WEmsM-0001Gk-Ew for pgsql-sql@arkaria.postgresql.org; Sat, 15 Feb 2014 21:34:18 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WEmsL-0001Gd-9q for pgsql-sql@postgresql.org; Sat, 15 Feb 2014 21:34:17 +0000 Received: from mail-pd0-x22f.google.com ([2607:f8b0:400e:c02::22f]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WEmsI-0000Td-6y for pgsql-sql@postgresql.org; Sat, 15 Feb 2014 21:34:16 +0000 Received: by mail-pd0-f175.google.com with SMTP id w10so13379607pde.20 for ; Sat, 15 Feb 2014 13:34:13 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=tidemark.com; s=google; h=user-agent:date:subject:from:to:message-id:thread-topic:references :in-reply-to:mime-version:content-type; bh=BDplRAF8bdBNF3MzZ42D2UhSUNlB07HUZ3WYgn+GhuI=; b=T/9JFpzNN1zWuV6uodh1QFb3XQT8JAoH2Ylhi8Q9H/aIKRnLZbItnf/1qvum3RwXin vG0YwC0lXi9tacDO1aY4eEZpIG8wUl9vedaY/zE9QfGCt9n028kP3wtWp2fOu648P+FU N0XBxcpDVOz1Gpu0QKOrs+SBWX6hZrRIB/mYI= X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:user-agent:date:subject:from:to:message-id :thread-topic:references:in-reply-to:mime-version:content-type; bh=BDplRAF8bdBNF3MzZ42D2UhSUNlB07HUZ3WYgn+GhuI=; b=aTcp2K4Bv4mHGDCuw+bx/rNW7qb149+4OgoZxSQXrHFbL5szWKsA7KdMj72PBF7xCf Lh/TNV8qbHUNVKv326UQfhV1SXz8QlW+yi1QQEevuXBoRXrqd3TwR6V5eB1eN+RQSNrH +y7Y0xqDJRqCG0qvGaIUd3EpLTBTeVeuBtN4J0QukYRcf53eRadjnT4+Bx7ERmttzI4S HbmNu2YMVMElqybBiHwfS7C8qvsoHXH8uTcsE+tI9V2GB+z2ln4oskaepzHxQnvLJ5z/ 51kJQrY1OhFMjgdE/GHzFRRp5Wrpc4Yuv1a/cWQ3JZgzJaiSxzDeeIB4tAYAZCjd8ScO 1cow== X-Gm-Message-State: ALoCoQmzMyrvFloXT7/werypx01VCjX/XsyhhqkogfuxTsmj9sn3I0/zkEcq4aGHGiYd3MVkURoB X-Received: by 10.68.176.65 with SMTP id cg1mr160537pbc.145.1392500053098; Sat, 15 Feb 2014 13:34:13 -0800 (PST) Received: from [10.0.1.2] (adsl-074-245-040-156.sip.clt.bellsouth.net. [74.245.40.156]) by mx.google.com with ESMTPSA id qw8sm30226085pbb.27.2014.02.15.13.34.10 for (version=TLSv1 cipher=RC4-SHA bits=128/128); Sat, 15 Feb 2014 13:34:12 -0800 (PST) User-Agent: Microsoft-MacOutlook/14.3.9.131030 Date: Sat, 15 Feb 2014 16:34:05 -0500 Subject: dynamically referencing a column name in a function From: James Sharrett To: Message-ID: Thread-Topic: dynamically referencing a column name in a function References: In-Reply-To: Mime-version: 1.0 Content-type: multipart/alternative; boundary="B_3475326850_45393460" X-Pg-Spam-Score: -2.0 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org > This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3475326850_45393460 Content-type: text/plain; charset="ISO-8859-1" Content-transfer-encoding: quoted-printable 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. Th= e issue is that I don=B9t 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=B9m not having luck. I=B9ve found a number of postings that have various work arounds but none seem to address the issue at hand. In the real code, I=B9m dealing with 100=B9s 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:=3D 'select * from a_test;'; --this outputs 10,25,40 as expected for sql_data in execute sql_qry loop sql_func_call:=3D '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:=3D '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:=3D '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:=3D '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; --B_3475326850_45393460 Content-type: text/html; charset="ISO-8859-1" Content-transfer-encoding: quoted-printable
Below is a stripped-down exam= ple 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= 217;t know the name of the column to pass into sub_test_function until run-t= ime.  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 paramet= ers 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, co= l_c) VALUES (35, 40, 45);

CREATE OR REPLACE FUNCTIO= N sub_test_function(col_value integer)
RETURNS integer as $$
=
begin
 return col_value;
end; $$
&nb= sp;LANGUAGE plpgsql;


--select * from= test_column_param('col_b');

CREATE OR REPLACE FUNC= TION test_column_param(col_name text)
RETURNS void as $$

declare
sql_qry text;
sql_data record;
sql_func_call text;
sub_func_ret integer;

<= /div>
begin
 sql_qry:=3D 'select * from a_test;';

 --this outputs 10,25,40 as expected
 fo= r sql_data in execute sql_qry loop
sql_func_call:=3D 'select * from sub_test_functi= on (' || sql_data.col_b || ');';
execute sql_func_call into sub_func_ret;

raise notice '%', sub_func_ret;

 end loop;<= /div>

/*
 --ERROR: record "sql_data" has n= o field "col_name" 
 for sql_data in execute sql_qry loo= p
sql= _func_call:=3D 'select * from sub_test_function (' || sql_data.col_name || ');= ';
ex= ecute sql_func_call into sub_func_ret;

raise notice '%', sub_func_= ret;

 end loop;


<= /div>
 --ERROR: syntax error at or near "." 
 f= or sql_data in execute sql_qry loop
sql_func_call:=3D 'select * from sub_test_funct= ion (' || sql_data || '.' || col_name || ');';
execute sql_func_call into sub_fun= c_ret;

raise notice '%', sub_func_ret;

&n= bsp;end loop;

--ERROR: schema "sql_data" does not e= xist
 for sql_data in execute sql_qry loop
sql_func_call:=3D 'select= * from sub_test_function (' || sql_data.quote_ident(col_name) || ');';
execute s= ql_func_call into sub_func_ret;

raise notice '%', sub_func_ret;

 end loop;
*/
end; $$
<= div> LANGUAGE plpgsql;

--B_3475326850_45393460--