proposal: schema PL session variables

Поиск
Список
Период
Сортировка
От Pavel Stehule
Тема proposal: schema PL session variables
Дата
Msg-id CAFj8pRD4OAXp2zp7dBRg5eo6X3rtT5MHTMVRN1e1kdK8xE6E4g@mail.gmail.com
обсуждение исходный текст
Ответы Re: proposal: schema PL session variables
Re: proposal: schema PL session variables
Re: proposal: schema PL session variables
Список pgsql-hackers
Дерево обсуждения
Hi

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.

Possible future enhancing
===================
* global life cycle (not only session)
* access and usage outside PL (from SQL)


Comments, notes??

Regards

Pavel

В списке pgsql-hackers по дате отправления: