> > some examples based on Ada doc > > FUNCTION xxx RETURN int AS > PRAGMA yyy -- pragma has function scope > BEGIN > > FUNCTION xxx RETURN int AS > BEGIN > DECLARE > PRAGMA yyy -- pragma has block scope
ok, sub-block makes sense over statement level IMO.
I am sending proof concept (parser only implementation) - it allows to control query plan usage on function and on block level
Examples
CREATE OR REPLACE FUNCTION fx()
RETURNS int AS $$
PRAGMA use_query_plan_cache(off); -- disable query plan cache on function level
DECLARE r record;
BEGIN
FOR r IN SELECT ... -- some complex query, where we prefer on one shot plan
LOOP
DECLARE
PRAGMA use_query_plan_cache(on); -- enable query plan cache for block
BEGIN
... statements inside cycle reuses query plan
END;
END LOOP;
END;
$$ LANGUAGE plpgsql;
or
BEGIN
...
DECLARE
PRAGMA use_query_plan_cache(off);
BEGIN
-- these queries has fresh plan only
SELECT ...
SELECT ...
END; -- end of PRAGMA scope
...
-- usual behave
END;
The behave is static - controlled on compile time only - the controlled feature can be enabled/disabled. The impact on runtime is zero
* the syntax is verbose - readable - I prefer strong clean signal for readers so something internals is different
* consistent with Ada, PL/SQL
* remove one reason for dynamic SQL
* allows to mix queries with without query plan cache - interesting for patter FOR IN slow query LOOP fast query; END LOOP;
* there is small risk of compatibility break - if somebody use variables named PRAGMA, because new reserved keyword is necessary - fails on syntax error - so it is easy identified.
* this syntax can be reused - autonomous_transaction like PL/SQL. I read a manual of Gnu Ada - and this is used often for implementation legacy (obsolete) behave, functionality.