System information

We’ll be making use of the PostgreSQL procedural language called pgSQL/PL to create
a function. This function will be called from a trigger that gets executed whenever we
modify or delete a record in the table used to store voicemail messages. This is so the
data is cleaned up and not left as an orphan in the database:
CREATE FUNCTION vm_lo_cleanup() RETURNS "trigger"
AS $$
declare
msgcount INTEGER;
begin
-- raise notice 'Starting lo_cleanup function for large object with oid
%',old.recording;
-- If it is an update action but the BLOB (lo) field was not changed,
don't do anything
if (TG_OP = 'UPDATE') then
if ((old.recording = new.recording) or (old.recording is NULL)) then
raise notice 'Not cleaning up the large object table,
as recording has not changed';
return new;
end if;
end if;
if (old.recording IS NOT NULL) then
SELECT INTO msgcount COUNT(*) AS COUNT FROM voicemessages WHERE recording
= old.recording;
if (msgcount > 0) then
raise notice 'Not deleting record from the large object table, as object
is still referenced';
return new;
else
perform lo_unlink(old.recording);
if found then
raise notice 'Cleaning up the large object table';
return new;
else
raise exception 'Failed to clean up the large object table';
return old;
end if;
end if;
else
raise notice 'No need to clean up the large object table,
no recording on old row';
return new;
end if;
end$$
LANGUAGE plpgsql;
We’re going to create a table called voicemessages where the voicemail information will
be stored:
CREATE TABLE voicemessages
(
uniqueid serial PRIMARY KEY,
msgnum int4,
dir varchar(80),
380 | Chapter 16: Relational Database Integration