Thread

VIP: plpgsql - early embedded sql plan preparation

Pavel Stehule <pavel.stehule@gmail.com> — 2011-08-23T11:30:19Z

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