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
|
create or replace function pgq.drop_queue(x_queue_name text)
returns integer as $$
-- ----------------------------------------------------------------------
-- Function: pgq.drop_queue(1)
--
-- Drop queue and all associated tables.
-- No consumers must be listening on the queue.
--
-- ----------------------------------------------------------------------
declare
tblname text;
q record;
num integer;
begin
-- check ares
if x_queue_name is null then
raise exception 'Invalid NULL value';
end if;
-- check if exists
select * into q from pgq.queue
where queue_name = x_queue_name;
if not found then
raise exception 'No such event queue';
end if;
-- check if no consumers
select count(*) into num from pgq.subscription
where sub_queue = q.queue_id;
if num > 0 then
raise exception 'cannot drop queue, consumers still attached';
end if;
-- drop data tables
for i in 0 .. (q.queue_ntables - 1) loop
tblname := q.queue_data_pfx || '_' || i;
execute 'DROP TABLE ' || tblname;
end loop;
execute 'DROP TABLE ' || q.queue_data_pfx;
-- delete ticks
delete from pgq.tick where tick_queue = q.queue_id;
-- drop seqs
-- FIXME: any checks needed here?
execute 'DROP SEQUENCE ' || q.queue_tick_seq;
execute 'DROP SEQUENCE ' || q.queue_event_seq;
-- delete event
delete from pgq.queue
where queue_name = x_queue_name;
return 1;
end;
$$ language plpgsql security definer;
|
