MIME-Version: 1.0
References: <CALXkTpxgWpu4RKGR+oMrXw4Q+q4h-3p+_LOzbAapDH01=52M-A@mail.gmail.com>
In-Reply-To: <CALXkTpxgWpu4RKGR+oMrXw4Q+q4h-3p+_LOzbAapDH01=52M-A@mail.gmail.com>
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Mon, 30 Sep 2024 12:22:38 +0200
Message-ID: <CAFj8pRD_4b1ByHNYWXTGP=h4Ww0P=GvN4gRL8DNdp2ncA3cz_g@mail.gmail.com>
Subject: Re: Trouble in generating the plpgsql procedure code
To: Nikhil Ingale <niks.bgm@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000b93b59062353973c"
Archived-At: <https://www.postgresql.org/message-id/CAFj8pRD_4b1ByHNYWXTGP%3Dh4Ww0P%3DGvN4gRL8DNdp2ncA3cz_g%40mail.gmail.com>
Precedence: bulk

--000000000000b93b59062353973c
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi

use format function

do $$
begin
  for i in 1..10
  loop
    execute format($_$
create or replace function %I(a int)
returns int as $__$
begin
  return a + %s;
end;
$__$ language plpgsql;
$_$, 'foo_' || i, i);
  end loop;
end;
$$;
DO

(2024-09-30 12:21:29) postgres=3D# \sf foo_1
CREATE OR REPLACE FUNCTION public.foo_1(a integer)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
begin
  return a + 1;
end;
$function$

Regards

Pavel

po 30. 9. 2024 v 12:09 odes=C3=ADlatel Nikhil Ingale <niks.bgm@gmail.com> n=
apsal:

> Guys,
> I have a requirement to generate the plpgsql procedure code itself by
> reading the postgresql metadata or system tables. I don't want to write a
> plpgsql procedure every time for every table because I have many tables i=
n
> db. I want to generate the procedure for more than 100 tables and write a=
ll
> the procedure code to a single file which can be used to execute to creat=
e
> the actual procedure.
>
> While I generate the procedure code I want the code to be added to the
> next line in a better readability format. I'm writing the generated code
> for all tables in single shot with the help of COPY to PROG
>
> My procedure code is mentioned below. But the generated code is having \n
> instead of adding the code to the next line.
>
>
> CREATE OR REPLACE FUNCTION emp_dts_iot () RETURNS TRIGGER AS
> $BODY$\nDECLARE\n  nCount INT;\nBEGIN\n   IF TG_OP IN ('INSERT') THEN\n
>     SELECT count(1) INTO nCount FROM employee \n                 WHERE id=
 =3D
> :NEW.id AND STATUS_FLAG =3D 9 ; \n              IF COALESCE(nCount,0) > 0
> THEN\n                 UPDATE test.employee  \n                    id =3D
> :NEW.\n                   ,description =3D :NEW.description\n
>   ,state_flag =3D 2\n                 WHERE id =3D :NEW.id ; \n
>  ELSE\n\t         INSERT INTO employee VALUES ( \n
> :NEW.id\n                   ,:NEW.description\n    ,1 ) ; \n
>  END IF;\n\n   ELSIF TG_OP IN ('UPDATE') THEN\n         SELECT count(1)
> INTO nCount FROM test.employee\n
>
> The code should have been written in the next line instead of \n.
>
>
> drop procedure insteadoftriggers;
> create or replace procedure insteadoftriggers( IN e_owner TEXT,
> p_table_name TEXT DEFAULT NULL, emp_owner TEXT DEFAULT 'test')
> AS $$
> DECLARE
>     TstTableCursor CURSOR IS SELECT table_name,test_table_name FROM
> app_tables WHERE TEST_TABLE_NAME IS NOT NULL AND table_name =3D
> COALESCE(p_table_name,table_name) AND owner=3D COALESCE(e_owner,owner) ;
>
> l_cnt NUMERIC :=3D 0;
>     tab TEXT :=3D E'\t';
>     l_col_str TEXT;
>     l_table_Name TEXT ;
>     cKeyCol TEXT DEFAULT '' ;
>     cWhere TEXT DEFAULT '' ;
>     trigger_body TEXT ;
>     cSpace character varying(100) :=3D '           ';
> BEGIN
>     FOR TstTableRec IN TstTableCursor
>     LOOP
>         l_table_name :=3D TstTableRec.TABLE_NAME ;
>         trigger_body :=3D
> 'CREATE OR REPLACE FUNCTION prod.' || TstTableRec.TABLE_NAME || '_IOT()
> RETURNS TRIGGER AS $BODY$' || CHR(10) ||
> 'DECLARE' || CHR(10) ||
> '  ' || 'nCount INT;' || chr(10) ||
> 'BEGIN' || chr(10) ||
> '   ' || 'IF TG_OP IN ' || '(' ||'''''INSERT'''''||') THEN' || chr(10) ||
> '         SELECT count(1) INTO nCount FROM test.' ||
> TstTableRec.TABLE_NAME || chr(10) ||
>           GetTrigClause(p_tdaowner, lower(TstTableRec.TABLE_NAME),
> ':NEW.') || ' AND STATUS_FLAG =3D 9 ; ' || chr(10) ||
>
>          '              '|| 'IF COALESCE(nCount,0) > 0 THEN' || chr(10) |=
|
>
> Can someone help me here?
>
> Regards,
> Nikhil Ingale
>

