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
