title: Use variable in Postgres pgplsql procedure with CTE
body: <p>Update: I tried the dynamic SQL (<code>EXECUTE FORMAT...</code>) but was getting syntax errors and saw google/other stackoverflow posts saying a dynamic query with a CTE wasn't possible, unless I am doing something wrong still</p>
  <p>I'm trying to inject some variables into my pgplsql Procedure that has a CTE that I use. I googld all over but it was utterly worthless as most post were revolving around mysql/sql server or they were doing something out of the scope of what I'm trying to do. I have a simple pgplsql Procedure as such:</p>
  <pre><code>CREATE OR REPLACE PROCEDURE public.some_dumb_procedure(batch_size integer, tbl_nm text)
      LANGUAGE plpgsql
  AS
  $procedure$
  declare
      
  begin
              // CTE expression here
              WITH my_dumb_CTE_list AS (select co.col1, co.col2, col_val
                                  from tbl_nm co // I need to inject the table name here dynamically
                                  where exists(select 1
                                               from crappy_table c
                                               where c.id = co.customer_ref_id
                                                 and co.offer_expiry_date &lt; now() - interval '180 days')
                                    and not exists(select 1 from super_stupid_list ssl where tbl_nm = 'master' and ssl.id = co.id)
                                  limit batch_size
              )
  
              INSERT INTO my_dumb_table
              (select *
              from my_dumb_CTE_list);
  
              commit;
             
  end;
  $procedure$
  ;
  </code></pre>
  <p>So it would be called like</p>
  <pre><code>call some_dumb_procedure(50000, 10000, 'thetable');
  </code></pre>
  <p>As you can see,  I'm just trying to inject the <code>tbl_nm</code> procedure argument into the subquery inside the CTE. For some reasons, the <code>batch_size</code> variable works fine and is injected into the <code>limit</code> expression (see example above where I passed 50,000 as the 1st argument to function, but the <code>tbl_nm</code> absolutely never injects at all. I tried declaring it as a variable and doing a <code>SELECT... INTO</code> but nothing works.</p>
  <p>How the hell do you insert/inject variables into a pgplsql procdure? \</p>