--000000000000b93b59062353973c
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi</div><div><br></div><div>use format function</div>=
<div><br></div><div><span style=3D"font-family:monospace">do $$<br>begin<br=
>=C2=A0 for i in 1..10<br>=C2=A0 loop<br>=C2=A0 =C2=A0 execute format($_$<b=
r>create or replace function %I(a int)<br>returns int as $__$<br>begin<br>=
=C2=A0 return a + %s;<br>end;<br>$__$ language plpgsql;<br>$_$, &#39;foo_&#=
39; || i, i);</span></div><div><span style=3D"font-family:monospace">=C2=A0=
 end loop;<br>end;<br>$$;<br>DO</span></div><div><span style=3D"font-family=
:monospace"><br></span></div><div><span style=3D"font-family:monospace">(20=
24-09-30 12:21:29) postgres=3D# \sf foo_1<br>CREATE OR REPLACE FUNCTION pub=
lic.foo_1(a integer)<br>=C2=A0RETURNS integer<br>=C2=A0LANGUAGE plpgsql<br>=
AS $function$<br>begin<br>=C2=A0 return a + 1;<br>end;<br>$function$</span>=
</div><div><br></div><div>Regards</div><div><br></div><div>Pavel<br></div><=
/div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">p=
o 30. 9. 2024 v=C2=A012:09 odes=C3=ADlatel Nikhil Ingale &lt;<a href=3D"mai=
lto:niks.bgm@gmail.com">niks.bgm@gmail.com</a>&gt; napsal:<br></div><blockq=
uote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1p=
x solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr">Guys,<div>I hav=
e a requirement to generate the plpgsql procedure code itself by reading th=
e postgresql metadata or system tables. I don&#39;t want to write a plpgsql=
 procedure every time for every table because I have many tables in db. I w=
ant to generate the procedure for more than 100 tables and write all the pr=
ocedure code to a single file which can be used to execute to create the ac=
tual procedure.<br><br>While I generate the procedure code I want the code =
to be added to the next line in a better readability format. I&#39;m writin=
g the generated code for all tables in single shot with the help of COPY to=
 PROG</div><div><br></div><div>My procedure code is mentioned below. But th=
