v20241126-0006-plpgsql-tests.patch

From fbe28e061a01635cf6f3d41082aacb683c9a68a8 Mon Sep 17 00:00:00 2001
From: Laurenz Albe <laurenz.albe@cybertec.at>
Date: Wed, 13 Nov 2024 14:06:06 +0100
Subject: [PATCH 06/21] plpgsql tests

---
 src/pl/plpgsql/src/Makefile                   |   3 +-
 .../src/expected/plpgsql_session_variable.out | 382 ++++++++++++++++++
 src/pl/plpgsql/src/meson.build                |   1 +
 .../src/sql/plpgsql_session_variable.sql      | 289 +++++++++++++
 4 files changed, 674 insertions(+), 1 deletion(-)
 create mode 100644 src/pl/plpgsql/src/expected/plpgsql_session_variable.out
 create mode 100644 src/pl/plpgsql/src/sql/plpgsql_session_variable.sql

diff --git a/src/pl/plpgsql/src/Makefile b/src/pl/plpgsql/src/Makefile
index 63cb96fae3e..bbcae27d422 100644
--- a/src/pl/plpgsql/src/Makefile
+++ b/src/pl/plpgsql/src/Makefile
@@ -35,7 +35,8 @@ REGRESS_OPTS = --dbname=$(PL_TESTDB)
 REGRESS = plpgsql_array plpgsql_cache plpgsql_call plpgsql_control \
 	plpgsql_copy plpgsql_domain plpgsql_misc \
 	plpgsql_record plpgsql_simple plpgsql_transaction \
-	plpgsql_trap plpgsql_trigger plpgsql_varprops
+	plpgsql_trap plpgsql_trigger plpgsql_varprops \
+	plpgsql_session_variable
 
 # where to find gen_keywordlist.pl and subsidiary files
 TOOLSDIR = $(top_srcdir)/src/tools
