Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-sql-owner+M37665=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
 id 1WEnvz-00037C-BB
 for pgsql-sql@arkaria.postgresql.org; Sat, 15 Feb 2014 22:42:07 +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+M37665=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
 id 1WEnvy-0006Xe-I2
 for pgsql-sql@arkaria.postgresql.org; Sat, 15 Feb 2014 22:42:06 +0000
Received: from makus.postgresql.org ([2001:4800:7903:4::125])
 by malur.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <jsharrett@tidemark.com>) id 1WEnvv-0006SN-69
 for pgsql-sql@postgresql.org; Sat, 15 Feb 2014 22:42:03 +0000
Received: from mail-pb0-x22f.google.com ([2607:f8b0:400e:c01::22f])
 by makus.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <jsharrett@tidemark.com>) id 1WEnvs-0001ob-Hr
 for pgsql-sql@postgresql.org; Sat, 15 Feb 2014 22:42:02 +0000
Received: by mail-pb0-f47.google.com with SMTP id rp16so13847269pbb.20
 for <pgsql-sql@postgresql.org>; Sat, 15 Feb 2014 14:41:59 -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:content-transfer-encoding;
 bh=0oWfG2kzFSvaSc+x/v72Bl3/xoIv5+DrdIScCOxM8Y4=;
 b=O7qvEoppDdGONbQIEZFlGThAhYCXPEP7jLEGFj872bBCUjvPzAIpXpv+oJwQqCyMig
 57S4rXNAa77k/FokRyTwen8C/I9RyJLx4T0aohPw7CerpU/SbWStbBUwMk+pvrUTTmDb
 LZlSB5yGjU7M5/iPvvYyEuX+WYcvzQVVm+cM8=
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
 :content-transfer-encoding;
 bh=0oWfG2kzFSvaSc+x/v72Bl3/xoIv5+DrdIScCOxM8Y4=;
 b=cXAahwgU2+Hgh0NU/OEiJGDWX2kgKi9ED96ftKfxWnhqIoziF1nMZQfKsW1JxfUh+Y
 2a425H2DFtx9cgCWzt8hWh8L0My6Ngb6+FVFmmlNlf11GpPL9BnOnSEsKbZrDpXIEBgI
 1Jrl9IQE95ZmUvmfVhGYtBUfYs7TMDnD8LkEf1EHJZH8J5Z3FVzV8DjF1dP6NwutobUd
 CtcO6j0s2CiPVUeYwu7JGKAgBQdvVM2fmfn/LMu57u4x2F+cIJGkLQL2JGaCZUt/uUTt
 AXKMhF5z3JQsJHuLvGmP7LKdQmG1ZyT4U8bV6l6b0zAs28O5h7/VQhJrMlHXKhpsauaK
 yn3g==
X-Gm-Message-State: 
 ALoCoQmXKuBqUUm0dN6cd3X29VCyaF9fHEI70cU2UB1tC7OWdUXCTV6sD7UucpH8lsH1VOLWpgAx
X-Received: by 10.66.136.229 with SMTP id qd5mr17675156pab.118.1392504119043;
 Sat, 15 Feb 2014 14:41:59 -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 rb6sm30509121pbb.41.2014.02.15.14.41.55
 for <multiple recipients>
 (version=TLSv1 cipher=RC4-SHA bits=128/128);
 Sat, 15 Feb 2014 14:41:58 -0800 (PST)
User-Agent: Microsoft-MacOutlook/14.3.9.131030
Date: Sat, 15 Feb 2014 17:41:50 -0500
Subject: Re: dynamically referencing a column name in a function
From: James Sharrett <jsharrett@tidemark.com>
To: Adrian Klaver <adrian.klaver@gmail.com>,
	<pgsql-sql@postgresql.org>
Message-ID: <CF25559D.477AC%jsharrett@tidemark.com>
Thread-Topic: [SQL] dynamically referencing a column name in a function
References: <CF254376.47760%jsharrett@tidemark.com>
 <CF25476A.47780%jsharrett@tidemark.com>
 <52FFE6CC.8090605@gmail.com>
In-Reply-To: <52FFE6CC.8090605@gmail.com>
Mime-version: 1.0
Content-type: text/plain;
	charset="ISO-8859-1"
Content-transfer-encoding: quoted-printable
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

Thanks Adrian.  The problem is that the data query will return many
columns all of which I need in later operations.  For this specific block
in the process, I need to take a pair of columns, based on user inputs,
and run their values them thru the operations performed in the sub
function to perform some operations and return the results and I need the
values I pass into the parameter to be in sync with the recordset which
will have many records with the same values for the column pairs so
keeping the whole record being operated on in line with the values passed
to the sub-function becomes the difficult part.  I=B9m trying to avoid using
a cursor but even with that I think I may run into similar issues.

On 2/15/14, 5:14 PM, "Adrian Klaver" <adrian.klaver@gmail.com> wrote:

>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=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 wo=
rk
>> 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');
>>
>
>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:=3D '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:=3D '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;
>
>>
>
>
>--=20
>Adrian Klaver
>adrian.klaver@gmail.com




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