2015-11-05 21:29 GMT+01:00 Pavel Stehule <pavel.steh...@gmail.com>: > > > 2015-11-05 13:31 GMT+01:00 Craig Ringer <cr...@2ndquadrant.com>: > >> On 5 November 2015 at 14:36, Pavel Stehule <pavel.steh...@gmail.com> >> wrote: >> >> > 1. The encapsulation and local scope - all objects in schema are >> accessible >> > from other objects in schema by default (can be rewritten by explicit >> > granting). Local objects are visible only from objects in schema. This >> needs >> > enhancing of our search_path mechanism. >> >> Yep. It's as if, within function packagename.funcname, packagename is >> implicitly prefixed to search_path . >> >> I can see that being handy, but not especially important. >> >> > 2. The schema variables - a server side session (can be emulated now) >> and >> > server side local schema session variables (doesn't exist) is pretty >> useful >> > for storing some temp data or high frequent change data - and can >> > significantly increase speed of some use cases. Now we emulate it via >> PLPerl >> > shared array, but the encapsulation is missing. >> >> This is the feature I feel we could really use. >> >> I see *lots* of people emulating session variables by (ab)using custom >> GUCs. The missing-ok variant of current_setting helps with this to the >> point where it's fairly OK now. >> >> The main advantage package variables have - IMO - are package >> permissions. You can define a variable that is writeable only by >> functions within a package. That's really handy for things like row >> security since it lets you have variables you can only set via a >> function that can do things like refuse to run again with different >> args, validate input, etc. So you can do expensive work once, then >> cheap row security checks against the preset variable. Or use it for >> things like "current customer" settings when using pooled connections. >> >> It might make sense to extend custom GUCs for this rather than invent >> a new mechanism, since GUCs have lots of useful properties like >> global, db, user, session and transaction scoping, etc. I'm not really >> sure... I just agree that it's a good idea to be able to have >> something with similar capabilities to package variables. Especially >> security properties. >> > > I mentioned "local schema session variables", but I had to say "local > schema variables", because I don't think using GUC is good idea. > > Personally I am inclined to use different mechanism than GUC - GUC is > untyped and slow, and I don't prefer T-SQL syntax - it is foreign element - > and it can do false believe about relation between T-SQL and Postgres. > > The local schema variables can be accessed only from PL functions - and it > can have usual syntax for any specific PL language. > > So some extension can looks like > > DECLARE [ VARIABLE ] schema.myvar AS integer; > > CREATE LOCAL FUNCTION schema.init() > RETURNS void AS $$ > BEGIN > myvar := 0; > END; > > CREATE OR REPLACE FUNCTION schema.current_var() > RETURNS integer AS $$ > BEGIN > RETURN myvar; > END; > > CREATE OR REPLACE FUNCTION schema.set_var(myvar integer) > RETURNS void AS $$ > BEGIN > schema.myvar := var; -- using qualified name as name collision solution > END; > > Outside schema the access should be via functions schema.current_var() and > schema.set_var(). > > The advantage of this design - we don't need to modify a SQL parser for > DQL and DML, and we don't need to introduce any nonstandard behave (syntax) > to SQL . >
probably we can adopt concept ANSI/SQL MODULEs enhanced about the variables. It is relative similar to proposed code. > > >> >> > 3. The initialization routines - the routines called when any object >> from >> > schema is used first time. >> >> ... which is somewhat similar to having an "on session start" trigger. >> Also an oft-wanted feature. >> >> -- >> Craig Ringer http://www.2ndQuadrant.com/ >> PostgreSQL Development, 24x7 Support, Training & Services >> > >