Hi,
this patch is based on discussions related to plpgsql2 project.
Currently we cannot to control plan cache from plpgsql directly. We can use dynamic SQL if we can enforce oneshot plan - but it means little bit less readable code (if we enforce dynamic SQL from performance reasons). It means so the code cannot be checked by plpgsql check too.
The plan cache subsystem allows some control by options CURSOR_OPT_GENERIC_PLAN and CURSOR_OPT_CUSTOM_PLAN. So we just a interface how to use these options from PLpgSQL. I used Ada language feature (used in PL/SQL too) - PRAGMA statement. It allows to set compiler directives. The syntax of PRAGMA statements allows to set a level where entered compiler directive should be applied. It can works on function level or block level.
Attached patch introduces PRAGMA plan_cache with options: DEFAULT, FORCE_CUSTOM_PLAN, FORCE_GENERIC_PLAN. Plan cache is partially used every time - the parser/analyzer result is cached every time.
Examples:
CREATE OR REPLACE FUNCTION foo(a int)
RETURNS int AS $$
DECLARE ..
BEGIN
DECLARE
/* block level (local scope) pragma */
PRAGMA plan_cache(FORCE_CUSTOM_PLAN);
BEGIN
SELECT /* slow query - dynamic sql is not necessary */
END;
END;
Benefits:
1. remove one case where dynamic sql is necessary now - security, static check
2. introduce PRAGMAs - possible usage: autonomous transactions, implicit namespaces settings (namespace for auto variables, namespace for function arguments).
Comments, notes?
Regards
Pavel