agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: James Sharrett <jsharrett@tidemark.com>
To: Adrian Klaver <adrian.klaver@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: dynamically referencing a column name in a function
Date: Sat, 15 Feb 2014 17:41:50 -0500
Message-ID: <CF25559D.477AC%jsharrett@tidemark.com> (raw)
In-Reply-To: <52FFE6CC.8090605@gmail.com>
References: <CF254376.47760%jsharrett@tidemark.com>
<CF25476A.47780%jsharrett@tidemark.com>
<52FFE6CC.8090605@gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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¹m 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¹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
view thread (4+ messages) latest in thread
Message-ID: <CF25559D.477AC%jsharrett@tidemark.com>
Permalink: ../CF25559D.477AC%25jsharrett@tidemark.com/
Also on: postgresql.org/message-id/CF25559D.477AC%jsharrett@tidemark.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jsharrett@tidemark.com, adrian.klaver@gmail.com
Subject: Re: dynamically referencing a column name in a function
In-Reply-To: <CF25559D.477AC%jsharrett@tidemark.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox