Passing column names dynamically for a record variable in PostgreSQL

Working with this dummy table

CREATE TEMP TABLE foo (id int, my_num numeric);INSERT INTO foo VALUES (1, 12.34)

First, I simplified and sanitized your example:

• Removed some noise that is irrelevant to the question.

• RETURNS SETOF void hardly makes sense. I use RETURNS void instead.

• I use text instead of character varying, just for the sake of simplicity.

• When using dynamic SQL, you have to safeguard against SQL injection, I use format() with %I in this case. There are other ways.

The basic problem is that SQL is very rigid with types and identifiers. You are operating with dynamic table name as well as with dynamic field name of a record - an anonymous record in your original example. Pl/pgSQL is not well equipped to deal with this. Postgres does not know what's inside an anonymous record. Only after you assign the record to a well known type can you reference individual fields.
Here is a closely related question, trying to set a field of a record with dynamic name:
How to set value of composite variable field using dynamic SQL

Basic function

CREATE OR REPLACE FUNCTION getrowdata1(table_name text, id int)  RETURNS void AS$func$ DECLARE   srowdata record;   reqfield text := 'my_num';   -- assigning at declaration time for convenience   value    numeric;BEGINRAISE NOTICE 'id: %', id; EXECUTE format('SELECT * FROM %I WHERE id = $1', table_name)USING  idINTO   srowdata;RAISE NOTICE 'srowdata: %', srowdata;RAISE NOTICE 'srowdatadata.my_num: %', srowdata.my_num;/* This does not work, even with dynamic SQLEXECUTE format('SELECT ($1).%I', reqfield)USING srowdataINTO value;RAISE NOTICE 'value: %', value;*/END$func$ LANGUAGE plpgsql;

Call:

SELECT * from getrowdata1('foo', 1);

The commented part would raise an exception:

could not identify column "my_num" in record data type: SELECT * from getrowdata(1,'foo')

hstore

You need to install the additional module hstore for this. Once per database with:

CREATE EXTENSION hstore;

Then all could work like this:

CREATE OR REPLACE FUNCTION getrowdata2(table_name text, id int)  RETURNS void AS$func$ DECLARE   hstoredata hstore;   reqfield   text := 'my_num';   value      numeric;BEGINRAISE NOTICE 'id: %', id; EXECUTE format('SELECT hstore(t) FROM %I t WHERE id = $1', table_name)USING  idINTO   hstoredata;RAISE NOTICE 'hstoredata: %', hstoredata;RAISE NOTICE 'hstoredata.my_num: %', hstoredata -> 'my_num';value := hstoredata -> reqfield;RAISE NOTICE 'value: %', value;END$func$ LANGUAGE plpgsql;

Call:

SELECT * from getrowdata2('foo', 1);

Polymorphic type

Alternative without installing additional modules.

Since you select a whole row into your record variable, there is a well defined type for it per definition. Use it. The key word is polymorphic types.

CREATE OR REPLACE FUNCTION getrowdata3(_tbl anyelement, id int)  RETURNS void AS$func$ DECLARE   reqfield text := 'my_num';   value    numeric;BEGINRAISE NOTICE 'id: %', id; EXECUTE format('SELECT * FROM %s WHERE id = $1', pg_typeof(_tbl))USING  idINTO   _tbl;RAISE NOTICE '_tbl: %', _tbl;RAISE NOTICE '_tbl.my_num: %', _tbl.my_num;EXECUTE 'SELECT ($1).' || reqfield   -- requfield must be SQLi-safe or escapeUSING _tblINTO  value;RAISE NOTICE 'value: %', value;END$func$ LANGUAGE plpgsql;

Call:

SELECT * from getrowdata3(NULL::foo, 1);

-> SQLfiddle

• I (ab-)use the input parameter _tbl for three purposes here:

  • Provides the well defined type of the record
  • Provides the name of the table, automatically schema-qualified
  • Serves as variable.

• More explanation in this related answer (last chapter):
  Refactor a PL/pgSQL function to return the output of various SELECT queries