agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Adrian Klaver <adrian.klaver@gmail.com>
To: James Sharrett <jsharrett@tidemark.com>
To: pgsql-sql@postgresql.org
Subject: Re: dynamically referencing a column name in a function
Date: Sat, 15 Feb 2014 14:14:36 -0800
Message-ID: <52FFE6CC.8090605@gmail.com> (raw)
In-Reply-To: <CF25476A.47780%jsharrett@tidemark.com>
References: <CF254376.47760%jsharrett@tidemark.com>
<CF25476A.47780%jsharrett@tidemark.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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: <52FFE6CC.8090605@gmail.com>
Permalink: ../52FFE6CC.8090605@gmail.com/
Also on: postgresql.org/message-id/52FFE6CC.8090605@gmail.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: adrian.klaver@gmail.com, jsharrett@tidemark.com
Subject: Re: dynamically referencing a column name in a function
In-Reply-To: <52FFE6CC.8090605@gmail.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