Re: Postgres DB recompilation

I am trying to do an update using regular expressions. Is something
like this possible?

a=# select path from page where path ~ '/academic-staff/reports/(.*$)';
path
--------------------------------------
/academic-staff/reports/agriculture/
/academic-staff/reports/arts/
/academic-staff/reports/education/
/academic-staff/reports/engineering/
/academic-staff/reports/medicine/
/academic-staff/reports/music/
/academic-staff/reports/science/
/academic-staff/reports/

a=# update page set path = '/academic-staff/fooreports/\1'
where path ~ '/academic-staff/reports/(.*$)';

What I'm trying to do is replace all occurances of
/academic-staff/reports/(.*$) with
/academic-staff/fooreports/\1
where \1 is everything that was matched by the .*$

can it be done? anyone know how?

--
David A Dickson
david(dot)dickson(at)mail(dot)mcgill(dot)ca