Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2009

    Question Unanswered: Dynamic Formatting of View text

    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

    61, 02, 99113456
    61, 04, 22123456
    61, 02, 94119876
    I want the display to be like the following:
    61 (02) 9911 3456
    61 0422 123 456
    61 (02) 9411 9876
    I am looking to set the formatting based on the Area Code.
    61, 02, 00 (00) 0000 0000
    61, 04, 00 0000 000 000
    Is this possible?

  2. #2
    Join Date
    Jun 2004
    Arizona, USA
    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.

    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.)
    Last edited by loquin; 11-23-11 at 13:36.
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert

  3. #3
    Join Date
    Jun 2004
    Arizona, USA
    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
    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' ...)
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert

  4. #4
    Join Date
    Feb 2009
    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
    CountryCode, AreaCode, NumberFormat
    01,12,+ab cd efgh ijkl
    61,02,(cd) efgh ijkl
    61,04,cdef ghi jkl
    Table 2 : Phone Numbers
    CountryCode, AreaCode, PhoneNumber
    View Result
    +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.
    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"()))))
                     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(
                -- 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";
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts