update field using substrings of another field

[Dave [Hawk-Systems]]

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 "[phone]"

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Doug McNaught]

"Dave [Hawk-Systems]" <da**@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 "[phone]"

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 ma*******@postgresql.org

[Tom Lane]

Doug McNaught <do**@mcnaught.org> writes:

"Dave [Hawk-Systems]" <da**@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 "[phone]"

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Andreas Fromm]

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]" <da**@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 "[phone]"

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 ma*******@postgresql.org

--
Andreas Fromm

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


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend