Re: proposal: session server side variables

2016-12-26 10:54 GMT+01:00 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:

> Hi
>
>
>>
>> In both case, the syntax should be nice and elegant... i.e. not only
>> based on functions, probably it should use some prefix convention (@, $)...
>> For the light weight option.
>>
>> DECLARE @someday DATE [ = <SQL EXPRESSION> ] [visibility restriction?];
>> ... then use @now as a possible value anywhere, which will be
>> substituted quite early in the execution process, before planning.
>> -- update a variable value:
>> [SET, ASSIGN, ... nothing?] @someday = <SQL EXPRESSION>;
>>
>> Ok, that is basically more or less the mysql syntax, too bad, but I think
>> it makes sense for a lightweight object which should not look like a
>> database object at all to avoid confusion.
>>
>> As far as implementation is concerned, I would use a TEXT to TEXT hash
>> table, and implicit cast the result when substituting.
>>
>> @var -> 'text value of var'::type_it_was_declared_with
>>
>
> We are talking about two different features (although the part of name can
> be same):
>
> you are talk about light session variables like MSSQL or MySQL (with same
> syntax), I am talking about secure session variables like Oracle package
> variables (with similar access syntax).
>
> Theoretically, there can be implemented both - but cannot be implemented
> together. Its are partially different features. My proposal is clearly
> related to analogy with Oracle package variables and should to help to
> people who does migration from Oracle, or who writing application in Oracle
> style - database first, almost logic in database.
>
> I have two important reasons why I insist on pg_class base.
>
> 1. security .. it is really fundamental part
>

Dynamic created variables (like MySQL) cannot be safe - anybody can create
variables with self preferred visibility.

> 2. possibility to static check by plpgsql_check - without entry in
> pg_class (or other catalogue table) I have not any valid information about
> type, existence of any variable.
>
> Although I am not supporter (due possible issues with plpgsql_checks) of
> MySQL or MSSQL style variables I am not strongly against this
> implementation with same syntax. But it is different feature, with
> different benefits and costs.
>
> I didn't proposed the packages (and package variables) due issues in
> multilingual PostgreSQL environment and because it is redundant to
> PostgreSQL schemas. Instead I proposed >>secure global session variables<<
> (global like global temporary tables).
>
> Currently light session variables can be implemented as not big extension.
> Secure session variables depends on pg_class internals.
>
> I am not sure if we need a special symbols - it is traditional only.
> Set/Get functions can do same work - years we use same technique for
> sequences. Setter function is simply. Currently is impossible to write
> elegant getter function - because the analyzer has limited work with "any"
> returning functions.
>
> Can be nice to have special hook for functions that returns "any" to push
> there some other external informations.
>
> Regards
>
> Pavel
>
>
>
>
>>
>> --
>> Fabien.
>>
>
>