e generated code is having \n instead of adding the code to the next line.<=
/div><div><br></div><div><br></div><div>CREATE OR REPLACE FUNCTION emp_dts_=
iot () RETURNS TRIGGER AS $BODY$\nDECLARE\n =C2=A0nCount INT;\nBEGIN\n =C2=
=A0 IF TG_OP IN (&#39;INSERT&#39;) THEN\n =C2=A0 =C2=A0 =C2=A0 =C2=A0 SELEC=
T count(1) INTO nCount FROM employee \n =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 WHERE id =3D :NEW.id AND STATUS_FLAG =3D 9 ; \n =C2=A0=
 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0IF COALESCE(nCount,0) &gt; 0 THEN=
\n =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 UPDATE test.empl=
oyee=C2=A0 \n=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=
 =C2=A0 id =3D :NEW.\n =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 ,description =3D :NEW.description\n =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ,state_flag =3D 2\n =C2=A0 =C2=A0 =C2=A0=
 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 WHERE id =3D :NEW.id ; \n =C2=A0 =C2=A0=
 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0ELSE\n\t =C2=A0 =C2=A0 =C2=A0 =C2=A0 INS=
ERT INTO employee VALUES ( \n =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 :NEW.id\n =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 ,:NEW.description\n=C2=A0 =C2=A0 ,1 ) ; \n =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0END IF;\n\n =C2=A0 ELSIF TG_OP IN (&#39;U=
PDATE&#39;) THEN\n =C2=A0 =C2=A0 =C2=A0 =C2=A0 SELECT count(1) INTO nCount =
FROM test.employee\n=C2=A0 =C2=A0=C2=A0<br></div><div><br></div><div>The co=
de should have been written in the next line instead of \n.</div><div><br><=
/div><div><br>drop procedure insteadoftriggers;<br>create or replace proced=
ure insteadoftriggers( IN e_owner TEXT, p_table_name TEXT DEFAULT NULL, emp=
_owner TEXT DEFAULT &#39;test&#39;)<br>AS $$<br>DECLARE<br>=C2=A0 =C2=A0 Ts=
tTableCursor CURSOR IS SELECT table_name,test_table_name FROM app_tables WH=
ERE TEST_TABLE_NAME IS NOT NULL AND table_name =3D COALESCE(p_table_name,ta=
ble_name) AND owner=3D COALESCE(e_owner,owner) ;<br>=C2=A0 =C2=A0</div><div=
>l_cnt NUMERIC :=3D 0;<br>=C2=A0 =C2=A0 tab TEXT :=3D E&#39;\t&#39;;<br>=C2=
=A0 =C2=A0 l_col_str TEXT;<br>=C2=A0 =C2=A0 l_table_Name TEXT ;<br>=C2=A0 =
=C2=A0 cKeyCol TEXT DEFAULT &#39;&#39; ;<br>=C2=A0 =C2=A0 cWhere TEXT DEFAU=
LT &#39;&#39; ;<br>=C2=A0 =C2=A0 trigger_body TEXT ;<br>=C2=A0 =C2=A0 cSpac=
e character varying(100) :=3D &#39; =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 &#39=
;;<br>BEGIN<br>=C2=A0 =C2=A0 FOR TstTableRec IN TstTableCursor<br>=C2=A0 =
=C2=A0 LOOP<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 l_table_name :=3D TstTableRec.TA=
BLE_NAME ;<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 trigger_body :=3D<br>&#39;CREATE =
OR REPLACE FUNCTION prod.&#39; || TstTableRec.TABLE_NAME || &#39;_IOT() RET=
URNS TRIGGER AS $BODY$&#39; || CHR(10) ||<br>&#39;DECLARE&#39; || CHR(10) |=
|<br>&#39; =C2=A0&#39; || &#39;nCount INT;&#39; || chr(10) ||<br>&#39;BEGIN=
&#39; || chr(10) ||<br>&#39; =C2=A0 &#39; || &#39;IF TG_OP IN &#39; || &#39=
;(&#39; ||&#39;&#39;&#39;&#39;&#39;INSERT&#39;&#39;&#39;&#39;&#39;||&#39;) =
THEN&#39; || chr(10) ||<br>&#39; =C2=A0 =C2=A0 =C2=A0 =C2=A0 SELECT count(1=
) INTO nCount FROM test.&#39; || TstTableRec.TABLE_NAME || chr(10) ||<br>=
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 GetTrigClause(p_tdaowner, lower(TstTable=
Rec.TABLE_NAME), &#39;:NEW.&#39;) || &#39; AND STATUS_FLAG =3D 9 ; &#39; ||=
 chr(10) ||<br><br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0&#39; =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0&#39;|| &#39;IF COALESCE(nCount,0) &gt; 0=
 THEN&#39; || chr(10) ||<br><br>Can someone help me here?</div><div><br></d=
iv><div>Regards,<br>Nikhil Ingale</div></div>
</blockquote></div>

--000000000000b93b59062353973c--