Re: proposal: schema PL session variables

Hi

2016-02-23 20:52 GMT+01:00 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:

>
>
> 2016-02-12 22:41 GMT+01:00 Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com>:
>
>> On 2/12/16 2:58 PM, Pavel Stehule wrote:
>>
>>>
>>> So I think adding something like this needs to at least address
>>> *how* SQL level access would work, *when* it's eventually
>>> implemented.
>>>
>>>
>>> I understand - and I agree.
>>>
>>> small note: Private variables should not be executed from any SQL,
>>> because SQL has not directly related schema. It can be executed only
>>> from SQL embedded in some object with attached schema - PL functions,
>>> views, constraints, .. So for this use case, the important information
>>> is info about a container. We have to hold info about related schema in
>>> planner/executor context.
>>>
>>
>> I think that's probably true, but this also shows why we need to consider
>> different PLs too. As it stands right now, the only way to access a
>> variable outside of plpgsql would be to call a plpgsql function, and
>> currently there's no way to make a plpgsql function private. So for this to
>> work, private variables probably need to be exposed directly through the pl
>> handler.
>>
>> Again, I'm not saying this all has to be implemented up front, but there
>> needs to be a plan for how it would work.
>>
>> I think it would be a good idea to start a wiki page on this topic to
>> start collecting stuff together.
>
>
> I wrote some basic info - https://wiki.postgresql.org/
> wiki/CREATE_PRIVATE_VARIABLE
>

I though about this feature and now I am thinking so it is really similar
to sequences. Sure there are differences - but a workflow is pretty
similar. Created, dropped by CREATE, DROP statements, accessed with
functions everywhere (and in some iteration directly in PLpgSQL). The
content can be of any type stored in memory - session or transaction
closed. In first iteration initialized on default value when it is first
accessed in scope. Accessibility can be controlled by rights to schema.

syntax:

CREATE (SESSION|TRANSACTION) VARIABLE schema.xx datatype DEFAULT ...;
DROP VARIABLE schema.xx;

Access:

SELECT setvar(regclass, "any"); -- supported by Parser - enforcing "any" to
datatype
SELECT getvar(regclass) -- returns "any" -- supported by Parser --
enforcing "any" to datatype

The access rights on variables can be exactly same like rights on sequences.

Regards

Pavel

>
>
> I changed my opinion on initialization part. The private variables with
> non null default should be initialized in session start. It is much more
> practical and it can be used for triggering some ON CONNECT custom routines.
>
> Regards
>
> Pavel
>
>
>
>>
>> --
>> Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
>> Experts in Analytics, Data Architecture and PostgreSQL
>> Data in Trouble? Get it in Treble! http://BlueTreble.com
>>
>
>