Postgresql – pl/pgsql: Dynamicly get a column name from a record

plpgsqlpostgresqlpostgresql-9.3

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;

Best Answer

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).

Related Question