public inbox for [email protected]
help / color / mirror / Atom feedTrouble in generating the plpgsql procedure code
3+ messages / 2 participants
[nested] [flat]
* Trouble in generating the plpgsql procedure code
@ 2024-09-30 10:08 Nikhil Ingale <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Nikhil Ingale @ 2024-09-30 10:08 UTC (permalink / raw)
To: [email protected]
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 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 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 =
:NEW.id AND STATUS_FLAG = 9 ; \n IF COALESCE(nCount,0) > 0
THEN\n UPDATE test.employee \n id =
:NEW.\n ,description = :NEW.description\n
,state_flag = 2\n WHERE id = :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 =
COALESCE(p_table_name,table_name) AND owner= COALESCE(e_owner,owner) ;
l_cnt NUMERIC := 0;
tab TEXT := E'\t';
l_col_str TEXT;
l_table_Name TEXT ;
cKeyCol TEXT DEFAULT '' ;
cWhere TEXT DEFAULT '' ;
trigger_body TEXT ;
cSpace character varying(100) := ' ';
BEGIN
FOR TstTableRec IN TstTableCursor
LOOP
l_table_name := TstTableRec.TABLE_NAME ;
trigger_body :=
'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 = 9 ; ' || chr(10) ||
' '|| 'IF COALESCE(nCount,0) > 0 THEN' || chr(10) ||
Can someone help me here?
Regards,
Nikhil Ingale
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Trouble in generating the plpgsql procedure code
@ 2024-09-30 10:22 Pavel Stehule <[email protected]>
parent: Nikhil Ingale <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Pavel Stehule @ 2024-09-30 10:22 UTC (permalink / raw)
To: Nikhil Ingale <[email protected]>; +Cc: [email protected]
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=# \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ílatel Nikhil Ingale <[email protected]> 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 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 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 =
> :NEW.id AND STATUS_FLAG = 9 ; \n IF COALESCE(nCount,0) > 0
> THEN\n UPDATE test.employee \n id =
> :NEW.\n ,description = :NEW.description\n
> ,state_flag = 2\n WHERE id = :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 =
> COALESCE(p_table_name,table_name) AND owner= COALESCE(e_owner,owner) ;
>
> l_cnt NUMERIC := 0;
> tab TEXT := E'\t';
> l_col_str TEXT;
> l_table_Name TEXT ;
> cKeyCol TEXT DEFAULT '' ;
> cWhere TEXT DEFAULT '' ;
> trigger_body TEXT ;
> cSpace character varying(100) := ' ';
> BEGIN
> FOR TstTableRec IN TstTableCursor
> LOOP
> l_table_name := TstTableRec.TABLE_NAME ;
> trigger_body :=
> '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 = 9 ; ' || chr(10) ||
>
> ' '|| 'IF COALESCE(nCount,0) > 0 THEN' || chr(10) ||
>
> Can someone help me here?
>
> Regards,
> Nikhil Ingale
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Trouble in generating the plpgsql procedure code
@ 2024-09-30 10:58 Nikhil Ingale <[email protected]>
parent: Pavel Stehule <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Nikhil Ingale @ 2024-09-30 10:58 UTC (permalink / raw)
To: Pavel Stehule <[email protected]>; +Cc: [email protected]
Let me try, Thanks Pavel.
On Mon, Sep 30, 2024 at 3:53 PM Pavel Stehule <[email protected]>
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=# \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ílatel Nikhil Ingale <[email protected]>
> 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 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 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 =
>> :NEW.id AND STATUS_FLAG = 9 ; \n IF COALESCE(nCount,0) > 0
>> THEN\n UPDATE test.employee \n id =
>> :NEW.\n ,description = :NEW.description\n
>> ,state_flag = 2\n WHERE id = :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 =
>> COALESCE(p_table_name,table_name) AND owner= COALESCE(e_owner,owner) ;
>>
>> l_cnt NUMERIC := 0;
>> tab TEXT := E'\t';
>> l_col_str TEXT;
>> l_table_Name TEXT ;
>> cKeyCol TEXT DEFAULT '' ;
>> cWhere TEXT DEFAULT '' ;
>> trigger_body TEXT ;
>> cSpace character varying(100) := ' ';
>> BEGIN
>> FOR TstTableRec IN TstTableCursor
>> LOOP
>> l_table_name := TstTableRec.TABLE_NAME ;
>> trigger_body :=
>> '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 = 9 ; ' || chr(10) ||
>>
>> ' '|| 'IF COALESCE(nCount,0) > 0 THEN' || chr(10) ||
>>
>> Can someone help me here?
>>
>> Regards,
>> Nikhil Ingale
>>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-09-30 10:58 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-09-30 10:08 Trouble in generating the plpgsql procedure code Nikhil Ingale <[email protected]>
2024-09-30 10:22 ` Pavel Stehule <[email protected]>
2024-09-30 10:58 ` Nikhil Ingale <[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