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
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
|
--
-- 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();
-- Check case where first attempt to determine if it's simple fails
create function simplesql() returns int language sql
as $$select 1 / 0$$;
create or replace function simplecaller() returns int language plpgsql
as $$
declare x int;
begin
select simplesql() into x;
return x;
end$$;
select simplecaller(); -- division by zero occurs during simple-expr check
create or replace function simplesql() returns int language sql
as $$select 2 + 2$$;
select simplecaller();
-- Check case where called function changes from non-SRF to SRF (bug #18497)
create or replace function simplecaller() returns int language plpgsql
as $$
declare x int;
begin
x := simplesql();
return x;
end$$;
select simplecaller();
drop function simplesql();
create function simplesql() returns setof int language sql
as $$select 22 + 22$$;
select simplecaller();
select simplecaller();
-- Check handling of simple expression in a scrollable cursor (bug #18859)
do $$
declare
p_CurData refcursor;
val int;
begin
open p_CurData scroll for select 42;
fetch p_CurData into val;
raise notice 'val = %', val;
end; $$;
|
