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.dickson@mail.mcgill.ca