Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-sql-owner+M37664=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
 id 1WEnVU-000237-Oc
 for pgsql-sql@arkaria.postgresql.org; Sat, 15 Feb 2014 22:14:45 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-sql-owner+M37664=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
 id 1WEnVU-0007Ft-8D
 for pgsql-sql@arkaria.postgresql.org; Sat, 15 Feb 2014 22:14:44 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
 by malur.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <adrian.klaver@gmail.com>) id 1WEnVT-0007Fn-IU
 for pgsql-sql@postgresql.org; Sat, 15 Feb 2014 22:14:43 +0000
Received: from mail-pb0-x22b.google.com ([2607:f8b0:400e:c01::22b])
 by magus.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <adrian.klaver@gmail.com>) id 1WEnVQ-0003Ru-P5
 for pgsql-sql@postgresql.org; Sat, 15 Feb 2014 22:14:43 +0000
Received: by mail-pb0-f43.google.com with SMTP id md12so13790661pbc.2
 for <pgsql-sql@postgresql.org>; Sat, 15 Feb 2014 14:14:39 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
 h=message-id:date:from:user-agent:mime-version:to:subject:references
 :in-reply-to:content-type:content-transfer-encoding;
 bh=54hI2oXz/zG/XmY3hSRVKrXJ1gNXbLFm/wNvvVV5+gg=;
 b=eFycFyDnbHb3atSyfTHCjBst2KUbBSgw2zg3dZZo9XQfLyK74Lo4qfCecWew2nWndN
 B/6O0E3wTstMZUwb6yU8nNWr0zOAuhDAwVM6lB9zzAHPoSCXlnSP/Bxbn0lSViV807ix
 4Qcok55HR2CD/Rb7g1EhhnpOfZWlXEx5VgIkUgMYg8WtFOAZeSuy9Gkzgxh+pSKQ5vV4
 0iCsJt5gY7pG6Do/uS+X3A9jurweExz/yLHAGVID4j4CxgYonFEg/bkMldiSPPyLyspS
 jGylo7gbe99NftXc+YjkYEvA/JxtcGlkFj04Xd7qcdvN/wNZin+9fr8JUq7djoFMfpPV
 63pA==
X-Received: by 10.68.138.165 with SMTP id qr5mr17487548pbb.123.1392502479180;
 Sat, 15 Feb 2014 14:14:39 -0800 (PST)
Received: from panda.site (65-102-185-39.tukw.qwest.net. [65.102.185.39])
 by mx.google.com with ESMTPSA id nz11sm76188224pab.6.2014.02.15.14.14.38
 for <multiple recipients>
 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128);
 Sat, 15 Feb 2014 14:14:38 -0800 (PST)
Message-ID: <52FFE6CC.8090605@gmail.com>
Date: Sat, 15 Feb 2014 14:14:36 -0800
From: Adrian Klaver <adrian.klaver@gmail.com>
User-Agent: Mozilla/5.0 (X11; Linux i686;
 rv:24.0) Gecko/20100101 Thunderbird/24.3.0
MIME-Version: 1.0
To: James Sharrett <jsharrett@tidemark.com>, pgsql-sql@postgresql.org
Subject: Re: dynamically referencing a column name in a function
References: <CF254376.47760%jsharrett@tidemark.com>
 <CF25476A.47780%jsharrett@tidemark.com>
In-Reply-To: <CF25476A.47780%jsharrett@tidemark.com>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 8bit
X-Pg-Spam-Score: -2.0 (--)
List-Archive: <http://archives.postgresql.org/pgsql-sql>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-sql.postgresql.org>
List-Owner: <mailto:pgsql-sql-owner@postgresql.org>
List-Post: <mailto:pgsql-sql@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-sql>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
X-Mailing-List: pgsql-sql
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql