On Russian PgConf I had a talk with Oleg about missing features in PLpgSQL, that can complicates a migrations from Oracle to PostgreSQL. Currently I see only one blocker - missing protected session variables. PL/SQL has package variables with possible only package scope and session life cycle. Currently we cannot to ensure/enforce schema scope visibility - and we cannot to implement this functionality in PL languages other than C.
I propose really basic functionality, that can be enhanced in future - step by step. This proposal doesn't contain any controversial feature or syntax, I hope. It is related to PLpgSQL only, but described feature can be used from any PL languages with implemented interface.
Proposal =======
I propose a possibility to declare variables on schema level. These variables can be accessed from any function inside schema, and cannot by accessed directly with functions from other schemas. Schema variables can be accessed only from functions (in this moment). In PLpgSQL the schema variables has same behave as local variables.
Syntax =====
New statement
CREATE SCHEMA VARIABLE varname AS type DEFAULT expr.
This statement creates new memory variable visible only from PL functions created inside related schema. The life cycle of this variable is limited to session. Variable is initialized to default expr (or NULL) when is first used in session.
Usage =====
DROP SCHEMA IF EXISTS test_schema CASCADE;
SET SCHEMA test_schema;
CREATE SCHEMA VARIABLE local_counter AS int DEFAULT 0;
CREATE OR REPLACE FUNCTION increment_counter()
RETURNS void AS $$
BEGIN
local_counter := local_counter + 1;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION get_counter()
RETURNS int AS $$
BEGIN
RETURN local_counter;
END;
$$ LANGUAGE plpgsql;
Security ======
Only a owner of schema can edit functions inside schema, and then only owner of schema has access to schema variable. If it is wanted, then schema variables can be accessed from outside by auxiliary explicitly created functions.