Обсуждение: update field using substrings of another field

Have a table with fnumber and number, both text
fnumber is a phone number, format "8005551212"
number needs to be the same number but in the format "(800) 555-1212"

we currently run the following each time the list is updated;
update pnums set number='(' || substring(fnumber from 1 for 3) || ') ' ||
substring(fnumber from 4 for 3) || '-' || substring(fnumber from 7 for 4);

is there a way to statically set that number field so that it always equals the
fnumber in that format,  or automatically updates itself when the fnumber field
is changed?

thanks

Dave

"Dave [Hawk-Systems]" <dave@hawk-systems.com> writes:

> Have a table with fnumber and number, both text
> fnumber is a phone number, format "8005551212"
> number needs to be the same number but in the format "(800) 555-1212"
>
> we currently run the following each time the list is updated;
> update pnums set number='(' || substring(fnumber from 1 for 3) || ') ' ||
> substring(fnumber from 4 for 3) || '-' || substring(fnumber from 7 for 4);
>
> is there a way to statically set that number field so that it always
> equals the fnumber in that format, or automatically updates itself
> when the fnumber field is changed?

You could do it fairly easily with a trigger.

-Doug

Doug McNaught <doug@mcnaught.org> writes:
> "Dave [Hawk-Systems]" <dave@hawk-systems.com> writes:
>> Have a table with fnumber and number, both text
>> fnumber is a phone number, format "8005551212"
>> number needs to be the same number but in the format "(800) 555-1212"

> You could do it fairly easily with a trigger.

Or consider plan B: why bother to actually store "number" at all,
if it is trivially computable from "fnumber"?

You could make a view that includes "number" as a derived column,
if you have apps that insist on seeing it as an ordinary-looking
column in the select result.

            regards, tom lane

Wouldn't it be the correct way to do such things just on retrieve of the
data, i.e. to implement the formatted output on the appication layer, or
maybe via a View, so that you don't save the data twice?

Doug McNaught wrote:

>"Dave [Hawk-Systems]" <dave@hawk-systems.com> writes:
>
>
>
>>Have a table with fnumber and number, both text
>>fnumber is a phone number, format "8005551212"
>>number needs to be the same number but in the format "(800) 555-1212"
>>
>>we currently run the following each time the list is updated;
>>update pnums set number='(' || substring(fnumber from 1 for 3) || ') ' ||
>>substring(fnumber from 4 for 3) || '-' || substring(fnumber from 7 for 4);
>>
>>is there a way to statically set that number field so that it always
>>equals the fnumber in that format, or automatically updates itself
>>when the fnumber field is changed?
>>
>>
>
>You could do it fairly easily with a trigger.
>
>-Doug
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>

--
Andreas Fromm

-----------------------------
Drink wet cement...
           ... and get stoned