1 -- Id: postgresql.conf,v 1.8.2.11 2003/07/15 11:15:43 pnixon Exp $
3 -- create plpgsql language
4 CREATE FUNCTION "plpgsql_call_handler" () RETURNS LANGUAGE_HANDLER AS '$libdir/plpgsql' LANGUAGE C;
5 CREATE TRUSTED LANGUAGE "plpgsql" HANDLER "plpgsql_call_handler";
8 CREATE OR REPLACE FUNCTION chop_number(VARCHAR) RETURNS VARCHAR AS '
10 original_number ALIAS FOR $1;
13 new_number := split_part(original_number,''#'',2);
14 IF new_number = '''' THEN
15 RETURN original_number;
16 ELSE RETURN new_number;
21 CREATE OR REPLACE FUNCTION chop_number_country(VARCHAR) RETURNS VARCHAR AS '
23 original_number ALIAS FOR $1;
27 new_number := split_part(original_number,''#'',2);
28 IF new_number = '''' THEN
29 clean_number := original_number;
30 ELSE clean_number := new_number;
32 IF substring(clean_number from 1 for 2) = ''00'' THEN
33 RETURN substring(clean_number from 3 for 2);
34 ELSIF substring(clean_number from 1 for 1) = ''0'' THEN
37 RETURN substring(clean_number from 1 for 2);
42 CREATE OR REPLACE FUNCTION chop_number_city(VARCHAR) RETURNS VARCHAR AS '
44 original_number ALIAS FOR $1;
48 new_number := split_part(original_number,''#'',2);
49 IF new_number = '''' THEN
50 clean_number := original_number;
51 ELSE clean_number := new_number;
53 IF substring(clean_number from 1 for 2) = ''00'' THEN
54 RETURN substring(clean_number from 5 for 3);
55 ELSIF substring(clean_number from 1 for 1) = ''0'' THEN
56 RETURN substring(clean_number from 2 for 3);
58 RETURN substring(clean_number from 3 for 3);
63 CREATE OR REPLACE FUNCTION chop_number_number(VARCHAR) RETURNS VARCHAR AS '
65 original_number ALIAS FOR $1;
69 new_number := split_part(original_number,''#'',2);
70 IF new_number = '''' THEN
71 clean_number := original_number;
72 ELSE clean_number := new_number;
74 IF substring(clean_number from 1 for 2) = ''00'' THEN
75 RETURN substring(clean_number from 8 for 11);
76 ELSIF substring(clean_number from 1 for 1) = ''0'' THEN
77 RETURN substring(clean_number from 5 for 11);
79 RETURN substring(clean_number from 6 for 11);
84 -- Some sample database VIEWs to simplify billing queries.
86 CREATE OR REPLACE VIEW StartVoIPd AS
87 SELECT DISTINCT ON(h323SetupTime, CallID) * FROM StartVoIP;
89 CREATE OR REPLACE VIEW StopVoIPd AS
90 SELECT DISTINCT ON(h323SetupTime, CallID) * FROM StopVoIP;
93 CREATE OR REPLACE VIEW call_history_csps2 AS
94 SELECT StartVoIP.h323ConnectTime, StopVoIP.h323DisconnectTime, (EXTRACT(EPOCH FROM(StopVoIP.h323DisconnectTime - StartVoIP.h323ConnectTime)))::BIGINT AS CallLength, StopVoIP.CalledStationId AS Number, StopVoIP.UserName AS UserName, StopVoIP.CallingStationId AS CallerID, StopVoIP.CallID
95 FROM StopVoIPd AS StopVoIP LEFT OUTER JOIN StartVoIPd AS StartVoIP
96 ON (StopVoIP.CallID = StartVoIP.CallID)
97 WHERE StopVoIP.NASIPAddress = '212.50.54.122'
98 ORDER BY StartVoIP.h323ConnectTime;
100 CREATE OR REPLACE VIEW call_history_csps AS
101 SELECT CAST ((h323DisconnectTime::date AT TIME ZONE 'UTC') AS date) AS Date, CAST ((h323DisconnectTime AT TIME ZONE 'UTC') AS time without time zone) AS Time, AcctSessionTime AS Length, CalledStationId AS Number, UserName AS UserName, CallingStationId AS CallerID, CallID
103 WHERE NASIPAddress = '212.50.54.122';
105 CREATE OR REPLACE VIEW call_history AS
106 SELECT CAST ((h323SetupTime::date AT TIME ZONE 'UTC') AS date) AS Date, CAST ((h323SetupTime AT TIME ZONE 'UTC') AS time without time zone) AS Time, AcctSessionTime AS Length, CalledStationId AS Number, UserName AS UserName, CallingStationId AS CallerID, H323RemoteAddress, NASIPAddress, CallID
109 CREATE OR REPLACE VIEW call_history AS
110 SELECT CAST ((pots.h323SetupTime::date AT TIME ZONE 'UTC') AS date) AS Date, CAST ((pots.h323SetupTime AT TIME ZONE 'UTC') AS time without time zone) AS Time, pots.AcctSessionTime AS Length, pots.CalledStationId AS Number, ip.H323RemoteAddress AS cust_ip, ip.NASIPAddress AS gw_ip
111 FROM StopTelephony AS pots LEFT OUTER JOIN StopVoIP AS ip
112 ON (pots.CallID = ip.CallID);
114 CREATE OR REPLACE VIEW call_history_customer AS
115 SELECT Date, Time, Length, Number, cust_ip, gw_ip, CustomerIP.Company AS Company
116 FROM call_history LEFT OUTER JOIN customerip
117 ON (call_history.cust_ip = CustomerIP.IpAddr);
119 CREATE OR REPLACE VIEW customerip AS
120 SELECT gw.cust_gw AS IpAddr, cust.company AS Company, cust.customer AS Customer, gw.location AS Location
121 FROM customers AS cust, cust_gw AS gw
122 WHERE cust.cust_id = gw.cust_id;
124 CREATE OR REPLACE VIEW VoIP AS
125 SELECT RadAcctId AS ID, NASIPAddress AS GWIP, AcctSessionTime AS Call_Seconds, chop_number_country(CalledStationId) AS Country, chop_number_city(CalledStationId) AS City,chop_number_number(CalledStationId) AS Number, chop_number(CalledStationId) AS Original_Number, EXTRACT(YEAR FROM (h323setuptime AT TIME ZONE 'UTC')) AS Year, EXTRACT(MONTH FROM (h323setuptime AT TIME ZONE 'UTC')) AS Month, EXTRACT(DAY FROM (h323setuptime AT TIME ZONE 'UTC')) AS Day, CAST ((h323SetupTime AT TIME ZONE 'UTC') AS time without time zone) AS Time, h323DisconnectCause AS error_code, H323RemoteAddress AS Remote_IP, CallID
128 CREATE OR REPLACE VIEW Telephony AS
129 SELECT RadAcctId AS ID, NASIPAddress AS GWIP, AcctSessionTime AS Call_Seconds, chop_number_country(CalledStationId) AS Country, chop_number_city(CalledStationId) AS City,chop_number_number(CalledStationId) AS Number, chop_number(CalledStationId) AS Original_Number, EXTRACT(YEAR FROM (h323setuptime AT TIME ZONE 'UTC')) AS Year, EXTRACT(MONTH FROM (h323setuptime AT TIME ZONE 'UTC')) AS Month, EXTRACT(DAY FROM (h323setuptime AT TIME ZONE 'UTC')) AS Day, CAST ((h323SetupTime AT TIME ZONE 'UTC') AS time without time zone) AS Time, h323DisconnectCause AS error_code, split_part(split_part(CiscoNASPort,':',1),' ',2) AS PRI, split_part(CiscoNASPort,':',3) AS PRI_channel, CiscoNASPort AS isdn_port, CallID AS ConfID
132 CREATE OR REPLACE VIEW calls AS
133 SELECT Date, Time, Length, Number, cust_ip, gw_ip
136 ORDER BY Date, Time, Number, Length, cust_ip ASC;
138 CREATE OR REPLACE VIEW call_history_daily AS
139 SELECT pots.h323ConnectTime, pots.AcctSessionTime, pots.CalledStationId, ip.H323RemoteAddress, pots.NASIPAddress
140 FROM StopTelephony AS pots, StopVoIP AS ip
141 WHERE pots.h323connecttime BETWEEN DATE'YESTERDAY' AND DATE'TODAY' AND pots.h323ConfID = ip.h323ConfID
142 ORDER BY h323ConnectTime, CalledStationId ASC;
144 CREATE OR REPLACE VIEW call_errors AS
145 SELECT pots.h323ConnectTime, pots.AcctSessionTime, pots.CalledStationId, ip.H323RemoteAddress, pots.NASIPAddress
146 FROM StopTelephony AS pots, StopVoIP AS ip
147 WHERE pots.h323ConfID = ip.h323ConfID AND ip.h323disconnectcause <> 0 AND ip.h323disconnectcause <> 10
148 AND ip.h323disconnectcause <> 11 AND ip.h323disconnectcause <> 13
149 ORDER BY H323ConnectTime, CalledStationId, H323RemoteAddress ASC;
153 CREATE OR REPLACE FUNCTION VoIPInsertRecord(StopVoIP.UserName%TYPE, StopVoIP.NASIPAddress%TYPE, StopVoIP.AcctSessionTime%TYPE,
154 StopVoIP.AcctInputOctets%TYPE, StopVoIP.AcctOutputOctets%TYPE, StopVoIP.CalledStationId%TYPE, StopVoIP.CallingStationId%TYPE,
155 StopVoIP.AcctDelayTime%TYPE, StopVoIP.h323CallOrigin%TYPE, StopVoIP.h323SetupTime%TYPE, StopVoIP.h323ConnectTime%TYPE, StopVoIP.h323DisconnectTime%TYPE,
156 StopVoIP.h323DisconnectCause%TYPE, StopVoIP.H323RemoteAddress%TYPE, StopVoIP.H323VoiceQuality%TYPE, StopVoIP.h323ConfID%TYPE) RETURNS BOOLEAN AS '
162 PERFORM radacctid FROM StopVoIP WHERE h323SetupTime = $10 AND NASIPAddress = $2 AND CallID = $16;
164 INSERT into StopVoIP (
165 UserName, NASIPAddress, AcctSessionTime, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId,
166 AcctDelayTime, h323callorigin, h323setuptime, h323connecttime, h323disconnecttime, h323disconnectcause,
167 H323RemoteAddress, h323voicequality, CallID) VALUES($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16);
172 ' LANGUAGE 'plpgsql';
174 CREATE OR REPLACE FUNCTION TelephonyInsertRecord(StopTelephony.UserName%TYPE, StopTelephony.NASIPAddress%TYPE, StopTelephony.AcctSessionTime%TYPE,
175 StopTelephony.AcctInputOctets%TYPE, StopTelephony.AcctOutputOctets%TYPE, StopTelephony.CalledStationId%TYPE, StopTelephony.CallingStationId%TYPE,
176 StopTelephony.AcctDelayTime%TYPE, StopTelephony.CiscoNASPort%TYPE, StopTelephony.h323CallOrigin%TYPE, StopTelephony.h323SetupTime%TYPE,
177 StopTelephony.h323ConnectTime%TYPE, StopTelephony.h323DisconnectTime%TYPE, StopTelephony.h323DisconnectCause%TYPE,
178 StopTelephony.H323VoiceQuality%TYPE, StopTelephony.CallID%TYPE) RETURNS BOOLEAN AS '
181 PERFORM radacctid FROM StopTelephony WHERE h323SetupTime = $11 AND NASIPAddress = $2 AND CallID = $16;
183 INSERT into StopTelephony (
184 UserName, NASIPAddress, AcctSessionTime, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId,
185 AcctDelayTime, CiscoNASPort, h323callorigin, h323setuptime, h323connecttime, h323disconnecttime, h323disconnectcause,
186 h323voicequality, CallID) VALUES($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16);
191 ' LANGUAGE 'plpgsql';