Re: Trouble in generating the plpgsql procedure code

Let me try, Thanks Pavel.

On Mon, Sep 30, 2024 at 3:53 PM Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
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 <niks(dot)bgm(at)gmail(dot)com>
> 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
>>
>