Use variable in Postgres pgplsql procedure with CTE

Question

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? \

Does this answer your question? Postgres Dynamic Query Function — JGH
– JGH, Commented Oct 7, 2021 at 13:36
@JGH I was getting an exception for the EXECUTE FORMAT and I saw other stackoverflow posts saying a CTE with a dynamic EXECUTE query nested inside isn't possble? — PainIsAMaster
– PainIsAMaster, Commented Oct 7, 2021 at 16:10
just put the entire query - including the cte - inside the execute statement — JGH
– JGH, Commented Oct 7, 2021 at 17:40
@JGH exactly what I needed thank you! if you post what you wrote as answer, I will accept as best answer thx very much — PainIsAMaster
– PainIsAMaster, Commented Oct 8, 2021 at 0:59

JGH · Accepted Answer · 2021-10-08 11:51:45Z

2

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

answered Oct 8, 2021 at 11:51

JGH

18.2k5 gold badges40 silver badges57 bronze badges

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

Collectives™ on Stack Overflow

Use variable in Postgres pgplsql procedure with CTE

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related