public inbox for [email protected]  

help / color / mirror / Atom feed

Trying to dynamically create a procedure
3+ messages / 3 participants
[nested] [flat]

* Trying to dynamically create a procedure
@ 2025-03-26 20:27 Dirschel, Steve <[email protected]>
  2025-03-26 20:30 ` Re: Trying to dynamically create a procedure Christophe Pettus <[email protected]>
  2025-03-28 07:12 ` Re: Trying to dynamically create a procedure Laurenz Albe <[email protected]>
  0 siblings, 2 replies; 3+ messages in thread

From: Dirschel, Steve @ 2025-03-26 20:27 UTC (permalink / raw)
  To: [email protected] <[email protected]>

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


^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Trying to dynamically create a procedure
  2025-03-26 20:27 Trying to dynamically create a procedure Dirschel, Steve <[email protected]>
@ 2025-03-26 20:30 ` Christophe Pettus <[email protected]>
  1 sibling, 0 replies; 3+ messages in thread

From: Christophe Pettus @ 2025-03-26 20:30 UTC (permalink / raw)
  To: Dirschel, Steve <[email protected]>; +Cc: [email protected] <[email protected]>



> On Mar 26, 2025, at 13:27, Dirschel, Steve <[email protected]> wrote:
> 
>   I think the problem has to do with having AS $$ and END $$ with the 2 $’s.

PostgreSQL's multiline-string syntax is quite flexible.  You can do things like:

DO $doblock$
 ...
$doblock$
LANGUAGE plpgsql;

I tend to put the name of the function between the $s to avoid nesting problems.





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Trying to dynamically create a procedure
  2025-03-26 20:27 Trying to dynamically create a procedure Dirschel, Steve <[email protected]>
@ 2025-03-28 07:12 ` Laurenz Albe <[email protected]>
  1 sibling, 0 replies; 3+ messages in thread

From: Laurenz Albe @ 2025-03-28 07:12 UTC (permalink / raw)
  To: Dirschel, Steve <[email protected]>; [email protected] <[email protected]>

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


^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2025-03-28 07:12 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-03-26 20:27 Trying to dynamically create a procedure Dirschel, Steve <[email protected]>
2025-03-26 20:30 ` Christophe Pettus <[email protected]>
2025-03-28 07:12 ` Laurenz Albe <[email protected]>

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