Postgres Pro
Downloads
Home   >   mailing lists

Thread: update field using substrings of another field

update field using substrings of another field

From
"Dave [Hawk-Systems]"
Date:
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

Re: update field using substrings of another field

From
Doug McNaught
Date:
"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

Re: update field using substrings of another field

From
Tom Lane
Date:
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

Re: update field using substrings of another field

From
Andreas Fromm
Date:
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