Re: proposal: schema PL session variables

On Tue, Feb 9, 2016 at 9:58 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
wrote:

>
>
> 2016-02-09 15:32 GMT+01:00 Marko Tiikkaja <marko(at)joh(dot)to>:
>
>> On 08/02/16 14:16, Pavel Stehule wrote:
>>
>>> 2016-02-08 13:53 GMT+01:00 Marko Tiikkaja <marko(at)joh(dot)to>:
>>>
>>>>
>>>> Yeah, and that's exactly what I don't want, because that means that
>>>> CREATE
>>>> SCHEMA VARIABLE suddenly breaks existing code.
>>>>
>>>>
>>> theoretically yes, but this conflict can be 100% detected - so no quiet
>>> bug
>>> is possible, and plpgsql_check can find this issue well. If you don't use
>>> schema variable, then your code will be correct. You have to explicitly
>>> create the variable, and if there will be any problem, then the problem
>>> will be only in PL functions in one schema. And you can identify it by
>>> statical analyse.
>>>
>>
>> I'm sorry, but I think you've got your priorities completely backwards.
>> You're saying that it's OK to add a footgun because blown-off pieces of
>> feet can be found by using a third party static analyzer barely anyone
>> uses. And at best, that footgun is only a very minor convenience (though
>> I'd argue that omitting it actually hurts readability).
>>
>
> I don't block the integration plpgsql_check to upstream. I spent hundreds
> hours for it.
>
> Can we look on this problem with different side? What I can do it for safe
> using proposed schema variables.
>
> The possible ways:
>
> 1. requirement prefix like : or @. I don't prefer it because a) hard to
> find a agreement - Oracle fans like ":", MSSQL like @, other maybe $, b)
> with any currently unsupported syntax I have to fix SQL lexer, parser
>
> 2. requirement to use qualified name everywhere - it can works, but I
> don't prefer it, because sometimes can be unfunny to write long qualified
> identifiers. There are not aliases on schema in PLpgSQL. Possible solved by
> variable aliases. But it requires alias.
>
> 3. plpgsql GUC where schema variables are: a) disabled, b) enabled, c)
> only qualified names are allowed - it is similar to #variable_conflict
> option
>
> I prefer @3 with "c" as default, but I can live with @2, and dislike @1
> due mentioned reasons.
>
> Can you be satisfied by any mentioned variant?
>
> Regards
>
> Pavel
>
>
>>
>> That makes absolutely no sense to me at all.
>>
>>
>> .m
>>
>
>
Would it make sense to explicitly import variables in function definitions?

CREATE SESSION VARIABLE foo integer;
CREATE SESSION VARIABLE my_schema.bar text;
SET SESSION VARIABLE foo to 4;
SET SESSION VARIABLE my_schema.bar to 'hi mom';

CREATE FUNCTION my_func (p_param text) returns boolean
LANGUAGE SQL
IMPORT g_foo integer FROM foo,
IMPORT g_textval IN OUT text FROM my_schema.bar

AS $$

SELECT COUNT(*) > 1
FROM my_table
WHERE id = g_foo
AND name = g_textval;
$$;

The IMPORT clause would be something like:

IMPORT local_var_name [IN] [OUT] type FROM [session variable | expression ]

And obviously it would reject importing an expression as an OUT type.
Importing an expression would largely serve the purpose of compile-time
macro, or allowing us to pass parameters into anonymous blocks, something
we've wanted for a while now.

With something like this, the session variables are seen as parameters
inside the function regardless of language and with no new prefix, :, @, or
otherwise.

Oh, and I suggest we call them SESSION variables rather than SCHEMA
variables, to reinforce the idea of how long the values in the variables
live. A session variable is in a sense a 1x1 temp table, whose definition
persists across sessions but whose value does not.

Of course, if they do persist across sessions, then yeah, SCHEMA makes more
sense. But every package variable in Oracle PL/SQL was initialized when the
package was first loaded into the session.