pl/pgsql: Dynamicly get a column name from a record Greeting,
I want dynamicly get a column name from a record.
As showing in the code below that I created a cursor and I used a loop to process each record in that cursor by fetching each row to a record type r1.
In my table I have these columns [dlq_2000,dlq_2001,...,dlq_2017,dlq_2017].
Also I created on top of it a loop to process each column separate.

The issue I am facing is getting the field name from r1 dynamicly and I am getting this error when I run the code:


  [Err] ERROR: record "r1" has no field "'dlq_'||counter::text" CONTEXT:
  SQL statement "SELECT ( r1."'dlq_'||counter::text" = 1 )"


Please advice how to solve this issue.

Thank you,

CREATE OR REPLACE FUNCTION update()
   RETURNS VOID AS $$
DECLARE 
cur SCROLL CURSOR   FOR select * from my_tbl;
r1 RECORD;
begin
OPEN cur ;
FOR counter IN  2000..2017 LOOP
    r1 := NULL;
    LOOP
        FETCH cur INTO r1;
        EXIT WHEN NOT FOUND;

        IF (r1."'dl_'||counter::text" = 1 ) THEN
            -- do some thing
            RAISE NOTICE 'processing year of : %', counter;
        END IF;     
    END LOOP;
END LOOP;
CLOSE cur;
END; 

 $$

LANGUAGE plpgsql;
Using row_to_json function:

do $$
declare
  r json;
  i int;
begin
  for r in 
    select row_to_json(t.*) 
    from (values(1,'a1','a2','a3'),(2,'b1','b2','b3')) as t(x,y11,y12,y13) 
  loop
    raise info '%', r;
    for i in 11..13 loop
      if r-&gt;&gt;('y'||i) like '%2' then -- Condition here
        raise info 'Do something for %', r-&gt;&gt;('y'||i);
      end if;
    end loop;
  end loop;
end $$;



INFO:  {"x":1,"y11":"a1","y12":"a2","y13":"a3"}
INFO:  Do something for a2
INFO:  {"x":2,"y11":"b1","y12":"b2","y13":"b3"}
INFO:  Do something for b2




Using array constructor:

do $$
declare
  r record;
  i int;
begin
  for r in 
    select x, array[y11,y12,y13] as y 
    from (values(1,'a1','a2','a3'),(2,'b1','b2','b3')) as t(x,y11,y12,y13) 
  loop
    raise info '%', r;
    for i in 1..3 loop
      if r.y[i] like '%2' then -- Condition here
        raise info 'Do something for %', r.y[i];
      end if;
    end loop;
  end loop;
end $$;



INFO:  (1,"{a1,a2,a3}")
INFO:  Do something for a2
INFO:  (2,"{b1,b2,b3}")
INFO:  Do something for b2




And using data normalization:

do $$
declare
  r record;
  i int;
begin
  for r in
    with
      test as (
        select * 
        from (values(1,'a1','a2','a3'),(2,'b1','b2','b3')) as t(x,y11,y12,y13)),
      norm as (
        select *, unnest(array[y11,y12,y13]) as y, unnest(array[11,12,13]) as z from test)
    select * from norm
    where y like '%2'  -- Condition here
  loop
    raise info 'Do something for %', r;
  end loop;
end $$;



INFO:  Do something for (1,a1,a2,a3,a2,12)
INFO:  Do something for (2,b1,b2,b3,b2,12)


There are simplified examples, just to show how it could be done in the your more complex task.But it was tested on the PostgreSQL 9.5