Postgresql dynamic cursor (for record in EXECUTE), field access

Question

Suppose the block below:

do $$
declare
v_table_name text:='table_name';
v_column_name text:='column_name';
v_record record;
v_sql_dynamic text;
v_enum_xml xml;
v_string text;
begin
  v_sql_dynamic:=format('select %s from %s',v_column_name,v_table_name);
  FOR v_record IN EXECUTE v_sql_dynamic
  LOOP
   raise notice 'field with name % has a value of %',v_column_name,***?????***;
  END LOOP;
end;
$$ language plpgsql

How do I get access to the field of v_record in the way like: v_record.v_column_name?

Answer 1

See Abelisto's comment. While converting the value to text anyway, to_json() is good enough and slightly faster.

DO
$$
DECLARE
   v_table_name   text := 'table_name';
   v_column_name  text := 'column_name';
   v_sql_dynamic  text := format('select %s from %s', v_column_name, v_table_name);
   v_record       record;
BEGIN
   FOR v_record IN EXECUTE v_sql_dynamic
   LOOP
      RAISE NOTICE 'field named % has value %.'
    , v_column_name, to_json(v_record) ->> v_column_name;
   END LOOP;
END
$$ LANGUAGE plpgsql;

Related:

• How to get the value of a dynamically generated field name in PL/pgSQL