How to extract a value trom rowtype using a dynamyc fieldname?

Question

I have a function that it receives in input a %ROWTYPE and a variable which contains the name of field in ROWTYPE

For example, my ROWTYPE contains 3 fields

data_row as data%ROWTYPE

  data_row.Code
  data_row.Description
  data_row.Value

And the fieldName variable contains 'Description'

Where is the most simple way in order to extract a value from data_row.Description?

Regrards, Marco

Answer 1

You can't refer to the record fields dynamically, at least without jumping through a lot of hoops using dynamic SQL and either requerying the underlying table or creating extra objects.

A simple way to do this is just with a case statement:

case upper(fieldName)
  when 'CODE' then
    -- do something with data_row.code
  when 'DESCRIPTION' then
    -- do something with data_row.description
  when 'VALUE' then
    -- do something with data_row.value
  else
    -- possibly indicate an error
end case;

The record field references are now static, it's just the decision of which to look at that is decided at runtime.

db<>fiddle demo

You may need to do some extra work to handle the data types being different; in that demo I'm relying on implicit conversion of everything to strings, which is kind of OK for numbers (or integers anyway) but still not ideal, and certainly not a good idea for dates. But how you handle that will depend on what your function will do with the data.