forked from postgres/postgres
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathplpgsql_simple.sql
More file actions
61 lines (48 loc) · 1.34 KB
/
Copy pathplpgsql_simple.sql
File metadata and controls
61 lines (48 loc) · 1.34 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
--
-- Tests for plpgsql's handling of "simple" expressions
--
-- Check that changes to an inline-able function are handled correctly
create function simplesql(int) returns int language sql
as 'select $1';
create function simplecaller() returns int language plpgsql
as $$
declare
sum int := 0;
begin
for n in 1..10 loop
sum := sum + simplesql(n);
if n = 5 then
create or replace function simplesql(int) returns int language sql
as 'select $1 + 100';
end if;
end loop;
return sum;
end$$;
select simplecaller();
-- Check that changes in search path are dealt with correctly
create schema simple1;
create function simple1.simpletarget(int) returns int language plpgsql
as $$begin return $1; end$$;
create function simpletarget(int) returns int language plpgsql
as $$begin return $1 + 100; end$$;
create or replace function simplecaller() returns int language plpgsql
as $$
declare
sum int := 0;
begin
for n in 1..10 loop
sum := sum + simpletarget(n);
if n = 5 then
set local search_path = 'simple1';
end if;
end loop;
return sum;
end$$;
select simplecaller();
-- try it with non-volatile functions, too
alter function simple1.simpletarget(int) immutable;
alter function simpletarget(int) immutable;
select simplecaller();
-- make sure flushing local caches changes nothing
\c -
select simplecaller();