title: What Identifier in "EXECUTE FORMAT" with "FOR ... IN" loop "record" variable/iterator that uses dot/period "." for Column name in PLPGSQL Procedure?
body: <p>I am using a basic plpgsql <strong>EXECUTE... FORMAT</strong> dynamic script with a <strong>FOR...IN</strong> loop. The problem is, the <strong>LOOP</strong> has a variable/pointer (like any FOR LOOP in any language) that iterates through the query result set of the <strong>SELECT...</strong> query in the <strong>EXECUTE</strong>. I need to make the column name part after the period/dot in <code>temprecord.column_name</code> dynamic, and thus use an Identifier (i.e. <code>%I, %s, %L</code>) for it, As you know, the way to get data (In this column is an <code>ALTER TABLE</code> sql statement) out of this variable/pointer is to use the &quot;dot notation&quot;, that is, i.e.</p>
  <pre><code>/* Output employee names from column &quot;names&quot; in Employee table */
  
  FOR temprecord IN
  EXECUTE format('SELECT * 
                  FROM %I t', ''Employee'')
  LOOP
     EXECUTE temprecord.names; -- THIS WORKS FINE WHEN I HARDCODE IT. I CAN'T SEEM TO MAKE IT DYNAMIC
  END LOOP;
  </code></pre>
  <p>So the above works fine when I hardcord <code>temprecord.names</code>. The problem is I want the column name dynamic, so if different callers/methods call my function, I can select different columns through the <strong>temprecord</strong> iterator, and EXECUTE this data.</p>
  <p>I tried many times what I have below and the best response I have gotten so far is that the sql query that I have in the column (as I stated above) <em><strong>executed</strong></em>, however, it showed an syntax error and truncation error, but I clearly noticed it was returned the all three columns of my table and concatenating the columns together. But I know the real problem is the variable <code>temprecord</code> not picking up on the dot notation that specifies column name i.e. <code>temprecord.column_name_here</code>, that's why it returns all columns and throws truncation here. As I stated above, it works when hardcoded.</p>
  <p>i.e.</p>
  <pre><code>/* Using the $$ for string formatting here */
  
  CREATE OR REPLACE PROCEDURE my_proc(drop_or_add text)
      LANGUAGE plpgsql
  AS
  $procedure$
  DECLARE
    temprecord record;
    col_nm text;
  BEGIN
    col_nm := concat_ws('_','sql',drop_or_add); -- SHOULD CONCATENATE TO COLUMN NAME, I.E. sql_drop OR sql_add WHICH HOLDS SQL &quot;ALTER TABLE...&quot; QUERY
    
  FOR temprecord IN
  EXECUTE format($f$ SELECT t.col1, t.col2, t.col3%s 
                     FROM some_tbl t
                     WHERE t.col4 = %L, drop_or_add, 'blue')
  LOOP
    EXECUTE format($f$ %I.%s $f$, &quot;temprecord&quot;, col_nm);
  END LOOP;
  
  END
  $procedure$;
  </code></pre>
  <p>Hopefully none of this syntax is confusing, nor my logic. Again, I am simply looping with the <code>temprecord</code> iterator variable and trying to access some columns of my database table, that holds some SQL statements and needs to be dynamic based on the argument passed to my Procedure. So, potentially <code>EXECUTE temprecord.sql_drop</code> or <code>EXECUTE temprecord.sql_add</code> can execute.</p>
  <p>I always used the <code>%s</code> in my other plpgsql scripts when I access a column of table using dot notation i.e. <code>tbl1.id = tbl2.%</code> and it's always worked fine. I have tried basically every combination possible, but I'll list a few here, i.e.</p>
  <pre><code>EXECUTE format($f$ %I.%s $f$, &quot;temprecord&quot;, col_nm);
  EXECUTE format($f$ %I%s $f$, &quot;temprecord&quot;, '.sql_drop');
  EXECUTE format($f$ %I.%s $f$, temprecord, col_nm);
  EXECUTE format($f$ %I.%I $f$, &quot;temprecord&quot;, col_nm);
  EXECUTE format($f$ %I.%I $f$, &quot;temprecord&quot;, &quot;col_nm&quot;);
  .
  ..
  ...
  </code></pre>