Re: Trouble in generating the plpgsql procedure code

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
>