Re: [HACKERS] merging some features from plpgsql2 project
| От | Pavel Stehule |
|---|---|
| Тема | Re: [HACKERS] merging some features from plpgsql2 project |
| Дата | |
| Msg-id | CAFj8pRAMc9pOkkwOt5NS7VSQMi7jNGO+0d6SyyE3W3Ck2rOf7w@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: [HACKERS] merging some features from plpgsql2 project (Merlin Moncure <mmoncure@gmail.com>) |
| Список | pgsql-hackers |
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
Вложения
В списке pgsql-hackers по дате отправления: