Re: proposal: schema PL session variables

Hi

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 ]
>
>
It cannot be implemented in SQL language, because there are not other
variables than function parameters.

It is possible in PLpgSQL, but I prefer the ALIAS keyword - introduction
new reserved keyword introduces a compatibility issues.

>
> 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.
>

I didn't propose SESSION variables - now there are some workarounds how to
anybody can emulate it, so this feature can wait. What we need is safe
session variables with limited access. And the border can be defined by
schema scope. So the keyword SCHEMA has sense, and it is necessary.

>
> 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.
>
>