Thread: VIP: plpgsql - early embedded sql plan preparation

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

On Tue, Aug 23, 2011 at 6:30 AM, Pavel Stehule <pavel.stehule@gmail.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

>
> Is it possible to raise warnings at function creation time?
>

when this code will be in core, then it is possible without one
significant exceptions - triggers :( I am not able to specify a target
table - and real type for NEW and OLD records.

Regards

Pavel

> merlin
>