Re: Trying to dynamically create a procedure

On Wed, 2025-03-26 at 20:27 +0000, Dirschel, Steve wrote:
> 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

If you nest dollar quotes, you need to use different strings between the dollars:

DO $do$
BEGIN
   EXECUTE 'CREATE PROCEDURE ... AS $fun$ ... $fun$';
END;
$do$;

Yours,
Laurenz Albe