How to specify with a parameter a field to extract from a RECORD variable

Question

Here's a table:

CREATE TABLE t_heights (id INT, height REAL);
INSERT INTO t_heights VALUES (1,53.63), (2,45.19), (3,47.06);

Reading through the table rows with a FOR...LOOP and a RECORD type variable like this works:

CREATE OR REPLACE FUNCTION fnct_row_by_row (input_table regclass) 
RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE
 current_row RECORD;
BEGIN
FOR current_row IN EXECUTE 'SELECT * FROM '||input_table
 LOOP
 RAISE NOTICE 'field value: %',current_row.height; -- *<<< hardcoded field name*
 END LOOP;
END;
$$
;

Results:

SELECT fnct_row_by_row ('t_heights') ;
NOTICE: field value: 53.63
NOTICE: field value: 45.19
NOTICE: field value: 47.06

However, the field extracted from the RECORD variable needs to have its name hardcoded.

How to specify dynamically the field to be extracted?

The following generates an error:

CREATE OR REPLACE FUNCTION fnct_row_by_row2 (input_table regclass, input_field_name TEXT) 
RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE
 current_row RECORD;
BEGIN
FOR current_row IN EXECUTE 'SELECT * FROM '||input_table
 LOOP
 RAISE NOTICE 'specified field: %',current_row.input_field_name; -- *<<< field name from parameter*
 END LOOP;
END;
$$
;

Execution:

SELECT fnct_row_by_row2 ('t_heights','height') ;
ERROR: record "current_row" has no field "input_field_name"
CONTEXTE : SQL statement "SELECT current_row.input_field_name"
PL/pgSQL function fnct_row_by_row2(regclass,text) line 10 at RAISE

Answer 1

Just do

 RAISE NOTICE 'specified field: %', input_field_name;

It's already a variable at the top. That will work if you want the column/field name. If you want the column's value you'll need to use something that provides a dynamic accessor to a row type. For that, you need.

• jsonb (as mentioned in the comments by Abelisto)
• hstore

That said, This whole thing is a really bad idea. When possible, you should just create a function that processes a scalar and run it over the result set.

CREATE FUNCTION f(a text)
RETURNS text
AS $$
 SELECT 'foo' + a;
$$ LANGUAGE sql;
SELECT f(x) FROM t;

Rather than doing something that's more complex and statically types the table name burried within.

CREATE FUNCTION fThatOpensACursor(col text)
RETURNS SETOF text
AS $$
 SELECT to_jsonb(t)->>col
 FROM statically_typed_table AS t;
$$ LANGUAGE sql;
SELECT fThatOpensACursor('t.myCol');

• Sébastien Clément: My example was just a part, for clarity, of a larger function where I loop through each row of a table and use some of the columns' values from that row to compare to a second table and return each time a new computed dataset. Thanks for your suggestion, but it's not clear to me how it applies.