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
132
133
134
135
136
137
138
139
140
141
142
143
144
|
CREATE FUNCTION plpgsql_check_custom(funcoid oid DEFAULT NULL::oid, warns boolean DEFAULT false, stop_fatal boolean DEFAULT true, msg_like text DEFAULT '%'::text, proname_regex text DEFAULT '^(.*)$'::text, schema_regex text DEFAULT '^(.*)$'::text)
RETURNS TABLE(ts character, check_msg text)
LANGUAGE plpgsql
AS
$$
DECLARE
-- This cursor drives the process (plpgsql_check_function()) does all the work!
-- The filters are simple enough to filter down the messages, or the procedure name, and to control the INTENSITY of the LINTING
-- You get the source... Make it your own! I wanted something I could use flexibly
msgs CURSOR (func oid, warnings boolean , fatals boolean) FOR SELECT *
FROM (SELECT p.oid,
p.prokind,
n.nspname || '.' || p.proname || '()' AS proname,
public.plpgsql_check_function(
funcoid => p.oid::regproc
, fatal_errors := fatals
, extra_warnings := warnings
, performance_warnings := warnings /* set these 3 to false for initial pass */
, all_warnings := warnings)::text AS err
FROM pg_catalog.pg_namespace n
JOIN pg_catalog.pg_proc p ON pronamespace = n.oid
JOIN pg_catalog.pg_language l ON p.prolang = l.oid
WHERE l.lanname = 'plpgsql'
AND p.prorettype <> 2279 /* not a trigger */
AND n.nspname <> 'public'
AND p.prokind IN ('p', 'f') -- Only function and procedures
AND p.oid = COALESCE(func, p.oid)
AND p.proname OPERATOR (pg_catalog.~) proname_regex
AND n.nspname OPERATOR (pg_catalog.~) schema_regex) q1
WHERE q1.err LIKE msg_like;
thisproc text := ''; -- Used so we only waste ONE line outputting what function we are working on, as opposed to a COLUMN
errmsg text; -- The error message: "error:42883:42:assignment:function schem.function(integer, unknown, unknown, unknown, unknown, unknown, unknown) does not exist"
a_txt text[]; -- Used to pars errmsg
fdecl text; -- Declaration after parsing
fname text; -- Before the parens
foid oid; -- Function OID to lookup the named parameters
parm1 text; -- between the parens
pos INT; -- Simple position of ( for parsing
a_p1 text[]; -- Array of Params from the users code
has_in boolean; -- is IN/OUT present in any parameters
names text; -- Function Signature with Parameter Names
a_name text[]; -- string_to_aarray( names, ', ' ) -- [IN/OUT/INOUT] FLDNAME type [DEFAULT ...]
a_pname text[]; -- Name ONLY of the field name
n_off INT; -- Offset into the array for stuff
str_out text; -- Messages to send out, with Chr(10) separating them!
flow_def text; -- Should we default to IN all the time for flow
flow text; -- IN/INOUT/OUT + DEF
BEGIN
ts := TO_CHAR(NOW(), 'HH24:MI:SS'); -- this is constant (Maybe a waste of the column, but forces a TABLE() return in case you want to add more columns, etc!
FOR msg IN msgs(funcoid, warns, stop_fatal)
LOOP
str_out := ''; -- Start Fresh, and add strings as we go, for one final RETURN NEXT!
IF thisproc <> msg.proname THEN -- Return a header!
IF thisproc <> '' THEN
check_msg := '';
RETURN NEXT; -- Blank line between different functions!
END IF;
thisproc := msg.proname;
check_msg := CONCAT('===========> PROCESSING: ', thisproc); -- While REDUNDANT on 42883 Errors, it separates ALL functions from each other!
RETURN NEXT;
END IF;
check_msg := msg.err;
RETURN NEXT;
errmsg := msg.err;
IF errmsg LIKE 'error:42883:%' THEN
-- SELECT '{}','{}','{}','{}','{}','{}' INTO a_txt, a_p1, a_p2, a_name, a_pname, a_flow; -- Produces plpgsql_check() warnings!
a_txt := '{}';
a_p1 := '{}';
a_name := '{}';
a_pname := '{}';
str_out := '#### ';
-- RETURN NEXT;
IF RIGHT(errmsg, 14) = 'does not exist' THEN errmsg := LEFT(errmsg, -15); END IF;
a_txt := STRING_TO_ARRAY(errmsg, ':');
IF CARDINALITY(a_txt) <> 5 THEN
check_msg := str_out || chr(10) || '######## ==> details unavailable, parsing error <=== #########'::TEXT;
RETURN NEXT;
CONTINUE;
END IF;
fdecl := a_txt[5];
pos := POSITION('(' IN fdecl);
IF pos = 0 THEN
check_msg := str_out || chr(10) || '######## ==> details unavailable, parsing error(2) <=== #########'::TEXT;
RETURN NEXT;
CONTINUE;
END IF;
fname := LEFT(fdecl, pos - 1); -- exclude the paren
fname := SUBSTR(fname, POSITION(' ' IN fname) + 1);
parm1 := TRIM(SUBSTR(fdecl, pos, POSITION(')' IN fdecl) - pos + 1));
-- RETURN NEXT (ts , concat('#### ', fdecl )); -- Really Just Debug!
BEGIN
foid := TO_REGPROC(fname)::oid; -- This function will not throw an exception, just returns NULL
-- REPLACES the error block
IF foid IS NULL THEN
check_msg := '#### Either No Such function or No Paramters!';
RETURN NEXT;
CONTINUE;
END IF;
str_out := str_out || chr(10) || CONCAT('#### ', 'Error in: ', thisproc, ' at Line: ', a_txt[3], ' PARAMETER TYPING ISSUE?') || chr(10) || '#### ';
a_p1 := STRING_TO_ARRAY(SUBSTRING(parm1, 2, LENGTH(parm1) - 2), ', '); -- These are just the types
SELECT (POSITION('IN ' IN args) + POSITION('OUT ' IN args) )> 0 as tagged, args into has_in, names FROM
(SELECT pg_catalog.PG_GET_FUNCTION_ARGUMENTS(foid) as args) t;
a_name := STRING_TO_ARRAY(names, ', '); -- Separate these out! has_in is set for us
/* We have an array of [INOUT] varname type [DEFAULT xxx] | And an array of the users param types param1 We will OUTPUT:
Parameter Name [35], INOUT+DEF[10], P1_TYPE[15], OUR_TYPE \n */
str_out := CONCAT(str_out, chr(10), '#### ', rpad('Param Name',20), ' ', rpad('Flow/DEF',10), rpad('(your code)',15), rpad('Definition',15) );
str_out := CONCAT(str_out, chr(10), '#### ', rpad('==========',20), ' ', rpad('========',10), rpad('===========',15), rpad('==========',15) );
IF has_in THEN
n_off := 1;
flow_def := NULL;
ELSE
n_off := 0;
flow_def := 'IN '; -- We have to force the display of IN, just for consistency.
END IF;
FOR x IN 1 .. CARDINALITY(a_name)
LOOP
a_pname := STRING_TO_ARRAY(a_name[x], ' '); -- Parse into an array
-- RAISE NOTICE 'a_pname 1 %, 2 %, 3 %', a_pname[1], a_pname[2], a_pname[3];
flow := COALESCE(flow_def, a_pname[1]) || CASE WHEN POSITION('DEFAULT' IN a_name[x])=0 THEN '' ELSE ' DEF' END;
str_out := CONCAT(str_out, chr(10), '#### ', rpad(a_pname[1+n_off],20), ' ',rpad(flow,10), rpad(coalesce(a_p1[x],'???'),15), rpad(a_pname[2+n_off],15) );
END LOOP;
EXCEPTION
WHEN OTHERS THEN
str_out := str_out || chr(10) || CONCAT('==== ERROR: ', SQLERRM, ' Unexpected Exception!');
END;
str_out := str_out || chr(10) || '#### ';
ELSE
CONTINUE; -- Nothing to do, not our message
END IF;
check_msg := str_out;
RETURN NEXT;
END LOOP;
IF thisproc='' AND funcoid is not null THEN
check_msg := 'No Messages Returned for: ' || funcoid::regproc;
RETURN NEXT;
END IF;
RETURN;
END
$$;
|
