Page 315 - Asterisk™: The Future of Telephony

At the PostgreSQL console, run the following script to create the large object type:
CREATE FUNCTION loin (cstring) RETURNS lo AS 'oidin' LANGUAGE internal IMMUTABLE STRICT;
CREATE FUNCTION loout (lo) RETURNS cstring AS 'oidout' LANGUAGE internal
IMMUTABLE STRICT;
CREATE FUNCTION lorecv (internal) RETURNS lo AS 'oidrecv' LANGUAGE internal
IMMUTABLE STRICT;
CREATE FUNCTION losend (lo) RETURNS bytea AS 'oidrecv' LANGUAGE internal
IMMUTABLE STRICT;

CREATE TYPE lo ( INPUT = loin, OUTPUT = loout, RECEIVE = lorecv, SEND = losend,
INTERNALLENGTH = 4, PASSEDBYVALUE );
CREATE CAST (lo AS oid) WITHOUT FUNCTION AS IMPLICIT;
CREATE CAST (oid AS lo) WITHOUT FUNCTION AS IMPLICIT;
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 from the table used to store voicemail. 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,
dont 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 cleanup the large object table';
return old;
end if;
end if;
else
raise notice 'No need to cleanup the large object table, no recording on old row';

ODBC Voicemail | 287