# 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 )"

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;


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