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
117
|
create or replace function pgq.get_batch_cursor(
in i_batch_id bigint,
in i_cursor_name text,
in i_quick_limit int4,
in i_extra_where text,
out ev_id bigint,
out ev_time timestamptz,
out ev_txid bigint,
out ev_retry int4,
out ev_type text,
out ev_data text,
out ev_extra1 text,
out ev_extra2 text,
out ev_extra3 text,
out ev_extra4 text)
returns setof record as $$
-- ----------------------------------------------------------------------
-- Function: pgq.get_batch_cursor(4)
--
-- Get events in batch using a cursor.
--
-- Parameters:
-- i_batch_id - ID of active batch.
-- i_cursor_name - Name for new cursor
-- i_quick_limit - Number of events to return immediately
-- i_extra_where - optional where clause to filter events
--
-- Returns:
-- List of events.
-- Calls:
-- pgq.batch_event_sql(i_batch_id) - internal function which generates SQL optimised specially for getting events in this batch
-- ----------------------------------------------------------------------
declare
_cname text;
_sql text;
begin
if i_batch_id is null or i_cursor_name is null or i_quick_limit is null then
return;
end if;
_cname := quote_ident(i_cursor_name);
_sql := pgq.batch_event_sql(i_batch_id);
-- apply extra where
if i_extra_where is not null then
_sql := replace(_sql, ' order by 1', '');
_sql := 'select * from (' || _sql
|| ') _evs where ' || i_extra_where
|| ' order by 1';
end if;
-- create cursor
execute 'declare ' || _cname || ' no scroll cursor for ' || _sql;
-- if no events wanted, don't bother with execute
if i_quick_limit <= 0 then
return;
end if;
-- return first block of events
for ev_id, ev_time, ev_txid, ev_retry, ev_type, ev_data,
ev_extra1, ev_extra2, ev_extra3, ev_extra4
in execute 'fetch ' || i_quick_limit::text || ' from ' || _cname
loop
return next;
end loop;
return;
end;
$$ language plpgsql; -- no perms needed
create or replace function pgq.get_batch_cursor(
in i_batch_id bigint,
in i_cursor_name text,
in i_quick_limit int4,
out ev_id bigint,
out ev_time timestamptz,
out ev_txid bigint,
out ev_retry int4,
out ev_type text,
out ev_data text,
out ev_extra1 text,
out ev_extra2 text,
out ev_extra3 text,
out ev_extra4 text)
returns setof record as $$
-- ----------------------------------------------------------------------
-- Function: pgq.get_batch_cursor(3)
--
-- Get events in batch using a cursor.
--
-- Parameters:
-- i_batch_id - ID of active batch.
-- i_cursor_name - Name for new cursor
-- i_quick_limit - Number of events to return immediately
--
-- Returns:
-- List of events.
-- Calls:
-- pgq.get_batch_cursor(4)
-- ----------------------------------------------------------------------
begin
for ev_id, ev_time, ev_txid, ev_retry, ev_type, ev_data,
ev_extra1, ev_extra2, ev_extra3, ev_extra4
in
select * from pgq.get_batch_cursor(i_batch_id,
i_cursor_name, i_quick_limit, null)
loop
return next;
end loop;
return;
end;
$$ language plpgsql strict; -- no perms needed
|
