Hello all,
I am sending updated patch, that implements a CHECK FUNCTION and CHECK
TRIGGER statements.
This patch is significantly redesigned to previous version (PL/pgSQL
part) - it is more readable, more accurate. There are new regress
tests.
Please, can some English native speaker fix doc and comments?
Usage is very simply
postgres=# \d t1
Table "public.t1"
Column | Type | Modifiers
--------+---------+-----------
a | integer |
b | integer |
Triggers:
t1_f1 BEFORE INSERT ON t1 FOR EACH ROW EXECUTE PROCEDURE f1_trg()
postgres=# \sf+ f1_trg
CREATE OR REPLACE FUNCTION public.f1_trg()
RETURNS trigger
LANGUAGE plpgsql
1 AS $function$
2 begin
3 new.a := new.a + 10;
4 new.b := new.b + 10;
5 new.c := 30;
6 return new;
7 end;
8 $function$
postgres=# check trigger t1_f1 on t1;
NOTICE: checking function "f1_trg()"
ERROR: record "new" has no field "c"
CONTEXT: checking of PL/pgSQL function "f1_trg" line 5 at assignment
Checker handler should be called explicitly
postgres=# select plpgsql_checker('f1'::regproc, 0);
ERROR: column "c" of relation "t1" does not exist
LINE 1: update t1 set c = 30
^
QUERY: update t1 set c = 30
CONTEXT: checking of PL/pgSQL function "f1" line 4 at SQL statement
or (check or plpgsql custom functions)
DO $$
DECLARE r regprocedure;
BEGIN
FOR r IN SELECT p.oid
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON
n.oid = p.pronamespace
LEFT JOIN pg_catalog.pg_language l ON
l.oid = p.prolang
WHERE l.lanname = 'plpgsql'
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND p.prorettype <>
'pg_catalog.trigger'::pg_catalog.regtype
LOOP
RAISE NOTICE 'check %', r;
PERFORM plpgsql_checker(r, 0);
END LOOP;
END;
$$;
ToDo:
CHECK FUNCTION search function according to function signature - it
should be changes for using a actual types - it can be solution for
polymorphic types and useful tool for work with overloaded functions -
when is not clean, that function was executed.
check function foo(int, int);
NOTICE: checking function foo(variadic anyarray)
...
and maybe some support for named parameters
check function foo(name text, surname text);
NOTICE: checking function foo(text, text, text, text)
...
Regards
Pavel Stehule
