Postgres accessing row values in a FOR loop

Question

I am novice to Postgres. Here is the structure and some sample data for my table job_defn:

CREATE TABLE job_defn (
        job_id INTEGER NOT NULL,
        job_name CHARACTER VARYING(255) NOT NULL,
        system CHARACTER VARYING(50) NOT NULL,
        frequency CHARACTER VARYING(10) NOT NULL,
        run_day_id INTEGER NOT NULL,
        run_day_of_month INTEGER NOT NULL,
        eff_start_date DATE NOT NULL,
        eff_end_date DATE NOT NULL
    );

Sample data:

---------------------------
job_id         frequency
---------------------------
1001           DAILY 
1002           WEEKLY
---------------------------

When running the following function, I was expecting the l_freq variable to contain frequency column value from the table. However, it is always printing NULL even though the table row contains a value:

CREATE OR REPLACE FUNCTION testing()
   RETURNS void AS $$
   DECLARE

       l_job_id INTEGER;
       l_job_defn_record job_defn%ROWTYPE;
       l_freq CHARACTER VARYING(10);
   BEGIN
       FOR l_job_defn_record IN 
               SELECT job_id, frequency FROM job_defn ORDER BY job_id ASC
       LOOP
               l_job_id := l_job_defn_record.job_id;
               l_freq := l_job_defn_record.frequency;
               raise info'job id: %,  frq: %', l_job_id, l_freq;
               raise info'row==>%', l_job_defn_record;

       END LOOP;
   END;
   $$ LANGUAGE plpgsql VOLATILE;

Log:

17:50:52  [CREATE - 0 row(s), 0.036 secs]  Command processed. No rows were affected
Code: 0 SQL State: 00000 --- job id: 10001,  frq: <NULL>
Code: 0 SQL State: 00000 --- row==>(10001,DAILY,,,,,,,,,,)
Code: 0 SQL State: 00000 --- job id: 10001,  frq: <NULL>
Code: 0 SQL State: 00000 --- row==>(10001,DAILY,,,,,,,,,,)
Code: 0 SQL State: 00000 --- job id: 10001,  frq: <NULL>
Code: 0 SQL State: 00000 --- row==>(10001,DAILY,,,,,,,,,,)
Code: 0 SQL State: 00000 --- job id: 10001,  frq: <NULL>
Code: 0 SQL State: 00000 --- row==>(10001,DAILY,,,,,,,,,,)
Code: 0 SQL State: 00000 --- job id: 10001,  frq: <NULL>
Code: 0 SQL State: 00000 --- row==>(10001,DAILY,,,,,,,,,,)
Code: 0 SQL State: 00000 --- job id: 10001,  frq: <NULL>
Code: 0 SQL State: 00000 --- row==>(10001,DAILY,,,,,,,,,,)
Code: 0 SQL State: 00000 --- job id: 10001,  frq: <NULL>
Code: 0 SQL State: 00000 --- row==>(10001,DAILY,,,,,,,,,,)
Code: 0 SQL State: 00000 --- job id: 10002,  frq: <NULL>
Code: 0 SQL State: 00000 --- row==>(10002,WEEKLY,,,,,,,,,,)
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.036/0.000 sec  [0 successful, 1 warnings, 0 errors]

The function seems ok, the problem may be with the type of columns. Edit the question and add the table definition (\d job_defn in psql) — klin
– klin, Commented Aug 6, 2018 at 22:16

Erwin Brandstetter · Accepted Answer · 2018-08-07 13:19:28Z

1

You declared the record variable l_job_defn_record as type job_defn%ROWTYPE. As you later clarified, the table job_defn actually consists of more than just the two columns you first disclosed. The assignment in the FOR loop only assigns the first two fields of the record variable:

...
   FOR l_job_defn_record IN 
      SELECT job_id, frequency FROM ...  -- wrong!
...

This way, the 2nd column from the SELECT query (job_defn.frequency) is assigned to the 2nd field of the record variable (l_job_defn_record.job_name) - obviously not as intended. It happens to work since frequency is also be type varchar. Trailing fields of the record are not assigned and default to NULL.

This would fix it:

...
   FOR l_job_defn_record IN 
      SELECT * FROM job_defn ORDER BY job_id
...

Often, SELECT * is suspicious coding, but in this case it is the proper way since l_job_defn_record has the same row type as the table job_defn by definition.

edited Aug 7, 2018 at 13:19

answered Aug 7, 2018 at 1:28

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Raghavendra Chary Desoju Over a year ago

Thank you very much for the explanation, suggested change fixed it :)

klin · Accepted Answer · 2018-08-07 08:03:31Z

1

Erwin clearly explained the cause of the error in his answer. I would suggest using the RECORD type for the variable, instead of ROWTYPE.

    l_job_defn_record RECORD;

The actual structure of such a variable is defined dynamically when a value is assigned to it. Therefore there is no possibility of incompatibility between the variable and the assigned value.

answered Aug 7, 2018 at 8:03

klin

123k15 gold badges240 silver badges262 bronze badges

1 Comment

Raghavendra Chary Desoju Over a year ago

Thank you very much, suggested change fixed it :)

Collectives™ on Stack Overflow

Postgres accessing row values in a FOR loop

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related