Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1svE7U-00DKZE-HO for pgsql-general@arkaria.postgresql.org; Mon, 30 Sep 2024 10:59:13 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1svE7T-00GSoa-LL for pgsql-general@arkaria.postgresql.org; Mon, 30 Sep 2024 10:59:11 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1svE7T-00GSoK-6B for pgsql-general@lists.postgresql.org; Mon, 30 Sep 2024 10:59:11 +0000 Received: from mail-oo1-xc35.google.com ([2607:f8b0:4864:20::c35]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1svE7Q-001mLv-ET for pgsql-general@lists.postgresql.org; Mon, 30 Sep 2024 10:59:09 +0000 Received: by mail-oo1-xc35.google.com with SMTP id 006d021491bc7-5e1b5b617b8so2021512eaf.0 for ; Mon, 30 Sep 2024 03:59:08 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1727693947; x=1728298747; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=CDaqhpNlCg1IBlf/H0YOa6eubd9vAuFqzwMsdj1oJnA=; b=mPArVdWinK+tt1+vQWY7faXtu2syjfxhdyzqWynkXfGvKBui2IvhBZYiK5CStzpzRm 8qPIvau6s1isE/2G+2XPEhgWSvrUYxrSgd2rqXbgUi709DiaH1VRMD3ZXaLxRozsOMiN Fg3+E0sQH4YCb1PfANISI5MCyhrGPA+dD8Aoc7zS/uD/dcuquC2UOQfs7Kqklr+qB/YS S02ZFkLHNnVqwA5aeXvl8JuClj50mAybbzrI10q5F8OTEOzN0KWTk19ThS2wGqOK/8Jr Wf+l6a5TZyVWnTfEZr4J6Y6e0U71s/aBlrEVpiq/fT78SuefZMsGJwqIOa65rypmhVV3 xZ7w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1727693947; x=1728298747; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=CDaqhpNlCg1IBlf/H0YOa6eubd9vAuFqzwMsdj1oJnA=; b=WYyRK+RJGZ7BsmnMpIh/0TZxBOiKpcT7vvk7rL7ZjY9MUff47jUjSJVEaf2QBIF0oz eyErkUfUN+AVZKCbj8j7Xw9IyAZ+W0pqC5XGiqiqCgfM0NLfhs5pW9u5DxMyRUz19Qoy +Fi4v1kPqwSZm29Gls1KEGpcWGFrGkH1imnCA2iAYryt+IZDSDihV6sm0GJ9tZOQUkFY NZ6xxoCOt8WTOtXrwZAlMmT+3ciFM71wdkLCMx82x+SqGOF7nUzvfuKeRrKJ581RzXGz 5/WlmPuqekHvEECGSMnuO2pcAqGNKDEQIrngnkI/lQnk98A+MzM37POlYI1HTCEfnaxs 2N1g== X-Gm-Message-State: AOJu0YyikV/NMsQOWNAPgTjgFtRvFv/Ptb7xRKJOmicwVTl2eZg9NX85 BsyEwJ8lmepoMLPXc36qmuNItQXHP01oCMyyYlM1ajIZMBVCkDVz1Uk3UxL9wTP+P6/cnN8JyIn EIHUs4B/0x98ArrxsSgCSUN4Gg5c= X-Google-Smtp-Source: AGHT+IFaukxiJTMQaNEjOZ4p74OwX9OKks+VHl+hnczZNpGYkB3dhluY20FBtCV8q2syiHPmHkxLRBq9UDHDrcR3qS8= X-Received: by 2002:a05:6820:1c83:b0:5e1:c6ae:d93 with SMTP id 006d021491bc7-5e7714c6e56mr6588666eaf.2.1727693947552; Mon, 30 Sep 2024 03:59:07 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Nikhil Ingale Date: Mon, 30 Sep 2024 16:28:31 +0530 Message-ID: Subject: Re: Trouble in generating the plpgsql procedure code To: Pavel Stehule Cc: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000064b3d0623541884" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000064b3d0623541884 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Let me try, Thanks Pavel. On Mon, Sep 30, 2024 at 3:53=E2=80=AFPM Pavel Stehule wrote: > 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 > napsal: > >> 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 = in >> db. I want to generate the procedure for more than 100 tables and write = all >> the procedure code to a single file which can be used to execute to crea= te >> 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 i= d =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 >> > --000000000000064b3d0623541884 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Let me try, Thanks Pavel.

On Mon, Sep 30, 2024 at 3:53=E2= =80=AFPM Pavel Stehule <pavel= .stehule@gmail.com> wrote:
Hi

use for= mat function

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

(2024-09-30 12:21:29) postgres=3D# \sf foo_1
CREATE OR REP= LACE FUNCTION public.foo_1(a integer)
=C2=A0RETURNS integer
=C2=A0LAN= GUAGE plpgsql
AS $function$
begin
=C2=A0 return a + 1;
end;
= $function$

Regards

Pavel

po 30. 9. 2024 v=C2=A012:09 odes=C3=ADlatel Nikhil Ingale &= lt;niks.bgm@gmail.c= om> napsal:
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 be= cause I have many tables in db. I want to generate the procedure for more t= han 100 tables and write all the procedure code to a single file which can = be used to execute to create 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 s= ingle shot with the help of COPY to PROG

My proced= ure 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$\nDECLAR= E\n =C2=A0nCount INT;\nBEGIN\n =C2=A0 IF TG_OP IN ('INSERT') THEN\n= =C2=A0 =C2=A0 =C2=A0 =C2=A0 SELECT 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.i= d AND STATUS_FLAG =3D 9 ; \n =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0IF COALESCE(nCount,0) > 0 THEN\n =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 UPDATE test.employee=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.desc= ription\n =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ,s= tate_flag =3D 2\n =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 W= HERE id =3D :NEW.id ; \n =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0EL= SE\n\t =C2=A0 =C2=A0 =C2=A0 =C2=A0 INSERT 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 ('UPDATE') 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

The code should have been written in the n= ext line instead of \n.


drop procedure instead= oftriggers;
create or replace procedure insteadoftriggers( IN e_owner TE= XT, p_table_name TEXT DEFAULT NULL, emp_owner TEXT DEFAULT 'test')<= br>AS $$
DECLARE
=C2=A0 =C2=A0 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_ow= ner,owner) ;
=C2=A0 =C2=A0
l_cnt NUMERIC :=3D 0;
=C2=A0 =C2= =A0 tab TEXT :=3D E'\t';
=C2=A0 =C2=A0 l_col_str TEXT;
=C2=A0= =C2=A0 l_table_Name TEXT ;
=C2=A0 =C2=A0 cKeyCol TEXT DEFAULT ''= ; ;
=C2=A0 =C2=A0 cWhere TEXT DEFAULT '' ;
=C2=A0 =C2=A0 trig= ger_body TEXT ;
=C2=A0 =C2=A0 cSpace character varying(100) :=3D ' = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ';
BEGIN
=C2=A0 =C2=A0 FOR Tst= TableRec IN TstTableCursor
=C2=A0 =C2=A0 LOOP
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 l_table_name :=3D TstTableRec.TABLE_NAME ;
=C2=A0 =C2=A0 =C2=A0 = =C2=A0 trigger_body :=3D
'CREATE OR REPLACE FUNCTION prod.' || T= stTableRec.TABLE_NAME || '_IOT() RETURNS TRIGGER AS $BODY$' || CHR(= 10) ||
'DECLARE' || CHR(10) ||
' =C2=A0' || 'nCou= nt INT;' || chr(10) ||
'BEGIN' || chr(10) ||
' =C2=A0= ' || 'IF TG_OP IN ' || '(' ||'''''= INSERT'''''||') THEN' || chr(10) ||
' = =C2=A0 =C2=A0 =C2=A0 =C2=A0 SELECT count(1) INTO nCount FROM test.' || = TstTableRec.TABLE_NAME || chr(10) ||
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = GetTrigClause(p_tdaowner, lower(TstTableRec.TABLE_NAME), ':NEW.') |= | ' AND STATUS_FLAG =3D 9 ; ' || chr(10) ||

=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0' =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0&= #39;|| 'IF COALESCE(nCount,0) > 0 THEN' || chr(10) ||

Can= someone help me here?

Regards,
Nikhil Ingale
--000000000000064b3d0623541884--