Re: VIP: plpgsql - early embedded sql plan preparation

On Tue, Aug 23, 2011 at 6:30 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> Hello
>
> I am returning back to discus two months ago about usage of
> plpgsql_lint. I integrated this module to plpgsql's core. This feature
> is controlled via plpgsql GUC variable prepare_plans and via plpgsql
> option. It works with all plpgsql's regress tests without tests where
> dynamic sql or refcursors are used. Early plan preparation is disabled
> default so it should not to break current applications.
>
> This feature can live as contrib module too, but integration has one
> advantage - there is still a possibility to use other plpgsql
> extensions - mainly plpgsql debugger.
>
> I didn't work on documentation yet, so I there is small example:
>
> CREATE TYPE tp AS (a int, b int);
>
> CREATE OR REPLACE FUNCTION test()
> RETURNS int AS $$
> DECLARE v tp;
> BEGIN
> v := (10,20);
> IF false THEN
>  RAISE NOTICE '%', v.z;
> END IF;
> RETURN v.a;
> END;
> $$ LANGUAGE plpgsql;
>
> postgres=# SELECT test();
>  test
> ------
>   10
> (1 row)
>
> with enabled early planning it found a bug in not executed code
>
> CREATE OR REPLACE FUNCTION public.test()
>  RETURNS integer
>  LANGUAGE plpgsql
> AS $function$
> #prepare_plans on_start
> DECLARE v tp;
> BEGIN
> v := (10,20);
> IF false THEN
>  RAISE NOTICE '%', v.z;
> END IF;
> RETURN v.a;
> END;
> $function$
>
> postgres=# select test();
> ERROR:  record "v" has no field "z"
> LINE 1: SELECT v.z
>               ^
> QUERY:  SELECT v.z
> CONTEXT:  PL/pgSQL function "test" line 7 at RAISE
>
> you can set GUC
>
> postgres=# set plpgsql.prepare_plans to on_start;
> SET
>
> and you can overwrite this global setting with directive
> #prepare_plans on_demand
>
> CREATE OR REPLACE FUNCTION public.test()
>  RETURNS integer
>  LANGUAGE plpgsql
> AS $function$
> #prepare_plans on_demand
> DECLARE v tp;
> BEGIN
> v := (10,20);
> IF false THEN
>  RAISE NOTICE '%', v.z;
> END IF;
> RETURN v.a;
> END;
> $function$
>
> Regards
>
> Pavel Stehule

Is it possible to raise warnings at function creation time?

merlin