Dynamic Formatting of View text

Wedgetail · #1 (**permalink**) 11-12-11, 06:12

Hi Forum,

Is there a way to dynamically format the text in a View depending on a table column. The context is for Phone Numbers.

eg. I have four Columns:
1) Country Code
2) Area Code
3) Number
4) Display Format

Quote:

61, 02, 99113456
61, 04, 22123456
61, 02, 94119876

I want the display to be like the following:

Quote:

61 (02) 9911 3456
61 0422 123 456
61 (02) 9411 9876

I am looking to set the formatting based on the Area Code.
eg.

Quote:

61, 02, 00 (00) 0000 0000
61, 04, 00 0000 000 000

Is this possible?

loquin · #2 (**permalink**) 11-23-11, 13:17

You state that you have four columns, but then, you only show three in the data...
That being said, pg uses your SQL select script to create the data in the view, so you should be able to come up with the SQL to format your data as you want.

Something using the SELECT CASE construct can be used to 'route' the number to the appropriate formatting. Something along these lines should be close.

Code:

SELECT country_code
       , CASE WHEN area_code = '02'  THEN '(' || area_code || ')'  || ' ' || substring("number" from 1 for 4) || ' ' ||substring("number" from 5 for 4) 
              WHEN area_code = '04'  THEN area_code || substring("number" from 1 for 2)
             [ELSE "Optional, Some Other Format?]
         END as "Number"

Play with the select statement until it produces the output you need, then use it to create your view.

I also note that your description has spaces in the field names, as well as the use of a reserved word (Number) for a field name; both of these practices are discouraged in every database I have used. With postgresql, it is recommended that you use lower case alpha, non-reserved-word object names, without spaces or dashes. Else, you HAVE to enclose offending field names within quotes, AND with postgresql, their letter case must exactly match the letter case of the object name. (i.e., if the field is named "Number", and you use "number", the cases do not match, and this field will not be found.)

loquin · #3 (**permalink**) 11-23-11, 13:32

If you wished to include U.S. area code formatting as well, you would include "country_code = 61 AND area_code = ..." in the first two WHEN clauses above, and add a third WHEN clause of

Code:

WHEN country_code = '01' THEN '(' || area_code || ') ' || substring("Number" from 1 for 3) || '-' || substring("Number from 4 for 4)

(assuming the U.S. country code is '01' ...)

Wedgetail · #4 (**permalink**) 11-23-11, 21:46

Hi Loquin,

Thank you for your reply.

I was aware that I could statically reformat the strings using a SQL select statement, but was wanting to Dynamically reformat it based on the values in a column.

After this post, I got the dynamic query to work using the TRANSLATE function [see pgManual].

To get this to work, I end up with the following two tables.

Table 1 : Formatting

Code:

CountryCode, AreaCode, NumberFormat
01,12,+ab cd efgh ijkl
61,02,(cd) efgh ijkl
61,04,cdef ghi jkl

Table 2 : Phone Numbers

Code:

CountryCode, AreaCode, PhoneNumber
01,12,45648762
61,02,92345578
61,02,91234785
61,04,22345687
61,04,45842695

View Result

Code:

+01 12 4564 8762
(02) 9234 5578
(02) 9123 4785
0422 345 687
0445 842 695

However, I was hoping I could stay away from the abcdefghijklmno syntax for the column. I would have preferred to find a way to space the formats as either +00 00 0000 0000 or +## ## #### ####. However, I have not found a way for that to be possible, and was hoping someone might suggest an alternative.

In regard to your other comments I appreciate them. However, I was trying to simplify the problem to just the issue I was having rather than include the rather complex layout of the true tables I have.

For your information, here is the final query I used to get the View to work.

Code:

CREATE OR REPLACE VIEW "Communication"."vPhoneNumber" AS
 
 WITH RECURSIVE "UserState"("IDState", "Parent-StateID", "Name", "Depth") AS (
                 SELECT s."IDState", s."Parent-StateID", s."Name", 1
                   FROM "Geographic"."State" s
                  WHERE s."IDState" = (( SELECT "Location"."Geographic-StateID"
                           FROM "UserSetting"."Location"
                          WHERE "Location"."Security-UserID" = (( SELECT "User"."IDUser"
                                   FROM "Security"."User"
                                  WHERE "User"."Username"::name = "current_user"()))))

        UNION
                 SELECT s."IDState", s."Parent-StateID", s."Name", us."Depth" + 1
                   FROM "UserState" us, "Geographic"."State" s
                  WHERE s."IDState" = us."Parent-StateID"
        )
SELECT pn."IDPhoneNumber", translate(
        CASE
            -- User located in same state as phone number
            WHEN (( SELECT "UserState"."IDState"
               FROM "UserState"
              WHERE "UserState"."IDState" = ndc."Geographic-StateID")) IS NOT NULL THEN ( SELECT PhoneNumberFormat"."Format"
               FROM "Communication"."PhoneNumberFormat"
              WHERE "PhoneNumberFormat"."IDPhoneNumberFormat" = ndc."FormatLocal")

            -- User in same Country as phone number, but different state
            WHEN (( SELECT "UserState"."IDState"
               FROM "UserState"
              WHERE "UserState"."Parent-StateID" IS NULL)) = (( SELECT "Geographic"."SelectCountryID"(ndc."Geographic-StateID") AS "SelectCountryID")) THEN ( SELECT "PhoneNumberFormat"."Format"
               FROM "Communication"."PhoneNumberFormat"
              WHERE "PhoneNumberFormat"."IDPhoneNumberFormat" = ndc."FormatNational")

            -- User is in a different Country to the phone number
            ELSE ( SELECT "PhoneNumberFormat"."Format"
               FROM "Communication"."PhoneNumberFormat"
              WHERE "PhoneNumberFormat"."IDPhoneNumberFormat" = ndc."FormatInternational")
        END::text, 'abcdefghijklmno'::text, (cc."CountryCode"::text || ndc."NDC"::text) || pn."SubscriberNumber"::text) AS "Phone Number"
   FROM "Communication"."E164CC" cc, "Communication"."E164NDC" ndc, "Communication"."PhoneNumber" pn
  WHERE pn."E164NDCID" = ndc."IDE164NDC";

Notes:
1. E164 is the International Telecommunications Union Standard : The international public telecommunication numbering plan.
2. Table E164CC holds the Country Code [Dialling Code]
3. Table E164NDC holds the National Designation Code [aka Area Code].
4. The Geographic.State table is recursive and holds the Country, State, County, Suburb details etc.

As you can see, the true query I was working on is rather convoluted, as it includes a recursive query, function call to a recursive query, and case statements.
My final Table 1: Formatting includes three Format columns for different call/dial types [Local, National, International].

I am still interested to see if there is another option for dynamically reformatting the phone numbers that does not use the TRANSLATE function.

Internet Services

Web Development

Digital Marketing

Consumer Tech