diff --git a/src/pl/plpgsql/src/expected/plpgsql_session_variable.out b/src/pl/plpgsql/src/expected/plpgsql_session_variable.out
new file mode 100644
index 00000000000..ab779900596
--- /dev/null
+++ b/src/pl/plpgsql/src/expected/plpgsql_session_variable.out
@@ -0,0 +1,382 @@
+-- test of session variables
+CREATE VARIABLE plpgsql_sv_var AS numeric;
+LET plpgsql_sv_var = pi();
+-- passing parameters to DO block
+DO $$
+BEGIN
+  RAISE NOTICE 'value of session variable is %', plpgsql_sv_var;
+END;
+$$;
+NOTICE:  value of session variable is 3.14159265358979
+-- passing output from DO block;
+DO $$
+BEGIN
+  LET plpgsql_sv_var = 2 * pi();
+END
+$$;
+SELECT plpgsql_sv_var AS "pi_multiply_2";
+  pi_multiply_2   
+------------------
+ 6.28318530717959
+(1 row)
+
+DROP VARIABLE plpgsql_sv_var;
+-- test access from PL/pgSQL
+CREATE VARIABLE plpgsql_sv_var1 AS int;
+CREATE VARIABLE plpgsql_sv_var2 AS numeric;
+CREATE VARIABLE plpgsql_sv_var3 AS varchar;
+CREATE OR REPLACE FUNCTION writer_func()
+RETURNS void AS $$
+BEGIN
+  LET plpgsql_sv_var1 = 10;
+  LET plpgsql_sv_var2 = pi();
+  -- very long value
+  LET plpgsql_sv_var3 = format('(%s)', repeat('*', 10000));
+END;
+$$ LANGUAGE plpgsql;
+CREATE OR REPLACE FUNCTION updater_func()
+RETURNS void AS $$
+BEGIN
+  LET plpgsql_sv_var1 = plpgsql_sv_var1 + 100;
+  LET plpgsql_sv_var2 = plpgsql_sv_var2 + 100000000000;
+  -- very long value
+  LET plpgsql_sv_var3 = plpgsql_sv_var3 || format('(%s)', repeat('*', 10000));
+END;
+$$ LANGUAGE plpgsql;
+CREATE OR REPLACE FUNCTION reader_func()
+RETURNS void AS $$
+BEGIN
+  RAISE NOTICE 'var1 = %', plpgsql_sv_var1;
+  RAISE NOTICE 'var2 = %', plpgsql_sv_var2;
+  RAISE NOTICE 'length of var3 = %', length(plpgsql_sv_var3);
+END;
+$$ LANGUAGE plpgsql;
+-- execute in a transaction
+BEGIN;
+SELECT writer_func();
+ writer_func 
+-------------
+ 
+(1 row)
+
+SELECT reader_func();
+NOTICE:  var1 = 10
+NOTICE:  var2 = 3.14159265358979
+NOTICE:  length of var3 = 10002
+ reader_func 
+-------------
+ 
+(1 row)
+
+SELECT updater_func();
+ updater_func 
+--------------
+ 
+(1 row)
+
+SELECT reader_func();
+NOTICE:  var1 = 110
+NOTICE:  var2 = 100000000003.14159265358979
+NOTICE:  length of var3 = 20004
+ reader_func 
+-------------
+ 
+(1 row)
+
+END;
+-- execute outside of a transaction
+SELECT writer_func();
+ writer_func 
+-------------
+ 
+(1 row)
+
+SELECT reader_func();
+NOTICE:  var1 = 10
+NOTICE:  var2 = 3.14159265358979
+NOTICE:  length of var3 = 10002
+ reader_func 
+-------------
+ 
+(1 row)
+
+SELECT updater_func();
+ updater_func 
+--------------
+ 
+(1 row)
+
+SELECT reader_func();
+NOTICE:  var1 = 110
+NOTICE:  var2 = 100000000003.14159265358979
+NOTICE:  length of var3 = 20004
+ reader_func 
+-------------
+ 
+(1 row)
+
+-- execute inside a PL/pgSQL block
+DO $$
+BEGIN
+  PERFORM writer_func();
+  PERFORM reader_func();
+  PERFORM updater_func();
+  PERFORM reader_func();
+END;
+$$;
+NOTICE:  var1 = 10
+NOTICE:  var2 = 3.14159265358979
+NOTICE:  length of var3 = 10002
+NOTICE:  var1 = 110
+NOTICE:  var2 = 100000000003.14159265358979
+NOTICE:  length of var3 = 20004
+-- plan caches should be correctly invalidated
+DROP VARIABLE plpgsql_sv_var1, plpgsql_sv_var2, plpgsql_sv_var3;
+CREATE VARIABLE plpgsql_sv_var1 AS int;
+CREATE VARIABLE plpgsql_sv_var2 AS numeric;
+CREATE VARIABLE plpgsql_sv_var3 AS varchar;
+-- should work again
+DO $$
+BEGIN
+  PERFORM writer_func();
+  PERFORM reader_func();
+  PERFORM updater_func();
+  PERFORM reader_func();
+END;
+$$;
+NOTICE:  var1 = 10
+NOTICE:  var2 = 3.14159265358979
+NOTICE:  length of var3 = 10002
+NOTICE:  var1 = 110
+NOTICE:  var2 = 100000000003.14159265358979
+NOTICE:  length of var3 = 20004
+DROP VARIABLE plpgsql_sv_var1, plpgsql_sv_var2, plpgsql_sv_var3;
+DROP FUNCTION writer_func;
+DROP FUNCTION reader_func;
+DROP FUNCTION updater_func;
+-- another check of correct plan cache invalidation
+CREATE VARIABLE plpgsql_sv_var1 AS int;
+CREATE VARIABLE plpgsql_sv_var2 AS int[];
+CREATE OR REPLACE FUNCTION test_func()
+RETURNS void AS $$
+DECLARE v int[] DEFAULT '{}';
+BEGIN
+  LET plpgsql_sv_var1 = 1;
+  v[plpgsql_sv_var1] = 100;
+  RAISE NOTICE '%', v;
+  LET plpgsql_sv_var2 = v;
+  LET plpgsql_sv_var2[plpgsql_sv_var1] = -1;
+  RAISE NOTICE '%', plpgsql_sv_var2;
+END;
+$$ LANGUAGE plpgsql;
+SELECT test_func();
+NOTICE:  {100}
+NOTICE:  {-1}
+ test_func 
+-----------
+ 
+(1 row)
+
+DROP VARIABLE plpgsql_sv_var1, plpgsql_sv_var2;
+CREATE VARIABLE plpgsql_sv_var1 AS int;
+CREATE VARIABLE plpgsql_sv_var2 AS int[];
+SELECT test_func();
+NOTICE:  {100}
+NOTICE:  {-1}
+ test_func 
+-----------
+ 
+(1 row)
+
+DROP FUNCTION test_func();
+DROP VARIABLE plpgsql_sv_var1, plpgsql_sv_var2;
+-- check secure access
+CREATE ROLE regress_var_owner_role;
+CREATE ROLE regress_var_reader_role;
+CREATE ROLE regress_var_exec_role;
+GRANT ALL ON SCHEMA public TO regress_var_owner_role, regress_var_reader_role, regress_var_exec_role;
+SET ROLE TO regress_var_owner_role;
+CREATE VARIABLE plpgsql_sv_var1 AS int;
+LET plpgsql_sv_var1 = 10;
+SET ROLE TO DEFAULT;
+SET ROLE TO regress_var_reader_role;
+CREATE OR REPLACE FUNCTION var_read_func()
+RETURNS void AS $$
+BEGIN
+  RAISE NOTICE '%', plpgsql_sv_var1;
+END;
+$$ LANGUAGE plpgsql SECURITY DEFINER;
+SET ROLE TO DEFAULT;
+SET ROLE TO regress_var_exec_role;
+-- should fail
+SELECT var_read_func();
+ERROR:  permission denied for session variable plpgsql_sv_var1
+CONTEXT:  PL/pgSQL expression "plpgsql_sv_var1"
+PL/pgSQL function var_read_func() line 3 at RAISE
+SET ROLE TO DEFAULT;
+SET ROLE TO regress_var_owner_role;
+GRANT SELECT ON VARIABLE plpgsql_sv_var1 TO regress_var_reader_role;
+SET ROLE TO DEFAULT;
+SET ROLE TO regress_var_exec_role;
+-- should be ok
+SELECT var_read_func();
+NOTICE:  10
+ var_read_func 
+---------------
+ 
+(1 row)
+
+SET ROLE TO DEFAULT;
+SET ROLE TO regress_var_owner_role;
+DROP VARIABLE plpgsql_sv_var1;
+SET ROLE TO DEFAULT;
+SET ROLE TO regress_var_exec_role;
+-- should fail, but not crash
+SELECT var_read_func();
+ERROR:  column "plpgsql_sv_var1" does not exist
+LINE 1: plpgsql_sv_var1
+        ^
+QUERY:  plpgsql_sv_var1
+CONTEXT:  PL/pgSQL function var_read_func() line 3 at RAISE
+SET ROLE TO DEFAULT;
+DROP FUNCTION var_read_func;
+REVOKE ALL ON SCHEMA public FROM regress_var_owner_role, regress_var_reader_role, regress_var_exec_role;
+DROP ROLE regress_var_owner_role;
+DROP ROLE regress_var_reader_role;
+DROP ROLE regress_var_exec_role;
+-- returns updated value
+CREATE VARIABLE plpgsql_sv_var1 AS int;
+CREATE OR REPLACE FUNCTION inc_var_int(int)
+RETURNS int AS $$
+BEGIN
+  LET plpgsql_sv_var1 = COALESCE(plpgsql_sv_var1 + $1, $1);
+  RETURN plpgsql_sv_var1;
+END;
+$$ LANGUAGE plpgsql;
+SELECT inc_var_int(1);
+ inc_var_int 
+-------------
+           1
+(1 row)
+
+SELECT inc_var_int(1);
+ inc_var_int 
+-------------
+           2
+(1 row)
+
+SELECT inc_var_int(1);
+ inc_var_int 
+-------------
+           3
+(1 row)
+
+SELECT inc_var_int(1) FROM generate_series(1,10);
+ inc_var_int 
+-------------
+           4
+           5
+           6
+           7
+           8
+           9
+          10
+          11
+          12
+          13
+(10 rows)
+
+CREATE VARIABLE plpgsql_sv_var2 AS numeric;
+LET plpgsql_sv_var2 = 0.0;
+CREATE OR REPLACE FUNCTION inc_var_num(numeric)
+RETURNS int AS $$
+BEGIN
+  LET plpgsql_sv_var2 = COALESCE(plpgsql_sv_var2 + $1, $1);
+  RETURN plpgsql_sv_var2;
+END;
+$$ LANGUAGE plpgsql;
+SELECT inc_var_num(1.0);
+ inc_var_num 
+-------------
+           1
+(1 row)
+
+SELECT inc_var_num(1.0);
+ inc_var_num 
+-------------
+           2
+(1 row)
+
+SELECT inc_var_num(1.0);
+ inc_var_num 
+-------------
+           3
+(1 row)
+
+SELECT inc_var_num(1.0) FROM generate_series(1,10);
+ inc_var_num 
+-------------
+           4
+           5
+           6
+           7
+           8
+           9
+          10
+          11
+          12
+          13
+(10 rows)
+
+DROP VARIABLE plpgsql_sv_var1, plpgsql_sv_var2;
+DROP FUNCTION inc_var_int;
+DROP FUNCTION inc_var_num;
+-- plpgsql variables are preferred against session variables
+CREATE VARIABLE plpgsql_sv_var1 AS int;
+DO $$
+<<myblock>>
+DECLARE plpgsql_sv_var1 int;
+BEGIN
+  LET plpgsql_sv_var1 = 100;
+
+  plpgsql_sv_var1 := 1000;
+
+  -- print 100;
+  RAISE NOTICE 'session variable is %', public.plpgsql_sv_var1;
+
+  -- print 1000
+  RAISE NOTICE 'plpgsql variable is %', myblock.plpgsql_sv_var1;
+
+  -- print 1000
+  RAISE NOTICE 'variable is %', plpgsql_sv_var1;
+END;
+$$;
+NOTICE:  session variable is 100
+NOTICE:  plpgsql variable is 1000
+NOTICE:  variable is 1000
+DROP VARIABLE plpgsql_sv_var1;
+-- the value should not be corrupted
+CREATE VARIABLE plpgsql_sv_v text;
+LET plpgsql_sv_v = 'abc';
+CREATE FUNCTION ffunc()
+RETURNS text AS $$
+BEGIN
+  RETURN gfunc(plpgsql_sv_v);
+END
+$$ LANGUAGE plpgsql;
+CREATE FUNCTION gfunc(t text)
+RETURNS text AS $$
+BEGIN
+  LET plpgsql_sv_v = 'BOOM!';
+  RETURN t;
+END;
+$$ LANGUAGE plpgsql;
+select ffunc();
+ ffunc 
+-------
+ abc
+(1 row)
+
+DROP FUNCTION ffunc();
+DROP FUNCTION gfunc(text);
+DROP VARIABLE plpgsql_sv_v;
diff --git a/src/pl/plpgsql/src/meson.build b/src/pl/plpgsql/src/meson.build
index 3dd734b776b..3905c0b6d45 100644
--- a/src/pl/plpgsql/src/meson.build
+++ b/src/pl/plpgsql/src/meson.build
@@ -88,6 +88,7 @@ tests += {
       'plpgsql_trap',
       'plpgsql_trigger',
       'plpgsql_varprops',
+      'plpgsql_session_variable',
     ],
   },
 }
diff --git a/src/pl/plpgsql/src/sql/plpgsql_session_variable.sql b/src/pl/plpgsql/src/sql/plpgsql_session_variable.sql
new file mode 100644
index 00000000000..a3cc264cf38
--- /dev/null
+++ b/src/pl/plpgsql/src/sql/plpgsql_session_variable.sql
@@ -0,0 +1,289 @@
+-- test of session variables
+CREATE VARIABLE plpgsql_sv_var AS numeric;
+
+LET plpgsql_sv_var = pi();
+
+-- passing parameters to DO block
+DO $$
+BEGIN
+  RAISE NOTICE 'value of session variable is %', plpgsql_sv_var;
+END;
+$$;
+
+-- passing output from DO block;
+DO $$
+BEGIN
+  LET plpgsql_sv_var = 2 * pi();
+END
+$$;
+
+SELECT plpgsql_sv_var AS "pi_multiply_2";
+
+DROP VARIABLE plpgsql_sv_var;
+
+-- test access from PL/pgSQL
+CREATE VARIABLE plpgsql_sv_var1 AS int;
+CREATE VARIABLE plpgsql_sv_var2 AS numeric;
+CREATE VARIABLE plpgsql_sv_var3 AS varchar;
+
+CREATE OR REPLACE FUNCTION writer_func()
+RETURNS void AS $$
+BEGIN
+  LET plpgsql_sv_var1 = 10;
+  LET plpgsql_sv_var2 = pi();
+  -- very long value
+  LET plpgsql_sv_var3 = format('(%s)', repeat('*', 10000));
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION updater_func()
+RETURNS void AS $$
+BEGIN
+  LET plpgsql_sv_var1 = plpgsql_sv_var1 + 100;
+  LET plpgsql_sv_var2 = plpgsql_sv_var2 + 100000000000;
+  -- very long value
+  LET plpgsql_sv_var3 = plpgsql_sv_var3 || format('(%s)', repeat('*', 10000));
+END;
+$$ LANGUAGE plpgsql;
+
+
+CREATE OR REPLACE FUNCTION reader_func()
+RETURNS void AS $$
+BEGIN
+  RAISE NOTICE 'var1 = %', plpgsql_sv_var1;
+  RAISE NOTICE 'var2 = %', plpgsql_sv_var2;
+  RAISE NOTICE 'length of var3 = %', length(plpgsql_sv_var3);
+END;
+$$ LANGUAGE plpgsql;
+
+-- execute in a transaction
+BEGIN;
+SELECT writer_func();
+SELECT reader_func();
+SELECT updater_func();
+SELECT reader_func();
+END;
+
+-- execute outside of a transaction
+SELECT writer_func();
+SELECT reader_func();
+SELECT updater_func();
+SELECT reader_func();
+
+-- execute inside a PL/pgSQL block
+DO $$
+BEGIN
+  PERFORM writer_func();
+  PERFORM reader_func();
+  PERFORM updater_func();
+  PERFORM reader_func();
+END;
+$$;
+
+-- plan caches should be correctly invalidated
+DROP VARIABLE plpgsql_sv_var1, plpgsql_sv_var2, plpgsql_sv_var3;
+
+CREATE VARIABLE plpgsql_sv_var1 AS int;
+CREATE VARIABLE plpgsql_sv_var2 AS numeric;
+CREATE VARIABLE plpgsql_sv_var3 AS varchar;
+
+-- should work again
+DO $$
+BEGIN
+  PERFORM writer_func();
+  PERFORM reader_func();
+  PERFORM updater_func();
+  PERFORM reader_func();
+END;
+$$;
+
+DROP VARIABLE plpgsql_sv_var1, plpgsql_sv_var2, plpgsql_sv_var3;
+
+DROP FUNCTION writer_func;
+DROP FUNCTION reader_func;
+DROP FUNCTION updater_func;
+
+-- another check of correct plan cache invalidation
+CREATE VARIABLE plpgsql_sv_var1 AS int;
+CREATE VARIABLE plpgsql_sv_var2 AS int[];
+
+CREATE OR REPLACE FUNCTION test_func()
+RETURNS void AS $$
+DECLARE v int[] DEFAULT '{}';
+BEGIN
+  LET plpgsql_sv_var1 = 1;
+  v[plpgsql_sv_var1] = 100;
+  RAISE NOTICE '%', v;
+  LET plpgsql_sv_var2 = v;
+  LET plpgsql_sv_var2[plpgsql_sv_var1] = -1;
+  RAISE NOTICE '%', plpgsql_sv_var2;
+END;
+$$ LANGUAGE plpgsql;
+
+SELECT test_func();
+
+DROP VARIABLE plpgsql_sv_var1, plpgsql_sv_var2;
+
+CREATE VARIABLE plpgsql_sv_var1 AS int;
+CREATE VARIABLE plpgsql_sv_var2 AS int[];
+
+SELECT test_func();
+
+DROP FUNCTION test_func();
+
+DROP VARIABLE plpgsql_sv_var1, plpgsql_sv_var2;
+
+-- check secure access
+CREATE ROLE regress_var_owner_role;
+CREATE ROLE regress_var_reader_role;
+CREATE ROLE regress_var_exec_role;
+
+GRANT ALL ON SCHEMA public TO regress_var_owner_role, regress_var_reader_role, regress_var_exec_role;
+
+SET ROLE TO regress_var_owner_role;
+
+CREATE VARIABLE plpgsql_sv_var1 AS int;
+LET plpgsql_sv_var1 = 10;
+
+SET ROLE TO DEFAULT;
+
+SET ROLE TO regress_var_reader_role;
+
+CREATE OR REPLACE FUNCTION var_read_func()
+RETURNS void AS $$
+BEGIN
+  RAISE NOTICE '%', plpgsql_sv_var1;
+END;
+$$ LANGUAGE plpgsql SECURITY DEFINER;
+
+SET ROLE TO DEFAULT;
+
+SET ROLE TO regress_var_exec_role;
+
+-- should fail
+SELECT var_read_func();
+
+SET ROLE TO DEFAULT;
+
+SET ROLE TO regress_var_owner_role;
+GRANT SELECT ON VARIABLE plpgsql_sv_var1 TO regress_var_reader_role;
+
+SET ROLE TO DEFAULT;
+
+SET ROLE TO regress_var_exec_role;
+
+-- should be ok
+SELECT var_read_func();
+
+SET ROLE TO DEFAULT;
+
+SET ROLE TO regress_var_owner_role;
+
+DROP VARIABLE plpgsql_sv_var1;
+
+SET ROLE TO DEFAULT;
+
+SET ROLE TO regress_var_exec_role;
+
+-- should fail, but not crash
+SELECT var_read_func();
+
+SET ROLE TO DEFAULT;
+
+DROP FUNCTION var_read_func;
+
+REVOKE ALL ON SCHEMA public FROM regress_var_owner_role, regress_var_reader_role, regress_var_exec_role;
+
+DROP ROLE regress_var_owner_role;
+DROP ROLE regress_var_reader_role;
+DROP ROLE regress_var_exec_role;
+
+-- returns updated value
+CREATE VARIABLE plpgsql_sv_var1 AS int;
+
+CREATE OR REPLACE FUNCTION inc_var_int(int)
+RETURNS int AS $$
+BEGIN
+  LET plpgsql_sv_var1 = COALESCE(plpgsql_sv_var1 + $1, $1);
+  RETURN plpgsql_sv_var1;
+END;
+$$ LANGUAGE plpgsql;
+
+SELECT inc_var_int(1);
+SELECT inc_var_int(1);
+SELECT inc_var_int(1);
+
+SELECT inc_var_int(1) FROM generate_series(1,10);
+
+CREATE VARIABLE plpgsql_sv_var2 AS numeric;
+
+LET plpgsql_sv_var2 = 0.0;
+
+CREATE OR REPLACE FUNCTION inc_var_num(numeric)
+RETURNS int AS $$
+BEGIN
+  LET plpgsql_sv_var2 = COALESCE(plpgsql_sv_var2 + $1, $1);
+  RETURN plpgsql_sv_var2;
+END;
+$$ LANGUAGE plpgsql;
+
+SELECT inc_var_num(1.0);
+SELECT inc_var_num(1.0);
+SELECT inc_var_num(1.0);
+
+SELECT inc_var_num(1.0) FROM generate_series(1,10);
+
+DROP VARIABLE plpgsql_sv_var1, plpgsql_sv_var2;
+
+DROP FUNCTION inc_var_int;
+DROP FUNCTION inc_var_num;
+
+-- plpgsql variables are preferred against session variables
+CREATE VARIABLE plpgsql_sv_var1 AS int;
+
+DO $$
+<<myblock>>
+DECLARE plpgsql_sv_var1 int;
+BEGIN
+  LET plpgsql_sv_var1 = 100;
+
+  plpgsql_sv_var1 := 1000;
+
+  -- print 100;
+  RAISE NOTICE 'session variable is %', public.plpgsql_sv_var1;
+
+  -- print 1000
+  RAISE NOTICE 'plpgsql variable is %', myblock.plpgsql_sv_var1;
+
+  -- print 1000
+  RAISE NOTICE 'variable is %', plpgsql_sv_var1;
+END;
+$$;
+
+DROP VARIABLE plpgsql_sv_var1;
+
+-- the value should not be corrupted
+CREATE VARIABLE plpgsql_sv_v text;
+LET plpgsql_sv_v = 'abc';
+
+CREATE FUNCTION ffunc()
+RETURNS text AS $$
+BEGIN
+  RETURN gfunc(plpgsql_sv_v);
+END
+$$ LANGUAGE plpgsql;
+
+CREATE FUNCTION gfunc(t text)
+RETURNS text AS $$
+BEGIN
+  LET plpgsql_sv_v = 'BOOM!';
+  RETURN t;
+END;
+$$ LANGUAGE plpgsql;
+
+select ffunc();
+
+DROP FUNCTION ffunc();
+DROP FUNCTION gfunc(text);
+
+DROP VARIABLE plpgsql_sv_v;
-- 
2.47.0