public inbox for [email protected]  

help / color / mirror / Atom feed

From: Dirschel, Steve <[email protected]>
To: [email protected] <[email protected]>
Subject: Trying to dynamically create a procedure
Date: Wed, 26 Mar 2025 20:27:29 +0000
Message-ID: <CH0PR03MB6034C83799B64677E3D4941AFAA62@CH0PR03MB6034.namprd03.prod.outlook.com> (raw)
In-Reply-To: <CALdEsqOM2q7bXt1Bo2S_41nHgSjR2_epzm=aHHFoHsQdzB3fqQ@mail.gmail.com>
References: <CALdEsqOM2q7bXt1Bo2S_41nHgSjR2_epzm=aHHFoHsQdzB3fqQ@mail.gmail.com>

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
This e-mail is for the sole use of the intended recipient and contains information that may be privileged and/or confidential. If you are not an intended recipient, please notify the sender by return e-mail and delete this e-mail and any attachments. Certain required legal entity disclosures can be accessed on our website: https://www.thomsonreuters.com/en/resources/disclosures.html

view thread (3+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Trying to dynamically create a procedure
  In-Reply-To: <CH0PR03MB6034C83799B64677E3D4941AFAA62@CH0PR03MB6034.namprd03.prod.outlook.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox