patch: CHECK FUNCTION statement - Mailing list pgsql-hackers

From Pavel Stehule
Subject patch: CHECK FUNCTION statement
Date
Msg-id CAFj8pRDkkzSi611Eimp=AXj2HD46k-W46GDvW9MKAD2OgwoKag@mail.gmail.com
Whole thread Raw
Responses Re: review: CHECK FUNCTION statement
List pgsql-hackers
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

Attachment

pgsql-hackers by date:

Previous
From: Jeff Davis
Date:
Subject: Re: GiST for range types (was Re: Range Types - typo + NULL string constructor)
Next
From: Heikki Linnakangas
Date:
Subject: Re: Inserting heap tuples in bulk in COPY