Use variable in Postgres pgplsql procedure with CTE-CodePudding

Update: I tried the dynamic SQL (EXECUTE FORMAT...) 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

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:

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 < 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$
;

So it would be called like

call some_dumb_procedure(50000, 10000, 'thetable');

As you can see, I'm just trying to inject the tbl_nm procedure argument into the subquery inside the CTE. For some reasons, the batch_size variable works fine and is injected into the limit expression (see example above where I passed 50,000 as the 1st argument to function, but the tbl_nm absolutely never injects at all. I tried declaring it as a variable and doing a SELECT... INTO but nothing works.

How the hell do you insert/inject variables into a pgplsql procdure? \

CodePudding user response：

The entire query - including the CTE - should be executed dynamically

EXECUTE format(
    'WITH myCTE as (select * from %I)
    INSERT INTO aTable SELECT * FROM myCTE',
    param_table_name
  );