proposal: SQL parser integration to PL/pgSQL
==Steps==
1. add hook to analyser (transform stage) to substitute unknown
columnref by param - when analyser detect unknown columnref, then call
callback, that returns possible para node or NULL (when external
environment doesn't have a variable). Returned param should be typed
or unknown (for polymorphic params).
2. add special modes to sql parser:
* that allows identify unknown columnref, but don't try to identify
functions, operators - but SRF function's should be identified,
because generate values (same with relations).
* that allows identify potential conflict's identifiers (maybe hook on
transformColumnRef?)
3. with this we should ensure some levels of SQL validation in
PL/pgSQL function:
* Full with warnings [FWW]- this identify identifier's collision (SQL,
PL/pgSQL),
* Full without warnings [FWOW] - this identify unknown functions,
unknown relations,
* Syntax - this identify correct syntax and unknown relation (ignore
check functions and operators),
* None - some "heuristic" minimalistic validation (like current) isn't
compatible and isn't usable.
4. For validation function user could to choose validation's level (GUC1),
5. For run - system is in [FWW] or [FWOW] mode (GUC2) - plans are
generate in compilation time for first run (?? we have to store
parseTree to protect from double query parsing).
==Issues==
* invasive patch - any relation changes (alter table add [drop]
column) should need "recompilation" (not only plan invalidation), new
dependency
* maybe little bit slower first run plpgsql functions,
* some new bugs
* developers have to change some habits - for full validatin should be
necessary creating "skeleton" functions.
==Benefits==
* identifier collisions should be detected clearly and early,
* SQL statements should be fully checked,
* some bugs will be displayed with clean messaage,
* more natural behave for people from Oracle, DB2
* allows named params for SQL
Note: this proposal is related to
http://archives.postgresql.org/pgsql-patches/2007-11/msg00253.php
Notes, comments?
Regards
Pavel Stehule
Pavel Stehule <pavel.stehule@gmail.com> writes:
==Steps==
1. add hook to analyser (transform stage) to substitute unknown
columnref by param - when analyser detect unknown columnref, then call
callback, that returns possible para node or NULL (when external
environment doesn't have a variable). Returned param should be typed
or unknown (for polymorphic params).
IMHO the hook definition should support both the case of external
variables taking precedence over query variables and vice versa.
I don't think the core parser should be forcing that decision. In any
case we'd probably need both options for plpgsql, so as to be able to
support both traditional and Oracle-compatible behavior.
I'd be inclined to do that by letting the hook function interpose
itself between the parser and the regular transformColumnRef processing,
so that it can call the regular transformColumnRef processing either
before or after doing its external lookups. Giving it control only
after the regular processing fails would mean there's no way to let
external variables take precedence.
2. add special modes to sql parser:
None of those seem like a good idea to me. The only part that seems
useful is warning about conflicts between external variables and query
variables. That can be implemented by the hook function itself, if we
define the hook behavior as above.
regards, tom lane
2009/5/24 Tom Lane <tgl@sss.pgh.pa.us>:
Pavel Stehule <pavel.stehule@gmail.com> writes:
==Steps==
1. add hook to analyser (transform stage) to substitute unknown
columnref by param - when analyser detect unknown columnref, then call
callback, that returns possible para node or NULL (when external
environment doesn't have a variable). Returned param should be typed
or unknown (for polymorphic params).IMHO the hook definition should support both the case of external
variables taking precedence over query variables and vice versa.
I don't think the core parser should be forcing that decision. In any
case we'd probably need both options for plpgsql, so as to be able to
support both traditional and Oracle-compatible behavior.
good idea
I'd be inclined to do that by letting the hook function interpose
itself between the parser and the regular transformColumnRef processing,
so that it can call the regular transformColumnRef processing either
before or after doing its external lookups. Giving it control only
after the regular processing fails would mean there's no way to let
external variables take precedence.
ok
2. add special modes to sql parser:
None of those seem like a good idea to me. The only part that seems
useful is warning about conflicts between external variables and query
variables. That can be implemented by the hook function itself, if we
define the hook behavior as above.
there is minimal one necessary - for polymorphic variables, we know
name, but we don't know type. And without types, we can't to transform
correctly functions.
regards
Pavel Stehule
Show quoted text
regards, tom lane
2009/5/25 Pavel Stehule <pavel.stehule@gmail.com>:
2009/5/24 Tom Lane <tgl@sss.pgh.pa.us>:
Pavel Stehule <pavel.stehule@gmail.com> writes:
==Steps==
1. add hook to analyser (transform stage) to substitute unknown
columnref by param - when analyser detect unknown columnref, then call
callback, that returns possible para node or NULL (when external
environment doesn't have a variable). Returned param should be typed
or unknown (for polymorphic params).IMHO the hook definition should support both the case of external
variables taking precedence over query variables and vice versa.
I don't think the core parser should be forcing that decision. In any
case we'd probably need both options for plpgsql, so as to be able to
support both traditional and Oracle-compatible behavior.good idea
the problem is place for hook variable - it should be in ParseState
because we need call hooked functions from environments that install
hook.
Pavel
Show quoted text
I'd be inclined to do that by letting the hook function interpose
itself between the parser and the regular transformColumnRef processing,
so that it can call the regular transformColumnRef processing either
before or after doing its external lookups. Giving it control only
after the regular processing fails would mean there's no way to let
external variables take precedence.ok
2. add special modes to sql parser:
None of those seem like a good idea to me. The only part that seems
useful is warning about conflicts between external variables and query
variables. That can be implemented by the hook function itself, if we
define the hook behavior as above.there is minimal one necessary - for polymorphic variables, we know
name, but we don't know type. And without types, we can't to transform
correctly functions.regards
Pavel Stehuleregards, tom lane