PostgreSQL - Generar Comentarios Automaticos por Esquemas

   
Vista:
Imágen de perfil de outrera

Generar Comentarios Automaticos por Esquemas

Publicado por outrera (35 intervenciones) el 01/04/2014 15:25:35
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
CREATE OR REPLACE FUNCTION utils.psp_parametros_function(p_schemaname character varying, p_functionname character varying)
  RETURNS text AS
$BODY$
DECLARE
    funcrow RECORD;
    numfunctions smallint := 0;
    numparameters int;
    i int;
    paramtext text;
BEGIN
FOR funcrow IN
  	SELECT n.nspname as "esquema",
	p.proname as "funcion",
	proargtypes
	FROM pg_catalog.pg_proc p
	LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
	WHERE n.nspname = p_schemaname and proname = p_functionname
 
LOOP
   --SELECT proargtypes FROM pg_proc WHERE proname = p_functionname
 
    --for some reason array_upper is off by one for the oidvector type, hence the +1
    numparameters = array_upper(funcrow.proargtypes, 1) + 1;
 
    i = 0;
    paramtext = '';
 
    LOOP
        IF i < numparameters THEN
            IF i > 0 THEN
                paramtext = paramtext || ', ';
            END IF;
            paramtext = paramtext || (SELECT typname FROM pg_type WHERE oid = funcrow.proargtypes[i]);
            i = i + 1;
        ELSE
            EXIT;
        END IF;
    END LOOP;
 
   -- EXECUTE 'DROP FUNCTION ' || p_functionname || '(' || paramtext || ');';
    numfunctions = numfunctions + 1;
 
END LOOP;
 
RETURN '( ' || paramtext || ' )';
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION utils.psp_parametros_function(character varying, character varying)
  OWNER TO postgres;
 
 
 
CREATE OR REPLACE FUNCTION utils.psp_comentar_funciones(p_schemaname character varying)
  RETURNS void AS
$BODY$
DECLARE
	oRecord  record;
	oCommentBody text;
	oAutor character varying;
	oTipo character varying;
	oContador integer;
	oCantAutor integer;
	oFechaActual date;
 
 
BEGIN
 
	oCommentBody='';
 
	FOR oRecord IN
		SELECT n.nspname as "esquema",
		p.proname as "funcion",
		pg_catalog.pg_get_function_result(p.oid) as "resultado",
		pg_catalog.pg_get_function_arguments(p.oid) as "parametros",
		obj_description(p.oid) as "comentario"
		FROM pg_catalog.pg_proc p
		LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
		WHERE n.nspname = p_schemaname and obj_description(p.oid) is null
		ORDER BY 1, 2, 4
	LOOP
 
 
 
	oTipo := CASE substring(oRecord.funcion,1,3)
		WHEN 'psi' THEN ' Insert (Insertar)'
		WHEN 'psu' THEN ' Update (Actualizar) '
		WHEN 'psd' THEN ' Delete (Eliminar) '
		WHEN 'pss' THEN ' Select (Consultar) '
		ELSE ' procesos '
 
       END;
 
 
	oCommentBody := ' COMMENT ON FUNCTION ' || trim(p_schemaname) || '.' ||trim(oRecord.funcion::character varying) ||  utils.psp_parametros_function( p_schemaname,oRecord.funcion::character varying) || ' IS ';
 
 
 
	oCommentBody :=  oCommentBody || CHR(39) ||'Procedimiento para '  || oTipo ||CHR(13) || CHR(10);
	oCommentBody :=  oCommentBody ||' -- ===================================================' ||CHR(13) || CHR(10);
	oCommentBody :=  oCommentBody ||'-- Titulo: ' ||CHR(13) || CHR(10);
	oCommentBody :=  oCommentBody ||'-- Descripción: Procesos de ' ||CHR(13) || CHR(10);
	oCommentBody :=  oCommentBody ||'-- Autor: ' ||CHR(13) || CHR(10);
	oCommentBody :=  oCommentBody ||'-- Creado: ' ||CHR(13) || CHR(10);
	oCommentBody :=  oCommentBody ||'-- Modificado: ' ||CHR(13) || CHR(10);
	oCommentBody :=  oCommentBody ||'--					' ||CHR(13) || CHR(10);
	oCommentBody :=  oCommentBody ||'-- ==================================================='||CHR(39) || ';';
 
 
	EXECUTE ''||oCommentBody ||'' ;
 
	--RAISE NOTICE 'comentando %', oCommentBody;
 
	END LOOP;
 
 
 
 
 
 
 
 
 
	return  ;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION utils.psp_comentar_funciones(character varying)
  OWNER TO postgres;
Valora esta pregunta
Me gusta: Está pregunta es útil y esta claraNo me gusta: Está pregunta no esta clara o no es útil
1
Responder