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

dynamic-sqlfunctionsplpgsqlpostgresql

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

Best Answer

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