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->>('y'||i) like '%2' then -- Condition here
        raise info 'Do something for %', r->>('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

StackExchange Database Administrators Q#163950, answer score: 7