CREATE PRIVATE VARIABLE

From PostgreSQL wiki
Jump to navigationJump to search

Introduction

One of missed functionality related to stored procedures is possibility to work with "unit" private variables. The following proposal describe introduction a schema private variables.

Now, we use a schemas for PL code modularization. The introduction of schema private variables is natural. The variables are:

  • accessible from schema related objects - functions (or views in future),
  • initialized when session is started (when default is not null), and destroyed when session is finishing,
  • accessed via get/set functions from all PL functions,
  • accessed via schema qualified identifier in PL/pgSQL. The schema identification should or should not be required. This can be controlled by PL/pgSQL option,
  • stored binary in PostgreSQL native format.
CREATE PRIVATE VARIABLE my_schema.login_time timestamp DEFAULT current_timestamp;
CREATE PRIVATE VARIABLE my_schema.session_state state_type DEFAULT init_state();
CREATE PRIVATE VARIABLE my_schema.counter2 int; -- the NULL is default

This proposal doesn't introduce "public" variables, although these variables can be introduced later. But my opinion about public variables is negative.

Some examples

CREATE OR REPLACE FUNCTION init_state(OUT state state_type)
AS $$
BEGIN
  state.user := current_user;
  state.counter := 0;
  RETURN;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION my_schema.increase_counter()
RETURNS int AS $$
BEGIN
  -- by default qualified name is required
  -- using "counter" only raises a error
  my_schema.counter := my_schema.counter + 1;
  RETURN my_schema.counter;
END;
$$ LANGUAGE plpgsql;

-- better, doesn't need on_connect initialization
CREATE OR REPLACE FUNCTION my_schema.increase_counter2()
RETURNS int AS $$
BEGIN
  my_schema.counter2 := COALESCE(my_schema.counter2 + 1, 1);
  RETURN my_schema.counter2;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION my_schema.increase_counter_python()
RETURNS int AS $$
  result = plpy.get_private_var("counter) + 1
  plpy.set_private_var("counter", result)
  return result
$$ LANGUAGE plpythonu;

-- but when function is not in "my_schema", same code can fail
CREATE OR REPLACE FUNCTION public.increase_counter_python_fail()
RETURNS int AS $$
  result = plpy.get_private_var("counter") + 1 # runtime error "schema private variable not found"
  plpy.set_private_var("counter", result)
  return result
$$ LANGUAGE plpythonu;

-- this doesn't work too
CREATE OR REPLACE FUNCTION my_schema.foo()
RETURNS int AS $$
BEGIN
  RETURN public.increase_counter_python_fail();
END;
$$ LANGUAGE plpgsql;

-- but following code should to work
-- user have to have a rights for running my_schema.increase_counter_python
CREATE OR REPLACE FUNCTION public.foo()
RETURNS int AS $$
BEGIN
  RETURN my_schema.increase_counter_python();
END;
$$ LANGUAGE plpgsql;