Trying to dynamically create a procedure

Hi,

I have the need to dynamically create a procedure.  Here is a simple procedure:

create or replace procedure junk.test_proc()

LANGUAGE plpgsql 

AS $$

declare 

  v_cnt         integer := 0;

begin

  raise notice 'v_cnt is %', v_cnt;         

end $$;

That creates and runs fine.

Here I’m trying to create it inside PL/pgSQL block (yes there is nothing dynamic below but the real code will have parts of the procedure that needs to have code dynamically generated):

DO $$

BEGIN

EXECUTE 'create or replace procedure junk.test_proc() ' ||

  'LANGUAGE plpgsql  '                          ||

  'AS $$ '                            ||

  'declare  '                              ||

  '  v_cnt         integer := 0; '               ||

  'begin '                            ||

  '  raise notice 'v_cnt is %', v_cnt; '         ||        

  'end $$';

END;

$$

It throws this error:

ERROR:  syntax error at or near "$$

DO $$"

LINE 1: $$

        ^

dbtest=>

dbtest=> END;

WARNING:  there is no transaction in progress

COMMIT

dbtest=>

dbtest=> $$

I think the problem has to do with having AS $$ and END $$ with the 2 $’s.  I’m not sure if there is different syntax I can use outside the $$ or if there is something I should use in the PL/pgSQL to escape those $$ to get this to work.

Any help would be appreciated.

Thanks