We have a SIP call accounting, rating and routing product that is driven by a SIP proxy that calls a lot of Postgres stored procedures on the backend. (The SIP

We have a SIP call accounting, rating and routing product that is driven by a SIP proxy that calls a lot of Postgres stored procedures on the backend. (The SIP proxy's configuration is written in a pseudo-programmatic, interpreted scripting language, but one lacking in many basic features and data structure primitives that can be taken for granted in the runtime of a general-purpose programming language, so most of the business layer is in the database of necessity, in addition to all the other reasons for doing it that way.) All throughout the platform, there are many points at which the ANI/caller ID and the dialed digits (the user part of the request URI) must be "translated", which is a fancy phone switch term for stripping one or more digits and then prepending one or more digits. It was such a common operation that it was genericised into our most oft-used PL/PgSQL utility function: -- Character stripping and prefix prepending transformation, which is applied -- to the ANI and DNIS at virtually all entry and exit points in the call -- processing machine. CREATE OR REPLACE FUNCTION strip_prepend_transform(_token varchar, _strip_digits integer, _prepend_prefix varchar) RETURNS varchar AS $$ DECLARE _strip_len smallint := 0; BEGIN IF _strip_digits > 0 THEN -- If the strip length given for some reason exceeds the -- actual string length, trim it down so that those digits -- that do exist are still trimmed. IF _strip_digits > LENGTH(_token) THEN _strip_len = LENGTH(_token); ELSE _strip_len = LENGTH(_token) - _strip_digits; END IF; _token := SUBSTRING(_token FROM (_strip_digits + 1) FOR _strip_len); END IF; IF LENGTH(_prepend_prefix) > 0 AND _prepend_prefix <> '' THEN _token := (_prepend_prefix || _token); END IF; RETURN _token; END $$ LANGUAGE 'plpgsql';