schema_variables.out

CREATE VARIABLE var1 AS integer;
CREATE TEMP VARIABLE var2 AS text;
DROP VARIABLE var1, var2;
-- functional interface
CREATE VARIABLE var1 AS numeric;
SELECT set_schema_variable('var1', 333);
 set_schema_variable 
---------------------
 
(1 row)

SELECT get_schema_variable('var1', null::numeric);
 get_schema_variable 
---------------------
                 333
(1 row)

SELECT set_schema_variable('var1', 333::integer);
 set_schema_variable 
---------------------
 
(1 row)

SELECT get_schema_variable('var1', null::numeric);
 get_schema_variable 
---------------------
                 333
(1 row)

SELECT set_schema_variable('var1', '333.55'::text);
 set_schema_variable 
---------------------
 
(1 row)

SELECT get_schema_variable('var1', null::numeric);
 get_schema_variable 
---------------------
              333.55
(1 row)

SELECT get_schema_variable('var1', null::int);
 get_schema_variable 
---------------------
                 334
(1 row)

SELECT get_schema_variable('var1', null::text);
 get_schema_variable 
---------------------
 333.55
(1 row)

-- access rights test
CREATE ROLE var_test_role;
SET ROLE TO var_test_role;
-- should to fail
SELECT set_schema_variable('var1', '1000'::text);
ERROR:  permission denied for schema variable var1
SELECT get_schema_variable('var1', null::numeric);
ERROR:  permission denied for schema variable var1
SET ROLE TO DEFAULT;
GRANT SELECT ON VARIABLE var1 TO var_test_role;
SET ROLE TO var_test_role;
-- should to fail
SELECT set_schema_variable('var1', '1000'::text);
ERROR:  permission denied for schema variable var1
-- should to work
SELECT get_schema_variable('var1', null::numeric);
 get_schema_variable 
---------------------
              333.55
(1 row)

SET ROLE TO DEFAULT;
GRANT UPDATE ON VARIABLE var1 TO var_test_role;
SET ROLE TO var_test_role;
-- should to work
SELECT set_schema_variable('var1', '1000'::text);
 set_schema_variable 
---------------------
 
(1 row)

SELECT get_schema_variable('var1', null::numeric);
 get_schema_variable 
---------------------
                1000
(1 row)

SET ROLE TO DEFAULT;
REVOKE ALL ON VARIABLE var1 FROM var_test_role;
CREATE VARIABLE var AS integer;
SELECT set_schema_variable('public.var', 1234);
 set_schema_variable 
---------------------
 
(1 row)

SELECT public.var;
 var  
------
 1234
(1 row)

DO $$
BEGIN
  RAISE NOTICE 'public.var is = %', public.var;
END;
$$;
NOTICE:  public.var is = 1234
CREATE OR REPLACE FUNCTION secure_var()
RETURNS int AS $$
  SELECT public.var;
$$ LANGUAGE sql SECURITY DEFINER;
SELECT secure_var();
 secure_var 
------------
       1234
(1 row)

SET ROLE TO var_test_role;
-- should to fail
SELECT public.var;
ERROR:  permission denied for schema variable var
-- should to work;
SELECT secure_var();
 secure_var 
------------
       1234
(1 row)

SET ROLE TO DEFAULT;
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM generate_series(1,100) g(v) WHERE v = var;
                  QUERY PLAN                   
-----------------------------------------------
 Function Scan on pg_catalog.generate_series g
   Output: v
   Function Call: generate_series(1, 100)
   Filter: (g.v = var)
(4 rows)

CREATE VIEW schema_var_view AS SELECT var;
SELECT * FROM schema_var_view;
 var  
------
 1234
(1 row)

\c -
-- should to work still, but var will be empty
SELECT * FROM schema_var_view;
 var 
-----
    
(1 row)

LET var1 = pi();
SELECT var1;
       var1       
------------------
 3.14159265358979
(1 row)

-- we can look on execution plan
EXPLAIN (VERBOSE, COSTS OFF) LET var1 = pi();
            QUERY PLAN            
----------------------------------
 Let on public.var1
   ->  Result
         Output: 3.14159265358979
(3 rows)

-- LET can be prepared
PREPARE var_pp(int, numeric) AS LET var1 = $1 + $2;
EXECUTE var_pp(100, 1.23456);
SELECT var1;
   var1    
-----------
 101.23456
(1 row)

CREATE VARIABLE var3 AS int;
CREATE OR REPLACE FUNCTION inc(int)
RETURNS int AS $$
BEGIN
  LET public.var3 = COALESCE(public.var3 + $1, $1);
  RETURN var3;
END;
$$ LANGUAGE plpgsql;
SELECT inc(1);
 inc 
-----
   1
(1 row)

SELECT inc(1);
 inc 
-----
   2
(1 row)

SELECT inc(1);
 inc 
-----
   3
(1 row)

SELECT inc(1) FROM generate_series(1,10);
 inc 
-----
   4
   5
   6
   7
   8
   9
  10
  11
  12
  13
(10 rows)

SET ROLE TO var_test_role;
-- should to fail
LET var3 = 0;
ERROR:  permission denied for schema variable var3
SET ROLE TO DEFAULT;
DROP VIEW schema_var_view;
DROP VARIABLE var CASCADE;
DROP VARIABLE var1 CASCADE;
DROP VARIABLE var3 CASCADE;
-- composite variables
CREATE TYPE sv_xyz AS (x int, y int, z numeric(10,2));
CREATE VARIABLE v1 AS sv_xyz;
CREATE VARIABLE v2 AS (x int, y int, z numeric(10,2));
\d v1
schema variable "public.v1"
 Column |     Type      
