From: pgsql-hackers-owner@postgresql.org > [mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Pavel Stehule > I propose a new database object - a variable. The variable is persistent > object, that holds unshared session based not transactional in memory value > of any type. Like variables in any other languages. The persistence is > required for possibility to do static checks, but can be limited to session > - the variables can be temporal. > > > My proposal is related to session variables from Sybase, MSSQL or MySQL > (based on prefix usage @ or @@), or package variables from Oracle (access > is controlled by scope), or schema variables from DB2. Any design is coming > from different sources, traditions and has some advantages or disadvantages. > The base of my proposal is usage schema variables as session variables for > stored procedures. It should to help to people who try to port complex > projects to PostgreSQL from other databases.
Very interesting. I hope I could join the review and testing.
you are welcome. I wrote a prototype last year based on envelope functions. But the integration must be much more close to SQL to be some clear benefit of this feature. So there is lot of work. I hope so I have a prototype after this winter. It is my plan for winter.
How do you think this would contribute to easing the port of Oracle PL/SQL procedures? Would the combination of orafce and this feature promote auto-translation of PL/SQL procedures? I'm curious what will be the major road blocks after adding the schema variable.
It depends on creativity of PL/SQL developers. Usual .. 80% application is possible to migrate with current GUC - some work does ora2pg. But GUC is little bit slower (not too important) and is not simple possibility to secure it.
So work with variables will be similar like GUC, but significantly more natural (not necessary to build wrap functions). It should be much better when value is of some composite type. The migrations will need some inteligence still, but less work and code will be more readable and cleaner.
I talked already about "schema pined" functions (schema private/public objects) - but I didn't think about it more deeply. There can be special access right to schema variables, the pined schema can be preferred before search_path. With this feature the schema will have very similar behave like Oracle Modules. Using different words - we can implement scope access rights based on schemas. But it is far horizon. What is important - proposal doesn't block any future enhancing in this case, and is consistent with current state. In future you can work with schema private functions, tables, variables, sequences. So variables are nothing special.