agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: James Sharrett <jsharrett@tidemark.com>
To: pgsql-sql@postgresql.org
Subject: dynamically referencing a column name in a function
Date: Sat, 15 Feb 2014 16:34:05 -0500
Message-ID: <CF25476A.47780%jsharrett@tidemark.com> (raw)
In-Reply-To: <CF254376.47760%jsharrett@tidemark.com>
References: <CF254376.47760%jsharrett@tidemark.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>

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');

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 * 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_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:= '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:= '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:= '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;

view thread (4+ messages)  latest in thread

Message-ID: <CF25476A.47780%jsharrett@tidemark.com>
Permalink:  ../CF25476A.47780%25jsharrett@tidemark.com/
Also on:    postgresql.org/message-id/CF25476A.47780%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
  Subject: Re: dynamically referencing a column name in a function
  In-Reply-To: <CF25476A.47780%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