--------+---------------
 x      | integer
 y      | integer
 z      | numeric(10,2)

\d v2
schema variable "public.v2"
 Column |     Type      
--------+---------------
 x      | integer
 y      | integer
 z      | numeric(10,2)

-- explicit casting is necessary
LET v1 = (1,2,3.14)::sv_xyz;
LET v2 = (10,20,3.14*10)::v2;
-- should to work too - there are prepared casts
LET v1 = (1,2,3.14)::v1;
SELECT v1;
     v1     
------------
 (1,2,3.14)
(1 row)

SELECT v2;
      v2       
---------------
 (10,20,31.40)
(1 row)

SELECT (v1).*;
 x | y |  z   
---+---+------
 1 | 2 | 3.14
(1 row)

SELECT (v2).*;
 x  | y  |   z   
----+----+-------
 10 | 20 | 31.40
(1 row)

SELECT get_schema_variable('v1', 'z', null::double precision);
 get_schema_variable 
---------------------
                3.14
(1 row)

SELECT get_schema_variable('v2', 'z', null::double precision);
 get_schema_variable 
---------------------
                31.4
(1 row)

-- should to fail
SELECT get_schema_variable('v1', 'w', null::double precision);
ERROR:  the variable "v1" has not field "w"
SELECT get_schema_variable('v2', 'w', null::double precision);
ERROR:  the variable "v2" has not field "w"
CREATE VARIABLE v3 AS integer;
SELECT get_schema_variable('v3', 'w', null::double precision);
ERROR:  the variable "v3" is not of row type
SELECT set_schema_variable('v1', 'z', 3.14 * 2);
 set_schema_variable 
---------------------
 
(1 row)

SELECT v1;
     v1     
------------
 (1,2,6.28)
(1 row)

SELECT set_schema_variable('v1', 'z', null::double precision);
 set_schema_variable 
---------------------
 
(1 row)

SELECT v1;
   v1   
--------
 (1,2,)
(1 row)

SELECT set_schema_variable('v1', 'z', 3.3333::double precision);
 set_schema_variable 
---------------------
 
(1 row)

SELECT set_schema_variable('v1', 'x', 10000.22);
 set_schema_variable 
---------------------
 
(1 row)

SELECT v1;
       v1       
----------------
 (10000,2,3.33)
(1 row)

SELECT v1.x + v1.z;
 ?column? 
----------
 10003.33
(1 row)

SELECT v2.x + v2.z;
 ?column? 
----------
    41.40
(1 row)

-- access to composite fields should be safe too
-- should to fail
SET ROLE TO var_test_role;
SELECT v2.x;
ERROR:  permission denied for schema variable v2
SET ROLE TO DEFAULT;
DROP VARIABLE v1;
DROP VARIABLE v2;
DROP VARIABLE v3;
DROP ROLE var_test_role;
-- scalar variables should not be in conflict with qualified column
CREATE VARIABLE varx AS text;
SELECT varx.relname FROM pg_class varx WHERE varx.relname = 'pg_class';
 relname  
----------
 pg_class
(1 row)

-- should to fail
SELECT varx.xxx;
ERROR:  missing FROM-clause entry for table "varx"
LINE 1: SELECT varx.xxx;
               ^
-- variables can be updated under RO transaction
BEGIN;
SET TRANSACTION READ ONLY;
LET varx = 'hello';
COMMIT;
SELECT varx;
 varx  
-------
 hello
(1 row)

DROP VARIABLE varx;
CREATE VARIABLE v1 AS (a int, b numeric, c text);
LET v1 = (1, pi(), 'hello');
SELECT v1;
             v1             
----------------------------
 (1,3.14159265358979,hello)
(1 row)

LET v1.b = 10.2222;
SELECT v1;
        v1         
-------------------
 (1,10.2222,hello)
(1 row)

-- should to fail
LET v1.x = 10;
ERROR:  cannot assign to field "x" of column "x" because there is no such column in data type v1
DROP VARIABLE v1;
-- arrays are supported
CREATE VARIABLE va1 AS numeric[];
LET va1 = ARRAY[1.1,2.1];
LET va1[1] = 10.1;
SELECT va1;
    va1     
------------
 {10.1,2.1}
(1 row)

CREATE VARIABLE va2 AS (a numeric, b numeric[]);
LET va2 = (10.1, ARRAY[0.0, 0.0]);
LET va2.a = 10.2;
SELECT va2;
        va2         
--------------------
 (10.2,"{0.0,0.0}")
(1 row)

LET va2.b[1] = 10.3;
SELECT va2;
         va2         
---------------------
 (10.2,"{10.3,0.0}")
(1 row)

DROP VARIABLE va1;
DROP VARIABLE va2;
-- default values
CREATE VARIABLE v1 AS numeric DEFAULT pi();
LET v1 = v1 * 2;
SELECT v1;
        v1        
------------------
 6.28318530717958
(1 row)

CREATE VARIABLE v2 AS (a numeric, b text DEFAULT 'hello');
LET public.v2.a = pi();
SELECT v2;
            v2            
--------------------------
 (3.14159265358979,hello)
(1 row)

DROP VARIABLE v1;
DROP VARIABLE v2;