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 pLEFT 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 THENparamtext = 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 pLEFT 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
1