4

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;
2
  • Are you trying to get the field name, or the field by-name? Feb 10, 2017 at 22:36
  • @CraigRinger I am trying to get the value of column named "'dl_'||counter::text" and compare it with 1 using any method.
    – Eyla
    Feb 10, 2017 at 22:42

3 Answers 3

3

You do not have an easy way to just check a "variable column". There is a (not very elegant) way of achieving this result, by using a CASE:

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
              (CASE counter 
                WHEN 2000 THEN r1.dl_2000
                WHEN 2001 THEN r1.dl_2001
                WHEN 2002 THEN r1.dl_2002
                WHEN 2003 THEN r1.dl_2003
                WHEN 2004 THEN r1.dl_2004
                WHEN 2005 THEN r1.dl_2005
                WHEN 2006 THEN r1.dl_2006
                WHEN 2007 THEN r1.dl_2007
                WHEN 2008 THEN r1.dl_2008
                WHEN 2009 THEN r1.dl_2009
                WHEN 2010 THEN r1.dl_2010
                WHEN 2011 THEN r1.dl_2011
                WHEN 2012 THEN r1.dl_2012
                WHEN 2013 THEN r1.dl_2013
                WHEN 2014 THEN r1.dl_2014
                WHEN 2015 THEN r1.dl_2015
                WHEN 2016 THEN r1.dl_2016
                WHEN 2017 THEN r1.dl_2017
              END) = 1 
            THEN
                -- do some thing
                RAISE NOTICE 'processing year of : %', counter;
            END IF;     
        END LOOP;
    END LOOP;
    CLOSE cur;
END; 
$$
LANGUAGE plpgsql;

I assume that all columns dl_2000 .. dl_2017 are defined to be integer (or bit). That is, the table definition looks like:

CREATE TABLE t
(
    /* some columns */
    dl_2000 integer, 
    dl_2001 integer, 
    dl_2002 integer, 
    dl_2003 integer, 
    /* ... */
    dl_2017 integer,
    /* more columns */
) ;

You can use instead an ARRAY of integers:

CREATE TABLE t
(
    /* some columns */
    dl integer[],
    /* more columns */
) ;

(You can have NOT NULL constraints on dl in the same way you have them on columns; although written diferently. That is, if necessary, you can have a CHECK (dl[2000] NOT NULL). You cannot have FOREIGN KEY constraints in those cases.

Then, your function would simply be:

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 dl[counter] = 1 THEN
                -- do some thing
                RAISE NOTICE 'processing year of : %', counter;
            END IF;     
        END LOOP;
    END LOOP;
    CLOSE cur;
END; 
$$
LANGUAGE plpgsql;

NOTE: I have not changed the logic in your function, although I am not really sure I would do it this way. At least, I would exchange the two loops. A big one for the cursor, and then inside, one for the columns (or indices of the array).

7

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

2

Not to step on @joanolo's toes, but here's a different way to approach the answer, if I am understanding what you're after. I agree that storing the data differently might make querying easier.

Given data like the following:

CREATE TABLE my_tbl ( id serial primary key, dl_2000 integer, dl_2001 integer, dl_2002 integer, dl_2003 integer, dl_2017 integer); insert into my_tbl (dl_2000,dl_2001,dl_2002,dl_2003,dl_2017) values (1,null,null,null,1), (1,null,1,null,null), (null,1,null,null,null), (2,4,5,6,7);

You can query the columns dynamically for matches:

DO $$ DECLARE rec RECORD; v_col_id TEXT; BEGIN FOR rec IN SELECT column_name,array_to_string(regexp_matches(column_name,'[0-9]+$'),',')::integer AS year FROM information_schema.columns WHERE table_name = 'my_tbl' LOOP EXECUTE FORMAT('SELECT id FROM my_tbl WHERE %I = 1', rec.column_name) INTO v_col_id; IF ( v_col_id IS NOT NULL) THEN RAISE NOTICE 'Column % for year %, with id of % matched.', rec.column_name, rec.year, v_col_id; END IF; END LOOP; END; $$; I used an anonymous plpgsql function for demonstration purposes. It can be written as a named function just as easily.

4
  • (I didn't say there wasn't a way. This can be done, but it ain't easy ;-)
    – joanolo
    Feb 11, 2017 at 0:24
  • 1
    My apologies, I didn't mean to imply that your answer was incorrect. I read the question as "I want to retrieve the matches where the column = 1", and thought of the answer in the way above. Your suggestions are perfectly valid.
    – bma
    Feb 11, 2017 at 0:27
  • No apologies are needed, and no offence taken. Just joking... DYNAMIC SQL is not for the faint-hearted, and not easy when learning how to use UDF. It's more complicated to debug than just plain pl/pgSQL, because the "compiler" can help you much less. And that's specially true when you're already in production. If I could avoid it by having a different table definition (or even a different table structure, with a 1:n extra table), I probably would.
    – joanolo
    Feb 11, 2017 at 0:37
  • What I want is to compare the value inside r1.selected-row if it is equal 1
    – Eyla
    Feb 11, 2017 at 1:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.