Re: merging some features from plpgsql2 project

Hi

>
> >
> > 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.

Notes, comments?

Regards

Pavel